求一个汇总查询问题 俺没分了 急用,先谢谢了 ...
A 结果再加个
a 表
id name type my1 my2 us
-----------------------------------
1 aa taa 20 30 1001
2 bb tbb 20 30 1002
3 cc taa 30 40 1001
4 dd tbb 40 50 1001
5 ee taa 30 40 1002
6 ff taa 50 60 1002
...... ......
t 表
tn name
-----------------------
taa A型
tbb B型
...... ......
us 表
id name
----------------
1001 USA
1002 USB
...... ......
想要结果:
type my1 my2 us1 my1_us1 my2_us1 us2 my1_us1 my2_us1 ......
-----------------------------------
A型 130 170 USA 50 70 USB 80 90
B型 60 80 USA 40 50 USB 20 30
......
-----------------------------------
总计 90 130 USA 90 130 USB 100 120
[解决办法]
create table A(id int, name varchar(10), type varchar(10), my1 int, my2 int, us varchar(10))
insert A select 1, 'aa ', 'taa ', 20, 30, '1001 '
union all select 2, 'bb ', 'tbb ', 20, 30, '1002 '
union all select 3, 'cc ', 'taa ', 30, 40, '1001 '
union all select 4, 'dd ', 'tbb ', 40, 50, '1001 '
union all select 5, 'ee ', 'taa ', 30, 40, '1002 '
union all select 6, 'ff ', 'taa ', 50, 60, '1002 '
go
create table T(tn varchar(10), name varchar(10))
insert T select 'taa ', 'A型 '
union all select 'tbb ', 'B型 '
go
create table us(id varchar(10), name varchar(10))
insert us select '1001 ', 'USA '
union all select '1002 ', 'USB '
go
declare @sql varchar(8000)
set @sql= 'select type=T.name, my1=sum(my1), my2=sum(my2), '
select
@sql=@sql+quotename( 'us '+id)+ '= '+quotename(name, ' ' ' ')+ ', '+
quotename( 'my1_us '+id)+ '=sum(case when us= '+quotename(id, ' ' ' ')+ ' then my1 else 0 end), '+
quotename( 'my2_us '+id)+ '=sum(case when us= '+quotename(id, ' ' ' ')+ ' then my2 else 0 end), '
from us
select @sql=left(@sql, len(@sql)-1),
@sql=@sql+ ' from A inner join T on A.type=T.tn group by T.tn, T.name '
exec(@sql)
--result
type my1 my2 us1001 my1_us1001 my2_us1001 us1002 my1_us1002 my2_us1002
---------- ----------- ----------- ------ ----------- ----------- ------ ----------- -----------
A型 130 170 USA 50 70 USB 80 100
B型 60 80 USA 40 50 USB 20 30
[解决办法]
create table A(id int, name varchar(10), type varchar(10), my1 int, my2 int, us varchar(10))
insert A select 1, 'aa ', 'taa ', 20, 30, '1001 '
union all select 2, 'bb ', 'tbb ', 20, 30, '1002 '
union all select 3, 'cc ', 'taa ', 30, 40, '1001 '
union all select 4, 'dd ', 'tbb ', 40, 50, '1001 '
union all select 5, 'ee ', 'taa ', 30, 40, '1002 '
union all select 6, 'ff ', 'taa ', 50, 60, '1002 '
go
create table T(tn varchar(10), name varchar(10))
insert T select 'taa ', 'A型 '
union all select 'tbb ', 'B型 '
go
create table us(id varchar(10), name varchar(10))
insert us select '1001 ', 'USA '
union all select '1002 ', 'USB '
go
declare @sql varchar(4000)
select @sql=isnull(@sql, ' ')+ ',[us '+id+ ']= ' ' '+name+ ' ' ', '
+ '[my1_us '+id+ ']=sum( case us when '+id+ ' then my1 else 0 end), '
+ '[my2_us '+id+ ']=sum( case us when '+id+ ' then my2 else 0 end) '
from us
select @sql= 'select type=isnull(T.name, ' '合计 ' '), my1=sum(my1), my2=sum(my2) '+@sql
+ ' from A inner join T on A.type=T.tn group by T.tn, T.name WITH ROLLUP having grouping(T.tn)=1 or grouping(T.name)=0 '
exec(@sql)
type my1 my2 us1001 my1_us1001 my2_us1001 us1002 my1_us1002 my2_us1002
---------- ----------- ----------- ------ ----------- ----------- ------ ----------- -----------
A型 130 170 USA 50 70 USB 80 100
B型 60 80 USA 40 50 USB 20 30
合计 190 250 USA 90 120 USB 100 130
(3 行受影响)
[解决办法]
create table a(id int, name varchar(2), [type] varchar(3),
my1 int, my2 int, us varchar(4), date datetime)
insert a select 1, 'aa ', 'taa ', 20, 30, '1001 ', '06-12-1 '
union all select 2, 'bb ', 'tbb ', 20, 30, '1002 ', '06-12-5 '
union all select 3, 'cc ', 'taa ', 30, 40, '1001 ', '06-12-11 '
union all select 4, 'dd ', 'tbb ', 40, 50, '1001 ', '06-12-21 '
union all select 5, 'ee ', 'taa ', 30, 40, '1002 ', '06-12-30 '
union all select 6, 'ff ', 'taa ', 50, 60, '1002 ', '07-1-3 '
go
create table T(tn varchar(10), name varchar(10))
insert T select 'taa ', 'A型 '
union all select 'tbb ', 'B型 '
go
create table us(id varchar(10), name varchar(10))
insert us select '1001 ', 'USA '
union all select '1002 ', 'USB '
go
declare @sql varchar(4000)
select @sql=isnull(@sql, ' ')+ ',[us '+id+ ']= ' ' '+name+ ' ' ', '
+ '[my1_us '+id+ ']=sum( case us when '+id+ ' then my1 else 0 end), '
+ '[my2_us '+id+ ']=sum( case us when '+id+ ' then my2 else 0 end) '
from us
select @sql= 'select type=isnull(T.name, ' '合计 ' '), my1=sum(my1), my2=sum(my2) '+@sql
+ ' from A inner join T on A.type=T.tn where date between ' '2006-12-01 00:00:00 ' '
and ' '2006-12-31 23:59:59 ' '
group by T.tn, T.name WITH ROLLUP having grouping(T.tn)=1 or grouping(T.name)=0 '
exec(@sql)
type my1 my2 us1001 my1_us1001 my2_us1001 us1002 my1_us1002 my2_us1002
---------- ----------- ----------- ------ ----------- ----------- ------ ----------- -----------
A型 80 110 USA 50 70 USB 30 40
B型 60 80 USA 40 50 USB 20 30
合计 140 190 USA 90 120 USB 50 70
(3 行受影响)
