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
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'
godeclare @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=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 '
--print @sql
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(2 行受影响)
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 行受影响)
id name type my1 my2 us date
--------------------------------------------
1 aa taa 20 30 1001 06-12-1
2 bb tbb 20 30 1002 06-12-5
3 cc taa 30 40 1001 06-12-11
4 dd tbb 40 50 1001 06-12-21
5 ee taa 30 40 1002 06-12-30
6 ff taa 50 60 1002 07-1-3
...... ......
如想要06年12月1日--06月12月31日结果:type my1 my2 us1 my1_us1 my2_us1 us2 my1_us1 my2_us1 ......
-----------------------------------------------------------------------
A型 80 110 USA 50 70 USB 30 30
B型 60 80 USA 40 50 USB 20 30
......
-----------------------------------------------------------------------
总计 90 130 USA 90 130 USB 50 60
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'
godeclare @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 行受影响)