求一sql 语句（在线等）
create table #Market
(
Id int ,
Name varchar(10)
)
insert into #Market(Id , Name) values( '1 ', 'Shanghai ')
insert into #Market(Id , Name) values( '2 ', 'Beijing ')
insert into #Market(Id , Name) values( '3 ', 'Guangzhou ')
insert into #Market(Id , Name) values( '4 ', 'Yangzhou ')
create table #Pannel
(
Id int ,
Name varchar(50)
)
insert into #Pannel(Id , Name) values( '10 ', 'AGB ')
insert into #Pannel(Id , Name) values( '20 ', 'CSM ')
insert into #Pannel(Id , Name) values( '30 ', 'AGB(great city) ')
create table #MarketMap
(
MarketId int,
PannelId int
)
insert into #MarketMap values(1,10)
insert into #MarketMap values(1,20)
insert into #MarketMap values(1,30)
insert into #MarketMap values(2,10)
insert into #MarketMap values(2,20)
insert into #MarketMap values(2,30)
insert into #MarketMap values(3,10)
insert into #MarketMap values(3,20)
DROP TABLE #Market
DROP TABLE #Pannel
DROP TABLE #MarketMap
最后要得到的结果是
/*
itemValuedisplayName AGB_10,CSM_20AGB(great city)_30
1shanghai 111
2beijing 111
3guangzhou110
4yangzhou000
*/
请问这个sql该怎么写
谢谢
[解决办法]
select isnull(mm.marketid,m.id) as itemValue, m.name
,max(case when mm.pannelid = 10 then 1 else 0 end) as AGB_10
,max(case when mm.pannelid = 20 then 1 else 0 end) as CSM_20
,max(case when mm.pannelid = 30 then 1 else 0 end) as [AGB(great city)_30]
from #MarketMap mm
join #Pannel p on p.id = mm.pannelid
right join #Market m on m.id = mm.marketid
group by mm.marketid,m.name,m.id
order by itemValue
go
DROP TABLE #Market
DROP TABLE #Pannel
DROP TABLE #MarketMap
/*
itemValue name AGB_10 CSM_20 AGB(great city)_30
----------- ---------- ----------- ----------- ------------------
1 Shanghai 1 1 1
2 Beijing 1 1 1
3 Guangzhou 1 1 0
4 Yangzhou 0 0 0
(4 row(s) affected)
*/
[解决办法]
create table #Market
(
Id int ,
Name varchar(10)
)
insert into #Market(Id , Name) values( '1 ', 'Shanghai ')
insert into #Market(Id , Name) values( '2 ', 'Beijing ')
insert into #Market(Id , Name) values( '3 ', 'Guangzhou ')
insert into #Market(Id , Name) values( '4 ', 'Yangzhou ')
create table #Pannel
(
Id int ,
Name varchar(50)
)
insert into #Pannel(Id , Name) values( '10 ', 'AGB ')
insert into #Pannel(Id , Name) values( '20 ', 'CSM ')
insert into #Pannel(Id , Name) values( '30 ', 'AGB(great city) ')
create table #MarketMap
(
MarketId int,
PannelId int
)
insert into #MarketMap values(1,10)
insert into #MarketMap values(1,20)
insert into #MarketMap values(1,30)
insert into #MarketMap values(2,10)
insert into #MarketMap values(2,20)
insert into #MarketMap values(2,30)
insert into #MarketMap values(3,10)
insert into #MarketMap values(3,20)
select #Market.*,t.* into #temp from #Market,(select #Pannel.name + '_ ' + cast(#MarketMap.PannelId as varchar) tname,#MarketMap.MarketId from #Pannel,#MarketMap where #Pannel.id = #MarketMap.pannelid) t where #Market.id = t.marketid
go
declare @sql varchar(8000)
set @sql = 'select id,name '
select @sql = @sql + ' , sum(case tname when ' ' ' + tname + ' ' ' then 1 else 0 end) [ ' + tname + '] '
from (select distinct tname from #temp ) as a
set @sql = @sql + ' from #temp group by id,name order by id,name '
exec(@sql)
DROP TABLE #Market
DROP TABLE #Pannel
DROP TABLE #MarketMap
drop table #temp
/*
id name AGB(great city)_30 AGB_10 CSM_20
----------- ---------- ------------------ ----------- -----------
1 Shanghai 1 1 1
2 Beijing 1 1 1
3 Guangzhou 0 1 1
*/
[解决办法]
这个需要临时表.
create table #Market
(
Id int ,
Name varchar(10)
)
insert into #Market(Id , Name) values( '1 ', 'Shanghai ')
insert into #Market(Id , Name) values( '2 ', 'Beijing ')
insert into #Market(Id , Name) values( '3 ', 'Guangzhou ')
insert into #Market(Id , Name) values( '4 ', 'Yangzhou ')
create table #Pannel
(
Id int ,
Name varchar(50)
)
insert into #Pannel(Id , Name) values( '10 ', 'AGB ')
insert into #Pannel(Id , Name) values( '20 ', 'CSM ')
insert into #Pannel(Id , Name) values( '30 ', 'AGB(great city) ')
create table #MarketMap
(
MarketId int,
PannelId int
)
insert into #MarketMap values(1,10)
insert into #MarketMap values(1,20)
insert into #MarketMap values(1,30)
insert into #MarketMap values(2,10)
insert into #MarketMap values(2,20)
insert into #MarketMap values(2,30)
insert into #MarketMap values(3,10)
insert into #MarketMap values(3,20)
go
declare @sql varchar(8000)
set @sql = 'select id,name '
select @sql = @sql + ' , sum(case tname when ' ' ' + tname + ' ' ' then 1 else 0 end) [ ' + tname + '] '
from (select distinct tname from (select #Market.*,t.* from #Market,(select #Pannel.name + '_ ' + cast(#MarketMap.PannelId as varchar) tname,#MarketMap.MarketId from #Pannel,#MarketMap where #Pannel.id = #MarketMap.pannelid) t where #Market.id = t.marketid) m ) as a
set @sql = @sql + ' from (select #Market.*,t.* from #Market,(select #Pannel.name + ' '_ ' ' + cast(#MarketMap.PannelId as varchar) tname,#MarketMap.MarketId from #Pannel,#MarketMap where #Pannel.id = #MarketMap.pannelid) t where #Market.id = t.marketid) m group by id,name order by id,name '
exec(@sql)
DROP TABLE #Market
DROP TABLE #Pannel
DROP TABLE #MarketMap
/*
id name AGB(great city)_30 AGB_10 CSM_20
----------- ---------- ------------------ ----------- -----------
1 Shanghai 1 1 1
2 Beijing 1 1 1
3 Guangzhou 0 1 1
*/
[解决办法]
create table Market
(
Id int ,
Name varchar(10)
)
insert into Market(Id , Name) values( '1 ', 'Shanghai ')
insert into Market(Id , Name) values( '2 ', 'Beijing ')
insert into Market(Id , Name) values( '3 ', 'Guangzhou ')
insert into Market(Id , Name) values( '4 ', 'Yangzhou ')
create table Pannel
(
Id int ,
Name varchar(50)
)
insert into Pannel(Id , Name) values( '10 ', 'AGB ')
insert into Pannel(Id , Name) values( '20 ', 'CSM ')
insert into Pannel(Id , Name) values( '30 ', 'AGB(great city) ')
create table MarketMap
(
MarketId int,
PannelId int
)
insert into MarketMap values(1,10)
insert into MarketMap values(1,20)
insert into MarketMap values(1,30)
insert into MarketMap values(2,10)
insert into MarketMap values(2,20)
insert into MarketMap values(2,30)
insert into MarketMap values(3,10)
insert into MarketMap values(3,20)
--查询处理
DECLARE @s nvarchar(4000)
--交叉报表处理代码头
SET @s= 'SELECT a.id,a.name '
--生成列记录水平显示的处理代码拼接(处理Item列)
SELECT @s=@s
+ ', '+QUOTENAME(isnull(b.PannelId,0))
+N '=SUM(CASE isnull(b.PannelId,0) WHEN '+rtrim(isnull(b.PannelId,0))
+N ' THEN 1 ELSE 0 END) '
from MarketMap b right join Market a on a.Id=b.MarketId
left join Pannel c on b.PannelId=c.id
GROUP BY b.PannelId
exec(@s+N ' from MarketMap b right join Market a on a.Id=b.MarketId
left join Pannel c on b.PannelId=c.id GROUP BY a.id,a.name order by a.id ')
DROP TABLE Market
DROP TABLE Pannel
DROP TABLE MarketMap
[解决办法]
create table Market
(
Id int ,
Name varchar(10)
)
insert into Market(Id , Name) values( '1 ', 'Shanghai ')
insert into Market(Id , Name) values( '2 ', 'Beijing ')
insert into Market(Id , Name) values( '3 ', 'Guangzhou ')
insert into Market(Id , Name) values( '4 ', 'Yangzhou ')
create table Pannel
(
Id int ,
Name varchar(50)
)
insert into Pannel(Id , Name) values( '10 ', 'AGB ')
insert into Pannel(Id , Name) values( '20 ', 'CSM ')
insert into Pannel(Id , Name) values( '30 ', 'AGB(great city) ')
create table MarketMap
(
MarketId int,
PannelId int
)
insert into MarketMap values(1,10)
insert into MarketMap values(1,20)
insert into MarketMap values(1,30)
insert into MarketMap values(2,10)
insert into MarketMap values(2,20)
insert into MarketMap values(2,30)
insert into MarketMap values(3,10)
insert into MarketMap values(3,20)
go
DECLARE @s nvarchar(4000)
SET @s= 'SELECT a.id,a.name '
SELECT @s=@s
+ ', '+QUOTENAME(c.name+ '_ '+rtrim(isnull(b.PannelId,0)))
+N '=SUM(CASE isnull(b.PannelId,0) WHEN '+rtrim(isnull(b.PannelId,0))
+N ' THEN 1 ELSE 0 END) '
from MarketMap b right join Market a on a.Id=b.MarketId
left join Pannel c on b.PannelId=c.id
where b.PannelId is not null
GROUP BY c.name,b.PannelId
order by b.PannelId
exec(@s+N ' from MarketMap b right join Market a on a.Id=b.MarketId
left join Pannel c on b.PannelId=c.id GROUP BY a.id,a.name order by a.id ')
DROP TABLE Market
DROP TABLE Pannel
DROP TABLE MarketMap
/*
id name AGB_10 CSM_20 AGB(great city)_30
----------- ---------- ----------- ----------- ------------------
1 Shanghai 1 1 1
2 Beijing 1 1 1
3 Guangzhou 1 1 0
4 Yangzhou 0 0 0
*/
