百分求一SQL查询问题。

Question

本帖最后由 jkkyo333 于 2011-11-01 23:00:24 编辑

Answer 1

create table D
(
   [id] int primary key identity(1,1),
   [name] varchar(50)
)
insert into D([name]) values('销售部')
insert into D([name]) values('研发部')
insert into D([name]) values('行政部')
insert into D([name]) values('运营部')
go
create table G
(
   id int primary key identity(1,1),
   [name] varchar(50)
)
insert into G([name]) values('水笔')
insert into G([name]) values('鼠标')
insert into G([name]) values('键盘')
insert into G([name]) values('笔记本')
insert into G([name]) values('A4纸')
insert into G([name]) values('餐巾纸')
go
create table DG
(
   DID int,
   GID int,
   Money int
)
insert into DG(DID,GID,Money) values(1,1,50)
insert into DG(DID,GID,Money) values(1,2,30)
insert into DG(DID,GID,Money) values(1,3,30)
insert into DG(DID,GID,Money) values(2,1,60)
insert into DG(DID,GID,Money) values(2,2,80)
insert into DG(DID,GID,Money) values(2,3,120)
insert into DG(DID,GID,Money) values(3,4,30)
insert into DG(DID,GID,Money) values(3,5,40)
insert into DG(DID,GID,Money) values(3,6,20)
insert into DG(DID,GID,Money) values(4,1,10)
insert into DG(DID,GID,Money) values(4,2,90)
insert into DG(DID,GID,Money) values(4,3,70)
insert into DG(DID,GID,Money) values(1,4,50)
insert into DG(DID,GID,Money) values(2,5,30)
insert into DG(DID,GID,Money) values(3,6,80)
insert into DG(DID,GID,Money) values(4,1,30)
insert into DG(DID,GID,Money) values(1,1,30)

Answer 2

select * from (
select DName=(select name from D where id=ta.DID),
   Gname=(select name from G where id=ta.GID),
   Money
from DG  ta
) tb
pivot
(sum(money)
 for gname in ([水笔],[鼠标],[键盘],[笔记本],[A4纸],[餐巾纸])
 ) tb
 

Answer 3

select * from (
select 部门=(select name from D where id=ta.DID),
   Gname=(select name from G where id=ta.GID),
   Money
from DG  ta
) tb
pivot
(sum(money)
 for gname in ([水笔],[鼠标],[键盘],[笔记本],[A4纸],[餐巾纸])
 ) tb
  
/*部门                                                 水笔          鼠标          键盘          笔记本         A4纸         餐巾纸
-------------------------------------------------- ----------- ----------- ----------- ----------- ----------- -----------
销售部                                                80          30          30          50          NULL        NULL
行政部                                                NULL        NULL        NULL        30          40          100
研发部                                                60          80          120         NULL        30          NULL
运营部                                                40          90          70          NULL        NULL        NULL(4 行受影响)
*/

Answer 4

;with maco as(
select D.name as Dname,G.name as Gname,Money from (
select DID,GID,Money=sum(Money) from DG
group by DID,GID) a left join D on a.DID=D.id
left join G on a.GID=G.id)select * into tt99 from macodeclare @sql varchar(8000)
set @sql = 'select Dname'
select @sql = @sql + ' , 
max(case Gname when ''' + Gname+ ''' then Money else 0 end) [' + Gname+ ']'
from (select distinct Gname from tt99) as a
set @sql = @sql + ' from tt99 group by Dname'
exec(@sql) drop table tt99
/*
Dname                                              A4纸         笔记本         餐巾纸         键盘          鼠标          水笔
-------------------------------------------------- ----------- ----------- ----------- ----------- ----------- -----------
销售部                                                0           50          0           30          30          80
行政部                                                40          30          100         0           0           0
研发部                                                30          0           0           120         80          60
运营部                                                0           0           0           70          90          40
*/

Answer 5

--直接这样也可以
declare @sql varchar(8000) set @sql = 'select Dname'
select @sql = @sql + ' , 
max(case Gname when ''' + name+ ''' then Money else 0 end) [' + name+ ']'
from (select distinct name from G) as a
set @sql = @sql + ' from (
select D.name as Dname,G.name as Gname,Money from (
select DID,GID,Money=sum(Money) from DG
group by DID,GID) a left join D on a.DID=D.id
left join G on a.GID=G.id) aa group by Dname'
exec(@sql) 

Answer 6

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#D') is null
drop table #D
Go
Create table #D([id] int,[name] nvarchar(3))
Insert #D
select 1,N'销售部' union all
select 2,N'研发部' union all
select 3,N'行政部' union all
select 4,N'运营部'
Go
--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#G') is null
drop table #G
Go
Create table #G([id] int,[name] nvarchar(6))
Insert #G
select 1,N'水笔' union all
select 2,N'鼠标' union all
select 3,N'键盘' union all
select 4,N'笔记本' union all
select 5,N'A4纸' union all
select 6,N'餐巾纸DG表'
Go
--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#DG') is null
drop table #DG
Go
Create table #DG([DID] int,[GID] int,[Money] int)
Insert #DG
select 1,1,50 union all
select 1,2,30 union all
select 1,3,30 union all
select 2,1,60 union all
select 2,2,80 union all
select 2,3,120 union all
select 3,4,30 union all
select 3,5,40 union all
select 3,6,20 union all
select 4,1,10 union all
select 4,2,90 union all
select 4,3,70 union all
select 1,4,50 union all
select 2,5,30 union all
select 3,6,80 union all
select 4,1,30 union all
select 1,1,30
Go
declare @s nvarchar(4000)
set @s='select a.[name] as 部门'
Select @s=@s+','+quotename([name])+'=sum(case when b.[GID]='+RTRIM([id])+' then [Money] end) ' from #Gexec(@s+' from #D as a inner join #DG as b on a.[ID]=b.[DID] group by a.[name]')/*
部门 水笔 鼠标 键盘 笔记本 A4纸 餐巾纸DG表
销售部 80 30 30 50 NULL NULL
行政部 NULL NULL NULL 30 40 100
研发部 60 80 120 NULL 30 NULL
运营部 40 90 70 NULL NULL NULL
*/

Answer 7

其它方法参照以下网址
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html

Answer 8

declare @sql varchar(8000)
set @sql = 'select Dname'
select @sql = @sql + ' , max(case Gname when ''' + name+ ''' then Money else 0 end)[' + name+ ']'
from (select distinct name from G) as a
set @sql = @sql +
               ' from (select 
                           D.name as Dname,G.name as Gname,Money 
                       from
                           (select DID,GID,Money=sum(Money) from DG group by DID,GID) a left join D on a.DID=D.id left join G on a.GID=G.id)t                        group by
                           Dname'
exec(@sql) 

Answer 9

动态:
declare @str1 nvarchar(1000),@str2 nvarchar(1000)
select @str1=ISNULL(@str1+',','')+'['+ltrim(id)+']',@str2=ISNULL(@str2+',','')+'['+LTRIM(id)+']['+name+']' from G
exec('select name,'+@str1+' from dg a inner join D b on a.DID=b.id
pivot (sum(a.money) for gid in('+@str1+'))t')
/*
name                                               1           2           3           4           5           6
-------------------------------------------------- ----------- ----------- ----------- ----------- ----------- -----------
销售部                                                80          30          30          50          NULL        NULL
研发部                                                60          80          120         NULL        30          NULL
行政部                                                NULL        NULL        NULL        30          40          100
运营部                                                40          90          70          NULL        NULL        NULL(4 行受影响)*/

Answer 10

OH!复制错了:
declare @str1 nvarchar(1000),@str2 nvarchar(1000)
select @str1=ISNULL(@str1+',','')+'['+ltrim(id)+']',@str2=ISNULL(@str2+',','')+'['+LTRIM(id)+']['+name+']' from G
exec('select name,'+@str2+' from dg a inner join D b on a.DID=b.id
pivot (sum(a.money) for gid in('+@str1+'))t')
/*
name                                               水笔          鼠标          键盘          笔记本         A4纸         餐巾纸
-------------------------------------------------- ----------- ----------- ----------- ----------- ----------- -----------
销售部                                                80          30          30          50          NULL        NULL
研发部                                                60          80          120         NULL        30          NULL
行政部                                                NULL        NULL        NULL        30          40          100
运营部                                                40          90          70          NULL        NULL        NULL(4 行受影响)*/

Answer 11

改name为部门:
declare @str1 nvarchar(1000),@str2 nvarchar(1000)
select @str1=ISNULL(@str1+',','')+'['+ltrim(id)+']',@str2=ISNULL(@str2+',','')+'['+LTRIM(id)+']['+name+']' from G
exec('select name 部门,'+@str2+' from dg a inner join D b on a.DID=b.id
pivot (sum(a.money) for gid in('+@str1+'))t')
/*
部门                                                 水笔          鼠标          键盘          笔记本         A4纸         餐巾纸
-------------------------------------------------- ----------- ----------- ----------- ----------- ----------- -----------
销售部                                                80          30          30          50          NULL        NULL
研发部                                                60          80          120         NULL        30          NULL
行政部                                                NULL        NULL        NULL        30          40          100
运营部                                                40          90          70          NULL        NULL        NULL(4 行受影响)*/

Answer 12

if object_id('D','U') is not null
   drop table D
go
create table D
(
 id int identity(1,1),
 name varchar(10)
)
go
insert into D (name)
select '销售部' union all
select '研发部' union all
select '行政部' union all
select '运营部'
go
if object_id('G','U') is not null
   drop table G
go
create table G
(
 id int identity(1,1),
 name varchar(10)
)
go
insert into G (name)
select '水笔' union all
select '鼠标' union all
select '键盘' union all
select '笔记本' union all
select 'A4纸' union all
select '餐巾纸'
go
if object_id('DG','U') is not null
   drop table DG
go
create table DG
(
DID int,
GID int,
Money int
)
go
insert into DG
select 1,1,50 union all
select 1,2,30 union all
select 1,3,30 union all
select 2,1,60 union all
select 2,2,80 union all
select 2,3,120 union all
select 3,4,30 union all
select 3,5,40 union all
select 3,6,20 union all
select 4,1,10 union all
select 4,2,90 union all
select 4,3,70 union all
select 1,4,50 union all
select 2,5,30 union all
select 3,6,80 union all
select 4,1,30 union all
select 1,1,30
go
declare @str varchar(max)
select @str=isnull(@str+','+char(10),'')+'['+name+']=sum(case when G.name='''+name+''' then Money else 0 end)' from G
set @str='select D.name,'+@str+'from DG inner join D on DID=D.ID inner join G on GID=G.ID group by D.name'
exec(@str)
/*
name       水笔          鼠标          键盘          笔记本         A4纸         餐巾纸
---------- ----------- ----------- ----------- ----------- ----------- -----------
销售部        80          30          30          50          0           0
行政部        0           0           0           30          40          100
研发部        60          80          120         0           30          0
运营部        40          90          70          0           0           0(4 行受影响)
*/