求一SQL 语句答案 在线等

来源：百度知道 编辑：UC知道 时间：2024/06/06 07:09:19
CREATE TABLE [tt1] (
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[nub] [int] NOT NULL ,
[type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

select * from tt1
insert into tt1 values('产品1',12,'红')
insert into tt1 values('产品1',14,'蓝')
insert into tt1 values('产品2',15,'红')
insert into tt1 values('产品2',6,'蓝')
insert into tt1 values('产品1',1,'红')
insert into tt1 values('产品1',8,'蓝')
insert into tt1 values('产品2',9,'红')

select name,sum(nub) as nub,type from tt1 group by type , name

1.只显示出出红色多余蓝色的商品？？？

2.要求出查询结果 格式如下
产品名 红 蓝
产品1 XX XX
产品2 XX XX
产品3 XX XX
.
.
.

涉及到表的行列转换
select name,'红'=sum(case type when '红' then nub end),'蓝'=sum(case type when '蓝' then nub end)
group by name

然后把这个表嵌套在外查询中select name from（上面的表）where '红'>'蓝'

select A.name,A.红,B.蓝 from
(select name,type,sum(nub) 红 from tt1 where type ='红' group by name,type)A,
(select name,type,sum(nub) 蓝 from tt1 where type ='蓝' group by name,type)B
where A.name=B.name and A.红>B.蓝;

---
以上，希望对你有所帮助。

1.
select a.name from
(select name,sum(nub) count ,TYPE from tt1 group by name,type) a,
(select name,sum(nub) count ,TYPE from tt1 group by name,type) b
where a.TYPE='红' and b.TYPE='蓝'
and a.name=b.name
and a.count>b.count;

2.
select a.name,sum(a.红),sum(a.蓝) from
(select name,(case when type='红' then sum(nub) end) 红 ,(case when type='蓝' then