50分 sql一句
如下四个表：
表a
cityid city
1 北京
2 南京
3 天津
4 上海
表b
productid producte
1 冰箱
2 热水器
3 电视机
4 洗衣机
5 电视柜
表c
cityid productid output
1 2 100
1 2 200
2 1 50
2 4 400
3 4 300
1 3 100
1 2 200
用一句sql语句或存储过程求出如下表的内容：
冰箱 热水器 电视机 洗衣机 电视柜
北京 0 500 100 0 0
南京 50 0 0 400 0
天津 0 0 0 300 0
上海 0 0 0 0 0
[解决办法]
Create TAble [表a](
[cityid] [int] IDENTITY (1, 1) NOT NULL ,
[city] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
)ON [PRIMARY]
GO
INSERT INTO [表a] ([city]) values (N '北京 ')
INSERT INTO [表a] ([city]) values (N '南京 ')
INSERT INTO [表a] ([city]) values (N '天津 ')
INSERT INTO [表a] ([city]) values (N '上海 ')
GO
Create TAble [表b](
[productid] [int] IDENTITY (1, 1) NOT NULL ,
[producte] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
)ON [PRIMARY]
GO
INSERT INTO [表b] ([producte]) values (N '冰箱 ')
INSERT INTO [表b] ([producte]) values (N '热水器 ')
INSERT INTO [表b] ([producte]) values (N '电视机 ')
INSERT INTO [表b] ([producte]) values (N '洗衣机 ')
INSERT INTO [表b] ([producte]) values (N '电视柜 ')
GO
CREATE TABLE [表c] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[cityid] [int] ,
[productid] [int],
[output] [numeric](18, 0)
) ON [PRIMARY]
GO
INSERT INTO [表c] ([cityid],[productid],[output]) values (1,2,100)
INSERT INTO [表c] ([cityid],[productid],[output]) values (1,2,200)
INSERT INTO [表c] ([cityid],[productid],[output]) values (2,1,50)
INSERT INTO [表c] ([cityid],[productid],[output]) values (2,4,400)
INSERT INTO [表c] ([cityid],[productid],[output]) values (3,4,300)
INSERT INTO [表c] ([cityid],[productid],[output]) values (1,3,100)
INSERT INTO [表c] ([cityid],[productid],[output]) values (1,2,200)
Go
Create View [myview]
as
Select [表c].* ,[表a].[city] As 城市,[表b].[producte] As 商品
From [表a],[表b],[表c]
WHERE [表a].[cityID]=[表c].[cityID] AND [表b].[productid]=[表c].[productid]
GO
select 城市,sum(case 商品 when '冰箱 ' then [output] else 0 end) as '冰箱 ',
sum(case 商品 when '热水器 ' then [output] else 0 end) as '热水器 ',
sum(case 商品 when '电视机 ' then [output] else 0 end) as '电视机 ',
sum(case 商品 when '洗衣机 ' then [output] else 0 end) as '洗衣机 ',
sum(case 商品 when '电视柜 ' then [output] else 0 end) as '电视柜 '
from [myview]
group by 城市
drop table [表c],[表b],[表a]
drop View [myview]
[解决办法]
我在一楼发的东西没人看,看来非要写完才行declare @str varchar(2000)set @str = 'select a.city 'select @str = @str + ',sum(case productid when ' ' ' + cast(productid as varchar(10)) + ' ' ' then output else 0 end ) as ' + producte from bset @str = @str + ' from a left outer join c on a.cityid = c.cityid group by a.city 'exec(@str)
