棘手的（ＳＱＬ）面试题

[songyutou]

群上的兄弟，谁帮忙做下这个题目：

一个简单的表TABLE 有100条以上的信息，其中包括：
产品 颜色 数量
产品1 红色 123
产品1 蓝色 126
产品2 蓝色 103
产品2 红色 NULL
产品2 红色 89
产品1 红色 203
。。。。。。。。。。。。
请用SQL语句完成以下问题：


1。按产品分类，仅列出各类商品中红色多于蓝色的商品名称及差额数量：





2。按产品分类，将数据按下列方式进行统计显示
产品 红色 蓝色

[lxzm1001]

2。按产品分类，将数据按下列方式进行统计显示
create table test(产品 varchar(10),颜色 varchar(10),数量 int)
insert test select '产品1','红色',123
insert test select '产品1','蓝色',126
insert test select '产品2','蓝色',103
insert test select '产品2','红色',NULL
insert test select '产品2','红色',89
insert test select '产品1','红色',203
select * from test
select 产品,红色=sum(case when 颜色='红色' then 数量 else 0 end),
蓝色=sum(case when 颜色='蓝色' then 数量 else 0 end) from test group by 产品

[Yang_]

1

select 产品,sum(case when 颜色='红色' then 数量 else 0 end)-sum(case when 颜色='蓝色' then 数量 else 0 end) as 差额
from test
group by 产品
having sum(case when 颜色='红色' then 数量 else 0 end)>sum(case when 颜色='蓝色' then 数量 else 0 end)

[Yang_]

1
or:
select 产品,sum(case when 颜色='红色' then 数量 when 颜色='蓝色' then -数量 else 0 end) as 差额
from test
group by 产品
having sum(case when 颜色='红色' then 数量 when 颜色='蓝色' then -数量 else 0 end)>0

[wisdomone]

UP

[lxzm1001]

select 产品,max(col)-min(col) from (
select 产品,颜色,sum(数量) col from test group by 产品,颜色) a group by 产品

[txlicenhe]

如果只有两种颜色也可以这样:
1:
select 产品,sum(IsNull(数量,0) * Replace(replace(颜色,'红色',1),'蓝色',-1))
from test
group by 产品
having sum(IsNull(数量,0) * Replace(replace(颜色,'红色',1),'蓝色',-1))>0

2:
select 产品,sum(IsNull(数量,0) * Replace(replace(颜色,'红色',1),'蓝色',0))
,sum(IsNull(数量,0) * Replace(replace(颜色,'红色',0),'蓝色',1))
from test
group by 产品
having sum(IsNull(数量,0) * Replace(replace(颜色,'红色',1),'蓝色',-1))>0

[mugua604]

1.
select a.产品,(红-蓝) from (select 产品,sum(数量) as 红 from test where 颜色='红色' group by 产品) a
left join (select 产品,sum(数量) as 蓝 from test where 颜色='蓝色' group by 产品) b
on a.产品=b.产品

[giftzheng]

create table test(产品 varchar(10),颜色 varchar(10),数量 int)
insert test select '产品1','红色',123
insert test select '产品1','蓝色',126
insert test select '产品2','蓝色',103
insert test select '产品2','红色',NULL
insert test select '产品2','红色',89
insert test select '产品1','红色',203
--1
select 产品,sum(a)
from
(
select 产品,-数量 as a
from test
where 颜色='蓝色'
union all
select 产品,数量 as a
from test
where 颜色='红色'
) as b
group by 产品
having sum(a)>0
--2
select 产品,红色=sum(case when 颜色='红色' then 数量 else 0 end),
蓝色=sum(case when 颜色='蓝色' then 数量 else 0 end) from test group by 产品
--表怪怪的

[shareinfo]

个人认为还是马可的高招呀!!

==================================
在SQL Server 高手的大海中小心的行走
==================================

[songyutou]

谢谢回贴的兄弟，你们的答案我都有运行了，问题解决了，真是感激！
对上面的txlicenhe(马可) 兄弟假设的“如果只有两种颜色。。。”那么我想再请问一下，如果颜色不止两种，还有更多种其它颜色，不知道这道题该怎么做（只作讨论）？

[Yang_]

case 是正宗的，两题都可解决

第一题的left join 和union all解决方法也是可取的

至于马可的答案，充分说明马可的聪明才智和娱乐天分，不知道马可队这个评价满意吗？

[txlicenhe]

呵呵，过奖。

也就闹着玩，太有局限性。 还是Yang_的解答最正宗。

[flymoon]

zvfzfgdx