求sql两表合并方法@@@@@@@

Question

使用select into导入 或是 union联合查询

Answer 1

use tempdb
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[pactNo] varchar(5),[quantity] int,[contractAmout] int,[amount] int,[ReceivablesDate] datetime)
insert [tb] select 1,'001',9,10000,100,'2008-11-14'
union all select 2,'001',9,10000,100,'2008-12-14'
union all select 3,'001',9,10000,100,'2009-01-14'
union all select 4,'001',9,10000,100,'2009-02-14'
union all select 5,'001',9,10000,100,'2009-03-14'
union all select 6,'001',9,10000,100,'2008-12-16'
union all select 7,'001',9,10000,100,'2009-01-16'
union all select 8,'001',9,10000,100,'2009-02-16'
union all select 9,'001',9,10000,100,'2009-03-16'
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([pactNo] nvarchar(3),[quantity] int,[contractAmout] int,[updateTime] Datetime)
Insert #T
select N'001',12,15000,'2009-02-14' union all
select N'001',30,50000,'2009-03-14' union all
select N'001',35,55000,'2009-03-25'
Go
---查询---
select 
pactNo,quantity,contractAmout,
convert(varchar(7),ReceivablesDate,120) as ReceivablesDate,sum(amount) as amount 
into # 
from tb 
group by pactNo,quantity,contractAmout,convert(varchar(7),ReceivablesDate,120)select
  isnull(日期,N'合同累计收款') as 日期,
  合同编号,
  合同人数,
  合同金额,
  本月收款,
  累计收款
from
(
select
  日期=ReceivablesDate,
  合同编号=pactNo,
  合同人数=ltrim(quantity),
  合同金额=ltrim(contractAmout),
  本月收款=ltrim(amount),
  累计收款=ltrim((select sum(amount) from # where pactNo=t.pactNo and left(ReceivablesDate,4)=left(t.ReceivablesDate,4) and ReceivablesDate <=t.ReceivablesDate)),
  ord=1
from # t
union all
select ltrim(year(ReceivablesDate))+N'总计','','','','',sum(amount),ord=2 from tb group by ltrim(year(ReceivablesDate))+N'总计' with rollup
union all
select convert(varchar(10),updateTime,120),pactNo,quantity,contractAmout,'','',ord=0 from #T
) t
order by left(日期,7),ord drop table #日期             合同编号  合同人数        合同金额        本月收款         累计收款
-------------- ----- ----------- ----------- ------------ -----------
合同累计收款               0           0                        900
2008-11        001   9           10000       100          100
2008-12        001   9           10000       200          300
2008总计               0           0                        300
2009-01        001   9           10000       200          200
2009-02-14     001   12          15000                    0
2009-02        001   9           10000       200          400
2009-03-14     001   30          50000                    0
2009-03-25     001   35          55000                    0
2009-03        001   9           10000       200          600
2009总计               0           0                        600(11 個資料列受到影響)

Answer 2

if object_id('[tb]') is not null drop table [tb] 
go 
create table [tb]([ID] int,[pactNo] varchar(5),[quantity] int,[contractAmout] int,[amount] int,[ReceivablesDate] datetime) 
insert [tb] select 1,'001',9,10000,100,'2008-11-14' 
union all select 2,'001',9,10000,100,'2008-12-14' 
union all select 3,'001',9,10000,100,'2009-01-14' 
union all select 4,'001',9,10000,100,'2009-02-14' 
union all select 5,'001',9,10000,100,'2009-03-14' 
union all select 6,'001',9,10000,100,'2008-12-16' 
union all select 7,'001',9,10000,100,'2009-01-16' 
union all select 8,'001',9,10000,100,'2009-02-16' 
union all select 9,'001',9,10000,100,'2009-03-16' 
if object_id('[tc]') is not null drop table [tc]
go
create table [tc]([pactNo] varchar(3),[quantity] int,[contractAmout] int,[updateTime] datetime)
insert [tc]
select '001',12,15000,'2009-02-14' union all
select '001',30,50000,'2009-03-14' union all
select '001',35,55000,'2009-03-25'---查询--- 
select pactNo,quantity,contractAmout,convert(varchar(7),ReceivablesDate,120) as ReceivablesDate,sum(amount) as amount into # from tb group by pactNo,quantity,contractAmout,convert(varchar(7),ReceivablesDate,120) select 
  isnull(日期,'合同累计收款') as 日期, 
  合同编号, 
  合同人数, 
  合同金额, 
  本月收款, 
  累计收款 
from 
( 
select 
  日期=ReceivablesDate, 
  合同编号=pactNo, 
  合同人数=ltrim(quantity), 
  合同金额=ltrim(contractAmout), 
  本月收款=ltrim(amount), 
  累计收款=ltrim((select sum(amount) from # where pactNo=t.pactNo and left(ReceivablesDate,4)=left(t.ReceivablesDate,4) and ReceivablesDate <=t.ReceivablesDate)) 
from # t 
union all 
select ltrim(year(ReceivablesDate))+'总计','','','','',sum(amount) from tb group by ltrim(year(ReceivablesDate))+'总计' with rollup 
union all
select convert(varchar(10),updateTime,120),pactNo,quantity,contractAmout,'','' from tc
) t 
order by left(isnull(日期,'合同累计收款'),7),len(日期) desc/**
日期               合同编号  合同人数        合同金额        本月收款         累计收款        
---------------- ----- ----------- ----------- ------------ ----------- 
2008-11          001   9           10000       100          100
2008-12          001   9           10000       200          300
2008总计                 0           0                        300
2009-01          001   9           10000       200          200
2009-02-14       001   12          15000                    0
2009-02          001   9           10000       200          400
2009-03-14       001   30          50000                    0
2009-03-25       001   35          55000                    0
2009-03          001   9           10000       200          600
2009总计                 0           0                        600
合同累计收款                 0           0                        900（所影响的行数为 11 行）
**/

Answer 3

union all
orselect into