Question

求教一道SQL题目

一个简单的表Proudect有以下信息
Proudect Color Amount
产品1 红 123
产品1 蓝 126
产品2 蓝 103
产品2 红 Null
产品2 红 89
产品1 红 203

要求写出：
1.按产品分类，仅列出各类产品中红色多于蓝色产品的名称及差额。
2.按产品分类，将数据按一下方式进行统计显示：
产品 红色 蓝色

Answer 1

楼上的算法应该是没什么问题，不过对于数据是null的要另行处理，这个时候需要用nvl函数

/*1.按产品分类，仅列出各类产品中红色多于蓝色产品的名称及差额。*/
select A.Proudect as 名称, (A.Amount - B.Amount) as 差额 from
(select Proudect, Color, Sum(nvl(Amount),0) Amount from Proudect
group by Proudect, Color) A,
(select Proudect, Color, Sum(nvl(Amount),0) Amount from Proudect
group by Proudect, Color) B
where A.Proudect = B.Proudect and A.Color = '红' and B.Color = '蓝' and A.Amount > B.Amount

/*2.按产品分类，将数据按一下方式进行统计显示：
产品 红色 蓝色
*/
select A.Proudect as 名称, A.Amount as '红色', B.Amount as '蓝色' from
(select Proudect, Color, Sum(nvl(Amount),0) Amount from Proudect
group by Proudect, Color) A,
(select Proudect, Color, Sum(nvl(Amount),0) Amount from Proudect
group by Proudect, Color) B
where A.Proudect = B.Proudect and A.Color = '红' and B.Color = '蓝'

Answer 2

我也看不懂

Answer 3

/*1.按产品分类，仅列出各类产品中红色多于蓝色产品的名称及差额。*/
select A.Proudect as 名称, (A.Amount - B.Amount) as 差额 from
(select Proudect, Color, Sum(Amount) Amount from Proudect
group by Proudect, Color) A,
(select Proudect, Color, Sum(Amount) Amount from Proudect
group by Proudect, Color) B
where A.Proudect = B.Proudect and A.Color = '红' and B.Color = '蓝' and A.Amount > B.Amount

/*2.按产品分类，将数据按一下方式进行统计显示：
产品 红色 蓝色
*/
select A.Proudect as 名称, A.Amount as '红色', B.Amount as '蓝色' from
(select Proudect, Color, Sum(Amount) Amount from Proudect
group by Proudect, Color) A,
(select Proudect, Color, Sum(Amount) Amount from Proudect
group by Proudect, Color) B
where A.Proudect = B.Proudect and A.Color = '红' and B.Color = '蓝'