select * from 表 where 条件union allselect 主值,'','',''…… from 表 where 主值 not in (select 主值 from 表 where 条件)
select a.主值,isnull(b.辅值,' ') from 表 a left join 表 b on a.主值=b.主值 and b.辅值=条件
select 主键, case when 条件 then col1 else null end col1, case when 条件 then col2 else null end col2, ... from table
create table T_200701018_1 ( aa varchar(20) primary key, bb varchar(20) ) insert into T_200701018_1(aa,bb) values('a','a') insert into T_200701018_1(aa,bb) values('b','b') insert into T_200701018_1(aa,bb) values('c','c')select * from T_200701018_1select aa,bb from T_200701018_1 where aa = 'a' union select aa,null bb from T_200701018_1 where aa != 'a'
select * from 表 where 条件 union all select 主值,'','' from 表 where not in 条件
select a.主值,isnull(b.辅值,' ')
from 表 a left join 表 b on a.主值=b.主值 and b.辅值=条件
case when 条件 then col1 else null end col1,
case when 条件 then col2 else null end col2,
...
from table
(
aa varchar(20) primary key,
bb varchar(20)
)
insert into T_200701018_1(aa,bb) values('a','a')
insert into T_200701018_1(aa,bb) values('b','b')
insert into T_200701018_1(aa,bb) values('c','c')select * from T_200701018_1select aa,bb from T_200701018_1 where aa = 'a'
union
select aa,null bb from T_200701018_1 where aa != 'a'
union all
select 主值,'','' from 表 where not in 条件