select case when class_ID=(select min(class_ID) from b where name_id=a.id) then cast(a.id as varchar) else '' end as id,
case when class_ID=(select min(class_ID) from b where name_id=a.id) then a.name else '' end as name,
b.content
from a , b where a.id = b.name_id
union all
select cast(a.id as varchar) id, a.name , '' content from a where id not in (select name_id from B)
case when class_ID=(select min(class_ID) from b where name_id=a.id) then a.name else '' end as name,
b.content
from a , b where a.id = b.name_id
union all
select cast(a.id as varchar) id, a.name , '' content from a where id not in (select name_id from B)
case when class_ID=(select min(class_ID) from b where name_id=a.id) then a.name else '' end as name, --如果是每个id中最小的class_id的那行,则显示name,否则显示''
b.content
from a , b where a.id = b.name_id
union all
select cast(a.id as varchar) id, a.name , '' content from a where id not in (select name_id from B) --如果a.id在b中不存在,则显示content为''估计你的意思是如果记录重复出现,最小的clsss_id显示所有记录,其他显示为空.以下是个单表的例,你的是两表联合查询而已.
declare @t table(序号 int,货物 varchar(100),数量 int)
insert into @t
select 1, 'A' , 30 union
select 2 , 'a' , 10 union
select 3 , 'a' , -5 union
select 4 , 'B' , 20 union
select 5 , 'b' , 100 union
select 6 , 'C' , 1 union
select 7 , 'c' , 98 --原数据
select * from @t
/*序号 货物 数量
----------- ---------------------------------------------------------------------------------------------------- -----------
1 A 30
2 a 10
3 a -5
4 B 20
5 b 100
6 C 1
7 c 98(所影响的行数为 7 行)
*/--要显示为:
select 序号,case when 序号=(select min(序号) from @t where 货物=a.货物) then 货物 else '' end as 货物, 数量
from @t a
order by 序号/*
序号 货物 数量
----------- ---------------------------------------------------------------------------------------------------- -----------
1 A 30
2 10
3 -5
4 B 20
5 100
6 C 1
7 98(所影响的行数为 7 行)*/