如下四个表:表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
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(zip varchar(10),count int,name varchar(10)) insert into a values('94501', 25 ,'LIA')
insert into a values(94501 ,14 ,'SBE')
insert into a values(94501 ,12 ,'VSBE')
insert into a values(94502 ,7 ,'LIA')
insert into a values(94502 ,2 ,'SBE')
insert into a values(94502 ,2 ,'VSBE')
select * from adeclare @str varchar(2000)set @str = 'select zip '
select @str = @str + ',sum(case name when ''' + name + ''' then count else 0 end ) as ' + name from (select distinct name from a) as aaset @str = @str + ' from a group by zip'exec(@str)
isnull((select sum(pt) from cdpd where cdid = 1 and pdid = 1),0) 冰箱,
isnull((select sum(pt) from cdpd where cdid = 1 and pdid = 2),0) 热水器,
isnull((select sum(pt) from cdpd where cdid = 1 and pdid = 3),0) 电视机,
isnull((select sum(pt) from cdpd where cdid = 1 and pdid = 4),0) 洗衣机,
isnull((select sum(pt) from cdpd where cdid = 1 and pdid = 5),0) 电视柜
from cd left join cdpd on cd.cityid = cdpd.cdid
where cityid = 1union
select city 城市,
isnull((select sum(pt) from cdpd where cdid = 2 and pdid = 1),0) 冰箱,
isnull((select sum(pt) from cdpd where cdid = 2 and pdid = 2),0) 热水器,
isnull((select sum(pt) from cdpd where cdid = 2 and pdid = 3),0) 电视机,
isnull((select sum(pt) from cdpd where cdid = 2 and pdid = 4),0) 洗衣机,
isnull((select sum(pt) from cdpd where cdid = 2 and pdid = 5),0) 电视柜
from cd left join cdpd on cd.cityid = cdpd.cdid
where cityid = 2 union
select city 城市,
isnull((select sum(pt) from cdpd where cdid = 3 and pdid = 1),0) 冰箱,
isnull((select sum(pt) from cdpd where cdid = 3 and pdid = 2),0) 热水器,
isnull((select sum(pt) from cdpd where cdid = 3 and pdid = 3),0) 电视机,
isnull((select sum(pt) from cdpd where cdid = 3 and pdid = 4),0) 洗衣机,
isnull((select sum(pt) from cdpd where cdid = 3 and pdid = 5),0) 电视柜
from cd left join cdpd on cd.cityid = cdpd.cdid
where cityid = 3 union
select city 城市,
isnull((select sum(pt) from cdpd where cdid = 4 and pdid = 1),0) 冰箱,
isnull((select sum(pt) from cdpd where cdid = 4 and pdid = 2),0) 热水器,
isnull((select sum(pt) from cdpd where cdid = 4 and pdid = 3),0) 电视机,
isnull((select sum(pt) from cdpd where cdid = 4 and pdid = 4),0) 洗衣机,
isnull((select sum(pt) from cdpd where cdid = 4 and pdid = 5),0) 电视柜
from cd left join cdpd on cd.cityid = cdpd.cdid
where cityid = 4这个笨重的方法竟然求出来是这样的上海这条应该在最后一条的 怎么能弄到最后面呢城市 冰箱 热水器 电视机 洗衣机 电视柜
北京 0 500 100 0 0
南京 50 0 0 400 0
上海 0 0 0 0 0
天津 0 0 0 300 0
等高人ing~~~~~
[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'上海')GOCreate 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'电视柜')GOCREATE 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)GoCreate 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] GOselect 城市,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)