product 表结构如下:Product Color Num
A1 Red 4
A2 Red 5
A1 Blue 6
A2 Blue 3
A3 Red 9
A3 Blue 5现在要求 筛选中 同一种商品 红色比蓝色多的产品名及差额如上面的结果应该是product balance
A2 2
A3 4请问这样一条SQL 语句该如何写??
A1 Red 4
A2 Red 5
A1 Blue 6
A2 Blue 3
A3 Red 9
A3 Blue 5现在要求 筛选中 同一种商品 红色比蓝色多的产品名及差额如上面的结果应该是product balance
A2 2
A3 4请问这样一条SQL 语句该如何写??
Item Color Quantity
----------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red null
Chair Red 210 1.列出红色商品的数据总和
2.按Item分类,列出各类商品的数量
3.按item分类,仅列出各类商品中红色多于蓝色的商品名称及差额数据.
4.按item分类,将数据按下列方式进行统计显示
Item Red Blue
1
SELECT SUM(quantity) FROM tb WHERE color='red'
2
SELECT item,count(*) FROM tb GROUP BY item
3
SELECT item,SUM(CASE WHEN color='red' THEN quantity ELSE 0 END)
-
SUM(CASE WHEN color='blue' THEN quantity ELSE 0 END) 差额
FROM tb
GROUP BY item
HAVING COUNT(CASE WHEN color='red' THEN 1 ELSE NULL END)>COUNT(CASE WHEN color='blue' THEN 1 ELSE NULL END)
4
SELECT item,COUNT(CASE WHEN color='red' THEN 1 ELSE NULL END) red,
COUNT(CASE WHEN color='blue' THEN 1 ELSE NULL END) blue
FROM tb
GROUP BY item
from product a
where color='red' and not exists(select 1 from product b where a.product=b.product and b.color='blue' and a.num<b.num)
Product,
sum(case Color when 'Red' then Num when 'Blue' then - Num else 0 end) as balance
from
product
group by
Product
having
sum(case Color when 'Red' then Num when 'Blue' then - Num else 0 end)>0
if object_id('product') is not null
drop table product
go
create table product(Product char(2),Color varchar(10),Num int)
insert into product
select 'A1','Red',4
union all select 'A2','Red',5
union all select 'A1','Blue',6
union all select 'A2','Blue',3
union all select 'A3','Red',9
union all select 'A3','Blue',5select Product,
sum(case when Color='Red' then Num else 0 end)-sum(case when Color='Blue' then Num else 0 end) as 'balance'
from product
group by Product
having sum(case when Color='Red' then Num else 0 end)>sum(case when Color='Blue' then Num else 0 end)
/*
Product balance
------- -----------
A2 2
A3 4(所影响的行数为 2 行)
*/
insert into @product values('A1','Red' ,4)
insert into @product values('A2','Red' ,5)
insert into @product values('A1','Blue',6)
insert into @product values('A2','Blue',3)
insert into @product values('A3','Red' ,9)
insert into @product values('A3','Blue',5)select
Product,
sum(case Color when 'Red' then Num when 'Blue' then - Num else 0 end) as balance
from
@product
group by
Product
having
sum(case Color when 'Red' then Num when 'Blue' then - Num else 0 end)>0/*
Product balance
------------ -----------
A2 2
A3 4
*/
insert into product select 'A1','Red ',4
insert into product select 'A2','Red ',5
insert into product select 'A1','Blue', 6
insert into product select 'A2','Blue', 3
insert into product select 'A3','Red ',9
insert into product select 'A3','Blue', 5 select a.product,a.num-(select num from product b where a.product=b.product and b.color='blue') as num
from product a
where color='red' and not exists(select 1 from product b where a.product=b.product and b.color='blue' and a.num <b.num)drop table product;
product num
-------------------- -----------
A2 2
A3 4