问题如下:
有表A和B,字段内容和样例数据分别如下:
表title T
T.title_id, T.catalog,
表T中的数据
T11072 Information technology
表sales S
S.seq_no, S.title_id, S.Sold(pcs), S.Month
表S中的数据
1 T11072 10 5
2 T11072 2 4
3 T11072 7 4
4 T11072 5 5
5 T11072 1 5
6 T11072 3 4
7 T11072 3 5
希望得到如下的结果:
T.title_id, T.catalog, Total_Sold(pcs) Month_5 Month_4
T11072 Information technology 31 19 12
Total_Sold(pcs)为S表中所有ID相同的数据的总计. 后面依此是有数据的月份的统计数据.比如5月份,4月份分别是多少.
请高手多多支招!!
谢谢!!
5 个解决方案
#1
----创建测试数据
if object_id('tbTest1') is not null
drop table tbTest1
if object_id('tbTest2') is not null
drop table tbTest2
GO
create table tbTest1(title_id varchar(10),catalog varchar(50))
insert tbTest1 select 'T11072','Information technology'
create table tbTest2(seq_no int,title_id varchar(10),Sold int,[Month] int)
insert tbTest2
select 1,'T11072', 10, 5 union all
select 2,'T11072', 2, 4 union all
select 3,'T11072', 7, 4 union all
select 4,'T11072', 5, 5 union all
select 5,'T11072', 1, 5 union all
select 6,'T11072', 3, 4 union all
select 7,'T11072', 3, 5
GO
----查询
declare @sql varchar(1000)
set @sql = 'select a.title_id,b.catalog, sum(a.Sold) as [Total_Sold(pcs)]'
select @sql = @sql + ',Month_' + rtrim([Month]) + '=sum(case [Month] when ' + rtrim([Month]) + ' then Sold else 0 end)'
from tbTest2 group by [Month]
set @sql = @sql + ' from tbTest2 as a INNER join tbTest1 as b on a.title_id = b.title_id group by a.title_id,b.catalog'
print @sql
EXEC(@sql)
----清除测试环境
drop table tbTest1,tbTest2
/*结果
title_id catalog Total_Sold(pcs) Month_4 Month_5
---------- ------------------------------------------------------------
T11072 Information technology 31 12 19
*/
if object_id('tbTest1') is not null
drop table tbTest1
if object_id('tbTest2') is not null
drop table tbTest2
GO
create table tbTest1(title_id varchar(10),catalog varchar(50))
insert tbTest1 select 'T11072','Information technology'
create table tbTest2(seq_no int,title_id varchar(10),Sold int,[Month] int)
insert tbTest2
select 1,'T11072', 10, 5 union all
select 2,'T11072', 2, 4 union all
select 3,'T11072', 7, 4 union all
select 4,'T11072', 5, 5 union all
select 5,'T11072', 1, 5 union all
select 6,'T11072', 3, 4 union all
select 7,'T11072', 3, 5
GO
----查询
declare @sql varchar(1000)
set @sql = 'select a.title_id,b.catalog, sum(a.Sold) as [Total_Sold(pcs)]'
select @sql = @sql + ',Month_' + rtrim([Month]) + '=sum(case [Month] when ' + rtrim([Month]) + ' then Sold else 0 end)'
from tbTest2 group by [Month]
set @sql = @sql + ' from tbTest2 as a INNER join tbTest1 as b on a.title_id = b.title_id group by a.title_id,b.catalog'
print @sql
EXEC(@sql)
----清除测试环境
drop table tbTest1,tbTest2
/*结果
title_id catalog Total_Sold(pcs) Month_4 Month_5
---------- ------------------------------------------------------------
T11072 Information technology 31 12 19
*/
#2
declare @t table(title_id varchar(20), catalog varchar(50))
insert into @t
select 't11072', 'information technology'
declare @s table(seq_no int,title_id varchar(20),sold money,[month] int)
insert into @s
select 1,'t11072',10,5
union all select 2,'t11072',2,4
union all select 3,'t11072',7,4
union all select 4,'t11072',5,5
union all select 5,'t11072',1,5
union all select 6,'t11072',3,4
union all select 7,'t11072',3,5
select t.*,合计=(select sum(sold)from @s group by title_id),
sum(case s.[month] when 4 then s.sold end)as '4',
sum(case s.[month] when 5 then s.sold end)as '5'
from @t t inner join @s s on t.title_id=s.title_id
group by t.title_id,t.catalog
insert into @t
select 't11072', 'information technology'
declare @s table(seq_no int,title_id varchar(20),sold money,[month] int)
insert into @s
select 1,'t11072',10,5
union all select 2,'t11072',2,4
union all select 3,'t11072',7,4
union all select 4,'t11072',5,5
union all select 5,'t11072',1,5
union all select 6,'t11072',3,4
union all select 7,'t11072',3,5
select t.*,合计=(select sum(sold)from @s group by title_id),
sum(case s.[month] when 4 then s.sold end)as '4',
sum(case s.[month] when 5 then s.sold end)as '5'
from @t t inner join @s s on t.title_id=s.title_id
group by t.title_id,t.catalog
#3
SELECT 'T.title_id' = T.[title_id],
'T.catelog' = T.[catalog],
'S.Sold(pcs)' = SUM(S.[sold(pcs)]),
'Month_5' = SUM(CASE WHEN S.[month] = 5 THEN S.[sold(pcs)] ELSE 0 END),
'Month_4' = SUM(CASE WHEN S.[month] = 4 THEN S.[sold(pcs)] ELSE 0 END)
FROM [title] T
INNER JOIN [sales] S ON T.[title_id] = S.[title_id]
GROUP BY T.[title_id], T.[catalog]
'T.catelog' = T.[catalog],
'S.Sold(pcs)' = SUM(S.[sold(pcs)]),
'Month_5' = SUM(CASE WHEN S.[month] = 5 THEN S.[sold(pcs)] ELSE 0 END),
'Month_4' = SUM(CASE WHEN S.[month] = 4 THEN S.[sold(pcs)] ELSE 0 END)
FROM [title] T
INNER JOIN [sales] S ON T.[title_id] = S.[title_id]
GROUP BY T.[title_id], T.[catalog]
#4
SELECT 'T.title_id' = T.[title_id],
'T.catelog' = T.[catalog],
'S.Sold(pcs)' = SUM(S.[sold(pcs)]),
'Month_5' = SUM(CASE WHEN S.[month] = 5 THEN S.[sold(pcs)] ELSE 0 END),
'Month_4' = SUM(CASE WHEN S.[month] = 4 THEN S.[sold(pcs)] ELSE 0 END)
FROM [title] T
INNER JOIN [sales] S ON T.[title_id] = S.[title_id]
GROUP BY T.[title_id], T.[catalog]
'T.catelog' = T.[catalog],
'S.Sold(pcs)' = SUM(S.[sold(pcs)]),
'Month_5' = SUM(CASE WHEN S.[month] = 5 THEN S.[sold(pcs)] ELSE 0 END),
'Month_4' = SUM(CASE WHEN S.[month] = 4 THEN S.[sold(pcs)] ELSE 0 END)
FROM [title] T
INNER JOIN [sales] S ON T.[title_id] = S.[title_id]
GROUP BY T.[title_id], T.[catalog]
#5
非常感谢大家,辛苦了!
非常谢谢hellowork.
case那些地方如果写死了,就没意义了.
非常谢谢hellowork.
case那些地方如果写死了,就没意义了.
#1
----创建测试数据
if object_id('tbTest1') is not null
drop table tbTest1
if object_id('tbTest2') is not null
drop table tbTest2
GO
create table tbTest1(title_id varchar(10),catalog varchar(50))
insert tbTest1 select 'T11072','Information technology'
create table tbTest2(seq_no int,title_id varchar(10),Sold int,[Month] int)
insert tbTest2
select 1,'T11072', 10, 5 union all
select 2,'T11072', 2, 4 union all
select 3,'T11072', 7, 4 union all
select 4,'T11072', 5, 5 union all
select 5,'T11072', 1, 5 union all
select 6,'T11072', 3, 4 union all
select 7,'T11072', 3, 5
GO
----查询
declare @sql varchar(1000)
set @sql = 'select a.title_id,b.catalog, sum(a.Sold) as [Total_Sold(pcs)]'
select @sql = @sql + ',Month_' + rtrim([Month]) + '=sum(case [Month] when ' + rtrim([Month]) + ' then Sold else 0 end)'
from tbTest2 group by [Month]
set @sql = @sql + ' from tbTest2 as a INNER join tbTest1 as b on a.title_id = b.title_id group by a.title_id,b.catalog'
print @sql
EXEC(@sql)
----清除测试环境
drop table tbTest1,tbTest2
/*结果
title_id catalog Total_Sold(pcs) Month_4 Month_5
---------- ------------------------------------------------------------
T11072 Information technology 31 12 19
*/
if object_id('tbTest1') is not null
drop table tbTest1
if object_id('tbTest2') is not null
drop table tbTest2
GO
create table tbTest1(title_id varchar(10),catalog varchar(50))
insert tbTest1 select 'T11072','Information technology'
create table tbTest2(seq_no int,title_id varchar(10),Sold int,[Month] int)
insert tbTest2
select 1,'T11072', 10, 5 union all
select 2,'T11072', 2, 4 union all
select 3,'T11072', 7, 4 union all
select 4,'T11072', 5, 5 union all
select 5,'T11072', 1, 5 union all
select 6,'T11072', 3, 4 union all
select 7,'T11072', 3, 5
GO
----查询
declare @sql varchar(1000)
set @sql = 'select a.title_id,b.catalog, sum(a.Sold) as [Total_Sold(pcs)]'
select @sql = @sql + ',Month_' + rtrim([Month]) + '=sum(case [Month] when ' + rtrim([Month]) + ' then Sold else 0 end)'
from tbTest2 group by [Month]
set @sql = @sql + ' from tbTest2 as a INNER join tbTest1 as b on a.title_id = b.title_id group by a.title_id,b.catalog'
print @sql
EXEC(@sql)
----清除测试环境
drop table tbTest1,tbTest2
/*结果
title_id catalog Total_Sold(pcs) Month_4 Month_5
---------- ------------------------------------------------------------
T11072 Information technology 31 12 19
*/
#2
declare @t table(title_id varchar(20), catalog varchar(50))
insert into @t
select 't11072', 'information technology'
declare @s table(seq_no int,title_id varchar(20),sold money,[month] int)
insert into @s
select 1,'t11072',10,5
union all select 2,'t11072',2,4
union all select 3,'t11072',7,4
union all select 4,'t11072',5,5
union all select 5,'t11072',1,5
union all select 6,'t11072',3,4
union all select 7,'t11072',3,5
select t.*,合计=(select sum(sold)from @s group by title_id),
sum(case s.[month] when 4 then s.sold end)as '4',
sum(case s.[month] when 5 then s.sold end)as '5'
from @t t inner join @s s on t.title_id=s.title_id
group by t.title_id,t.catalog
insert into @t
select 't11072', 'information technology'
declare @s table(seq_no int,title_id varchar(20),sold money,[month] int)
insert into @s
select 1,'t11072',10,5
union all select 2,'t11072',2,4
union all select 3,'t11072',7,4
union all select 4,'t11072',5,5
union all select 5,'t11072',1,5
union all select 6,'t11072',3,4
union all select 7,'t11072',3,5
select t.*,合计=(select sum(sold)from @s group by title_id),
sum(case s.[month] when 4 then s.sold end)as '4',
sum(case s.[month] when 5 then s.sold end)as '5'
from @t t inner join @s s on t.title_id=s.title_id
group by t.title_id,t.catalog
#3
SELECT 'T.title_id' = T.[title_id],
'T.catelog' = T.[catalog],
'S.Sold(pcs)' = SUM(S.[sold(pcs)]),
'Month_5' = SUM(CASE WHEN S.[month] = 5 THEN S.[sold(pcs)] ELSE 0 END),
'Month_4' = SUM(CASE WHEN S.[month] = 4 THEN S.[sold(pcs)] ELSE 0 END)
FROM [title] T
INNER JOIN [sales] S ON T.[title_id] = S.[title_id]
GROUP BY T.[title_id], T.[catalog]
'T.catelog' = T.[catalog],
'S.Sold(pcs)' = SUM(S.[sold(pcs)]),
'Month_5' = SUM(CASE WHEN S.[month] = 5 THEN S.[sold(pcs)] ELSE 0 END),
'Month_4' = SUM(CASE WHEN S.[month] = 4 THEN S.[sold(pcs)] ELSE 0 END)
FROM [title] T
INNER JOIN [sales] S ON T.[title_id] = S.[title_id]
GROUP BY T.[title_id], T.[catalog]
#4
SELECT 'T.title_id' = T.[title_id],
'T.catelog' = T.[catalog],
'S.Sold(pcs)' = SUM(S.[sold(pcs)]),
'Month_5' = SUM(CASE WHEN S.[month] = 5 THEN S.[sold(pcs)] ELSE 0 END),
'Month_4' = SUM(CASE WHEN S.[month] = 4 THEN S.[sold(pcs)] ELSE 0 END)
FROM [title] T
INNER JOIN [sales] S ON T.[title_id] = S.[title_id]
GROUP BY T.[title_id], T.[catalog]
'T.catelog' = T.[catalog],
'S.Sold(pcs)' = SUM(S.[sold(pcs)]),
'Month_5' = SUM(CASE WHEN S.[month] = 5 THEN S.[sold(pcs)] ELSE 0 END),
'Month_4' = SUM(CASE WHEN S.[month] = 4 THEN S.[sold(pcs)] ELSE 0 END)
FROM [title] T
INNER JOIN [sales] S ON T.[title_id] = S.[title_id]
GROUP BY T.[title_id], T.[catalog]
#5
非常感谢大家,辛苦了!
非常谢谢hellowork.
case那些地方如果写死了,就没意义了.
非常谢谢hellowork.
case那些地方如果写死了,就没意义了.