select * from ( select name,zy='rk',rkrq=convert(varchar(10),rkrq,120),rksl=cast(sum(rksl) as varchar),xsrq='-',xlsl='-' from table1 group by name,rkrq union all select name,zy='xs',rkrq='-',rksl='-',xsrq=convert(varchar(10),xsrq,120),xssl=cast(sum(xssl) as varchar) from table2 group by name,xsrq ) a order by name,case rkrq when '-' then xsrq else rkrq end
--测试数据 declare @table1 table(name varchar(10),rksl int,rkrq datetime) insert into @table1 select 'aaa',10,'2003-11-1' union all select 'aaa',20,'2003-11-1' union all select 'bbb',30,'2003-11-4'declare @table2 table(name varchar(10),xssl int,xsrq datetime) insert into @table2 select 'aaa',100,'2003-10-23' union all select 'bbb',300,'2003-10-31' union all select 'ccc',500,'2003-12-1'--查询处理 select * from ( select name,zy='rk',rkrq=convert(varchar(10),rkrq,120),rksl=cast(sum(rksl) as varchar(8)),xsrq='-',xlsl='-' from @table1 group by name,rkrq union all select name,zy='xs',rkrq='-',rksl='-',xsrq=convert(varchar(10),xsrq,120),xssl=cast(sum(xssl) as varchar(8)) from @table2 group by name,xsrq ) a order by name,case rkrq when '-' then xsrq else rkrq end/*--查询结果 name zy rkrq rksl xsrq xlsl ---------- ---- ---------- -------- ---------- -------- aaa xs - - 2003-10-23 100 aaa rk 2003-11-01 30 - - bbb xs - - 2003-10-31 300 bbb rk 2003-11-04 30 - - ccc xs - - 2003-12-01 500(所影响的行数为 5 行) --*/
select name,zy='rk',rkrq=convert(varchar(10),rkrq,120),rksl=cast(sum(rksl) as varchar),xsrq='-',xlsl='-'
from table1 group by name,rkrq
union all
select name,zy='xs',rkrq='-',rksl='-',xsrq=convert(varchar(10),xsrq,120),xssl=cast(sum(xssl) as varchar)
from table2 group by name,xsrq
) a order by name,case rkrq when '-' then xsrq else rkrq end
declare @table1 table(name varchar(10),rksl int,rkrq datetime)
insert into @table1
select 'aaa',10,'2003-11-1'
union all select 'aaa',20,'2003-11-1'
union all select 'bbb',30,'2003-11-4'declare @table2 table(name varchar(10),xssl int,xsrq datetime)
insert into @table2
select 'aaa',100,'2003-10-23'
union all select 'bbb',300,'2003-10-31'
union all select 'ccc',500,'2003-12-1'--查询处理
select * from (
select name,zy='rk',rkrq=convert(varchar(10),rkrq,120),rksl=cast(sum(rksl) as varchar(8)),xsrq='-',xlsl='-'
from @table1 group by name,rkrq
union all
select name,zy='xs',rkrq='-',rksl='-',xsrq=convert(varchar(10),xsrq,120),xssl=cast(sum(xssl) as varchar(8))
from @table2 group by name,xsrq
) a order by name,case rkrq when '-' then xsrq else rkrq end/*--查询结果
name zy rkrq rksl xsrq xlsl
---------- ---- ---------- -------- ---------- --------
aaa xs - - 2003-10-23 100
aaa rk 2003-11-01 30 - -
bbb xs - - 2003-10-31 300
bbb rk 2003-11-04 30 - -
ccc xs - - 2003-12-01 500(所影响的行数为 5 行)
--*/