多表复合查询 帮帮忙 急！

Question

两个表t1,t2;  t1 ,t2都有bumen,name,jlsl,wcsl字段；t1表内容是   bumen    　      name        jlsl      wcsl                                                           xiaoshou        dianyuan       15        24 
                                                          caigou           xianka        34        23 
                                                          yingxiao         xianshiqi     24        20 
                                                           bangongshi      jianpan      45        24 
                                                           shouhou         xianka        24        13
                                              t2表内容是      bumen             name      jlsl        wcsl 
                                              
                                                             xiaoshou        dianyuan    12        10
                                                             shouhou          xianka      11        4 怎么写sql语句？　将两表bumen 相同 且对应name相同的jlsl,wcsl值分别进行相减  其他的不同的bumen的name以及相对应jlsl,wcsl值 直接出即可。
                  bumen, name  都可以作为关联字段。

Answer 1

SELECT t1.bumen,t1.name,(t1.jlsl-t2.jls1) as new-jlsl,(t1.wcsl-t2.wcsl) as new-wcsl 
from t1,t2 
where (t1.bumen=t2.bumen) and (t1.name=t2.name)

Answer 2

select bumen,name,
new_jlsl=(case when count(*)>1 then (select t1.jlsl-t2.jlsl from t1,t2 where t1.bumen=t2.bumen and t1.name=t2.name and t1.bumen=a.name and t1.name=a.name) else jlsl end), 
new_wcsl=(case when count(*)>1 then (select t1.wcsl-t2.wcsl from t1,t2 where t1.bumen=t2.bumen and t1.name=t2.name and t1.bumen=a.name and t1.name=a.name) else wcsl end) from t1 a union all t2 b group by bumen,name

Answer 3

上面写错了修改下
select a.bumen,a.name,
new_jlsl=(case when count(*)>1 then (select t1.jlsl-t2.jlsl from t1,t2 where t1.bumen=t2.bumen and t1.name=t2.name and t1.bumen=a.bumen and t1.name=a.name) else a.jlsl end), 
new_wcsl=(case when count(*)>1 then (select t1.wcsl-t2.wcsl from t1,t2 where t1.bumen=t2.bumen and t1.name=t2.name and t1.bumen=a.bumen and t1.name=a.name) else a.wcsl end) from (select * from t1 union all select * from t2) a group by a.bumen,a.name

Answer 4

http://topic.csdn.net/u/20080619/16/3a7d94d8-3dd4-498c-929c-851cde61cccd.html?seed=2115416381

Answer 5

--> --> (Andy)生成测试数据 2008-06-19 20:27:41  
Set Nocount On
declare @1 table([bumen] nvarchar(10),[name] nvarchar(9),[jlsl] int,[wcsl] int)
Insert @1
select N'xiaoshou',N'dianyuan',15,24 union all
select N'caigou',N'xianka',34,23 union all
select N'yingxiao',N'xianshiqi',24,20 union all
select N'bangongshi',N'jianpan',45,24 union all
select N'shouhou',N'xianka',24,13declare @2 table([bumen] nvarchar(8),[name] nvarchar(8),[jlsl] int,[wcsl] int)
Insert @2
select N'xiaoshou',N'dianyuan',12,10 union all
select N'shouhou',N'xianka',11,4Select 
a.[bumen],
a.[name],
[jlsl]=a.[jlsl]-Isnull(b.[jlsl],0),
[wcsl]=a.[wcsl]-Isnull(b.[wcsl],0)
From @1 a 
Left Outer Join @2 b On b.[bumen]=a.[bumen] And b.[name]=a.[name]/*
bumen      name      jlsl        wcsl
---------- --------- ----------- -----------
xiaoshou   dianyuan  3           14
caigou     xianka    34          23
yingxiao   xianshiqi 24          20
bangongshi jianpan   45          24
shouhou    xianka    13          9*/