关联表求值的合计

[威尔亨特]

tableA
-----------------------
no month
1 2007/11
2 2007/11
3 2007/11
4 2007/12

tableB
---------------------------
no line_no type money
1 1 1 50
1 2 1 60
1 3 2 100
2 1 1 80
2 2 2 130
3 1 1 40
3 2 2 70
3 3 3 90
4 1 1 40
5 1 1 70

想要的结果：如果选 2007/11月的数据
-----------------------
month type money
2007/11 1 230 (tableB 中 第1，2，4，6条数据的和)
2007/11 2 300 (tableB 中 第3，5，7条数据的和)
2007/11 3 90 (tableB 中 第8条数据)

该怎么写sql？

[pt1314917]

month type money
2007/11 1 230 (tableB 中 第1，2，4，6条数据的和)
--------------------------------------------
为什么是 第1，2，4，6条数据的和，而不算上最后两条？
有什么区别？？

[kk19840210]

create table #tablea ([no] int,[month] varchar(10))
insert into #tablea values(1,'2007/11')
insert into #tablea values(2,'2007/11')
insert into #tablea values(3,'2007/11')
insert into #tablea values(4,'2007/12')


create table #tableb ([no] int,line_no int,[type] int,[money] int )
insert into #tableb values(1,  1 ,1,50)
insert into #tableb values(1 , 2 , 1 ,60 )
insert into #tableb values(1 , 3, 2 ,100 )
insert into #tableb values(2 ,  1, 1 , 80 )
insert into #tableb values(2 ,  2 ,2, 130)
insert into #tableb values(3 , 1 ,1,40)
insert into #tableb values(3 , 2 ,2, 70)
insert into #tableb values(3,  3 ,3  , 90 )
insert into #tableb values(4 , 1,1,40)
insert into #tableb values(5 , 1 ,1 ,70)

select a.[month],[type],[money]=sum([money]) from #tablea a inner join #tableb b on a.[no]=b.[no]  group by [month],[type] having [month]='2007/11'


month      type        money
---------- ----------- -----------
2007/11    1           230
2007/11    2           300
2007/11    3           90

(3 行受影响)

[CathySun118]

select a.month as month,b.type as type,b.money as money from tableA a,(select type,sum(money) as money from tableB where no<4 group by type ) b
where a.no=b.type group by b.type

[penglewen]

DECLARE @tA table ([no] int,[month] char(7))
insert into @tA
SELECT 1,'2007/11' union all
SELECT 2,'2007/11' union all
SELECT 3,'2007/11' union all
SELECT 4,'2007/12'

DECLARE @tB table ([no] int,line_no int,type int,[money] int)
INSERT INTO @tB
SELECT 1,1,1,50 union all
SELECT 1,2,1,60 union all
SELECT 1,3,2,100 union all
SELECT 2,1,1,80 union all
SELECT 2,2,2,130 union all
SELECT 3,1,1,40 union all
SELECT 3,2,2,70 union all
SELECT 3,3,3,90 union all
SELECT 4,1,1,40 union all
SELECT 5,1,1,70

SELECT a.[month],b.type,sum([money]) as [money] FROM @tA a , @tB b where a.[no] = b.[no] and a.[month] = '2007/11' group by a.[month],b.type

[威尔亨特]

我是楼主，问题问错啦，现在更正了
sorry，数据造错了，应该是
tableA
-----------------------
no month
1 2007/11
2 2007/11
3 2007/11
4 2007/12

tableB
---------------------------
no line_no dept type money
1 1 A 1 50
1 2 A 1 60
1 3 B 2 100
2 1 B 1 80
2 2 C 2 130
3 1 A 2 40
3 2 B 2 70
3 3 C 3 90
3 4 C 3 100
4 1 B 1 40
5 1 C 1 70

想要的结果：如果选 2007/11月的数据
-----------------------
month dept type_1_money type_2_money type_3_money
2007/11 A 110 （第1，2条的和） 40 （第6条） 0
2007/11 B 80 （第4条） 170 （第3，7条的和） 0
2007/11 C 0 130 （第5条） 190 （第8，9条的和）

选出的结果是按部门分类的，有三种 type的money ，两个表用no关联。
该怎么写sql？

[威尔亨特]

想要的结果：如果选 2007/11月的数据
-----------------------
month dept type_1_money type_2_money type_3_money
2007/11 A 110 40 0
2007/11 B 80 170 0
2007/11 C 0 130 190

[kk19840210]

select a.[month],b.[dept],type_1_money=sum(case when [type]=1 then [money] else 0 end),type_2_money=sum(case when [type]=2 then [money] else 0 end),type_3_money=sum(case when [type]=3 then [money] else 0 end) from #tablea a inner join #tableb b on a.[no]=b.[no]  group by [month],[dept] having [month]='2007/11'

[kk19840210]

create table #tablea ([no] int,[month] varchar(10))
insert into #tablea values(1,'2007/11')
insert into #tablea values(2,'2007/11')
insert into #tablea values(3,'2007/11')
insert into #tablea values(4,'2007/12')


create table #tableb ([no] int,line_no int,dept varchar(10),[type] int,[money] int )
insert into #tableb values(1,  1 ,'A',1,50)
insert into #tableb values(1 , 2 ,'A', 1 ,60 )
insert into #tableb values(1 , 3, 'B',2 ,100 )
insert into #tableb values(2 ,  1, 'B',1 , 80 )
insert into #tableb values(2 ,  2 ,'C',2, 130)

insert into #tableb values(3 , 1 ,'A',2,40)
insert into #tableb values(3 , 2 ,'B',2, 70)
insert into #tableb values(3,  3 ,'C',3  , 90 )
insert into #tableb values(3,  4 ,'C',3  , 100 )
insert into #tableb values(4 , 1,'B',1,40)
insert into #tableb values(5 , 1 ,'C',1 ,70)


select a.[month],b.[dept],type_1_money=sum(case when [type]=1 then [money] else 0 end),type_2_money=sum(case when [type]=2 then [money] else 0 end),type_3_money=sum(case when [type]=3 then [money] else 0 end) from #tablea a inner join #tableb b on a.[no]=b.[no]  group by [month],[dept] having [month]='2007/11'



month      dept       type_1_money type_2_money type_3_money
---------- ---------- ------------ ------------ ------------
2007/11    A          110          40           0
2007/11    B          80           170          0
2007/11    C          0            130          190

(3 行受影

[penglewen]

[sql]
DECLARE @tA table ([no] int,[month] char(7))
insert into @tA
SELECT 1,'2007/11' union all
SELECT 2,'2007/11' union all
SELECT 3,'2007/11' union all
SELECT 4,'2007/12'

DECLARE @tB table ([no] int,line_no int,detp char(1),type int,[money] int)
INSERT INTO @tB
SELECT 1,1,'A',1,50 union all
SELECT 1,2,'A',1,60 union all
SELECT 1,3,'B',2,100 union all
SELECT 2,1,'B',1,80 union all
SELECT 2,2,'C',2,130 union all
SELECT 3,1,'A',1,40 union all
SELECT 3,2,'B',2,70 union all
SELECT 3,3,'C',3,90 union all
SELECT 3,4,'C',3,100 union all
SELECT 4,1,'B',1,40 union all
SELECT 5,1,'C',1,70



SELECT [month],detp,
max(case type when '1' then money else 0 end) 'type_1_money',
max(case type when '2' then money else 0 end) 'type_2_money',
max(case type when '3' then money else 0 end) 'type_3_money'
FROM
(SELECT a.[month],b.type,b.detp,sum([money]) as [money] FROM
@tA a , @tB b WHERE a.[no] = b.[no]
GROUP BY a.[month],b.type,b.detp HAVING a.[month] = '2007/11') T
GROUP BY [month],detp
[/sql]

[威尔亨特]

谢谢 小虫，penglewen
不过感觉select嵌套不太好。