大家请进,紧急,SQL语句问题-如何在一对主表和明细表中查找出这样的结果数据来?
大家好.
问题如下:
有表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月份分别是多少.

请高手多多支招!!

谢谢!!


[解决办法]
----创建测试数据
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
*/

[解决办法]
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
[解决办法]
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]