将所有值(QTY)中的所有值总结，然后加入并总和客户(QTY)表的所有值，然后显示为可用的_STOCK中的另一列

Question

总和函数结果不正确(加入两个表时(##

select dress,sum(qty) as sold 
 from customer group by dress
-----------------------
Dress         |  Sold |
Casual Dress  |  5    |
Fancy Dress   |  2    |
Jeans         |  3    |
-----------------------

- 正确的结果

select dress,sum(qty) as stock 
from stock group by dress
-----------------------
Dress         |  Stock |
Casual Dress  |  15    |
Fancy Dress   |  17    |
Jeans         |  35    |
Party Wear    |  15    |
-----------------------

- 正确的结果

连接两个表

    select s.Id ,s.dress, sum(s.qty) 
    as stock, sum(c.qty) as sold,
    sum(s.qty-c.qty) as available
    from stock s
    left outer join customer c
    on c.Id=s.Id group by s.dress,s.Id
----------------------------------------------
Id  Dress        | Stock | Sold | available  |
1   Fancy Dress  | 17    | 6    | 11         | 
2   Casual Dress | 30    | 10   | 20         |
3   Party Wear   | 15    | NULL | NULL       |
4   Jeans        | 35    | 18   | 17         |
----------------------------------------------

加入上面的两个表

Answer 1

我试图用数据来重现您的案例(根据2个正确的查询结果(，但没有任何错误发生

http://sqlfiddle.com/#！18/9eecb/4208

我怀疑您在每张桌子上的同一产品具有不同的ID，因此您可以尝试加入桌子上的桌子(名称(，而不是IDS

Answer 2

我建议使用带有ProductID和ProductName的产品表进行更好的数据库设计。但是，根据您提供的内容

create table #customer
(
    customerId      int
    ,dressName      varchar(20)
    ,qtySold        int
)
create table #dress
(
    dressId     int 
    ,dressName  varchar(20)
    ,qtyInStock int
)
insert into #customer
select 1,'Casual Dress',5 union all
select 1,'Casual Dress',5 union all
select 1,'Fancy Dress',2 union all
select 2,'Jeans',3
insert into #dress
select 1,'Casual Dress',15 union all
select 2,'Fancy Dress',17 union all
select 3,'Jeans',35 union all
select 4,'Casual Dress',15 union all
select 5,'Party Wear',15 
select * from #customer
select * from #dress
select t2.dressName, t2.stock, t1.sold, (t2.stock - t1.sold) as available 
from
    (select dressName, sum(qtySold) as sold
     from #customer
     group by dressName) t1
     right join (select dressName, sum(qtyInStock) as stock
            from #dress
            group by dressName) t2 on t1.dressName = t2.dressName

 drop table #customer
 drop table #dress

ouptput：

dressName       stock   sold    available
Casual Dress    30      10      20
Fancy Dress     17      2       15
Jeans           35      3       32
Party Wear      15      NULL    NULL