select ID,CODE,NAME,CJ
from(
select ID,CODE,NAME,CJ=cast(CJ as varchar),s1=0,s2=code from STUDENT
union all
select 'XX' id,'XX' code,'XX' name,'0' cj,0 sl,'z' s2
union all
select '','',name+' '+cast(sum(cj) as varchar),'',s1=1,s2=code from STUDENT group by name,code
)a order by s1,s2
from(
select ID,CODE,NAME,CJ=cast(CJ as varchar),s1=0,s2=code from STUDENT
union all
select 'XX' id,'XX' code,'XX' name,'0' cj,0 sl,'z' s2
union all
select '','',name+' '+cast(sum(cj) as varchar),'',s1=1,s2=code from STUDENT group by name,code
)a order by s1,s2
create table STUDENT(ID varchar(10),CODE varchar(10),NAME varchar(10),CJ MONEY)
insert STUDENT select '01','A','语文',10
union all select '01','B','数学',20
union all select '02','A','语文',15
union all select '03','B','数学',80
union all select '03','C','外语',60
go--查询
select ID,CODE,NAME,CJ
from(
select ID,CODE,NAME,CJ=cast(CJ as varchar),s1=0,s2=code from STUDENT
union all
select 'xx','xx','xx','0',1,''
union all
select '','',name+' '+cast(sum(cj) as varchar),'',s1=2,s2=code from STUDENT group by name,code
)a order by s1,s2
go--删除测试
drop table STUDENT/*--测试结果ID CODE NAME CJ
---------- ---------- ------------------- ---------
02 A 语文 15.00
01 A 语文 10.00
01 B 数学 20.00
03 B 数学 80.00
03 C 外语 60.00
xx xx xx 0
语文 25.00
数学 100.00
外语 60.00 (所影响的行数为 9 行)
--*/
create table 表A(ID varchar(10),NAME varchar(10),CJ MONEY)
insert 表A select '01','张山',10
union all select '02','李四',20
union all select '03','王五', 5
union all select '04','找六',10
union all select '05','小张',30
union all select '06','里强',10create table 表B(SID varchar(10),TID varchar(10))
insert 表B select '02','01'
union all select '03','01'
union all select '05','06'
go--查询
select ID=case when isnull(b.s2,0)=0 then a.ID else '' end
,T_NAME=case when isnull(b.s2,0)=0 then a.NAME else a.id end
,S_NAME=case when isnull(b.s2,0)=0 then '' else a.NAME end
,CJ=case when isnull(b.s2,0)=0 then isnull(a.CJ,0)+isnull(c.CJ,0) else 0 end
from 表A a
left join (
select TID,s1=TID,s2=0 from 表B
union
select SID,TID,1 from 表B
)b on a.id=b.tid
left join(
select b.TID,CJ=SUM(a.CJ)
from 表A a,表B b
where a.ID=b.SID
group by b.TID
)C on a.ID=c.TID
order by isnull(b.s1,a.id),b.s2
go--删除测试
drop table 表A,表B/*--测试结果ID T_NAME S_NAME CJ
---------- ---------- ---------- ---------------------
01 张山 35.0000
02 李四 .0000
03 王五 .0000
04 找六 10.0000
06 里强 40.0000
05 小张 .0000(所影响的行数为 6 行)
--*/