求一个sql 请指点
a表:
cp name
1 a
2 b
3 c
4 d
b:表
cp value
1 aa
2 bb
3 cc
3 ccc
4 dd

求c:表
cp name value
1 a aa
2 b bb
3 c cc
ccc
4 d dd



------解决方案--------------------
declare @a table(cp int,name varchar(10))
insert into @a select 1, 'a '
insert into @a select 2, 'b '
insert into @a select 3, 'c '
insert into @a select 4, 'd '
declare @b table(cp int,value varchar(10))
insert into @b select 1, 'aa '
insert into @b select 2, 'bb '
insert into @b select 3, 'cc '
insert into @b select 3, 'ccc '
insert into @b select 4, 'dd '

select
(case when exists(select 1 from @b where cp=a.cp and value <b.value) then ' ' else rtrim(a.cp) end) n_cp,
(case when exists(select 1 from @b where cp=a.cp and value <b.value) then ' ' else rtrim(a.name) end) name,
b.value
from
@a a,@b b
where
a.cp=b.cp
order by
a.cp,b.value

/*
n_cp name value
------------ ---------- ----------
1 a aa
2 b bb
3 c cc
ccc
4 d dd
*/
------解决方案--------------------
create table t1(cp int,name varchar(10))

create table t2(cp int,name varchar(10))


insert t1
select 1, 'a ' union all
select 2, 'b ' union all
select 3, 'c ' union all
select 4, 'd '

insert t2
select 1, 'aa ' union all
select 2, 'bb ' union all
select 3, 'cc ' union all
select 3, 'ccc ' union all
select 4, 'dd '


select
case when exists(select cp from t2 where cp=a.cp and name <b.name) then ' ' else rtrim(a.cp) end ,
case when exists(select name from t2 where cp=a.cp and name <b.name) then ' ' else rtrim(a.name) end,
b.name
from t1 a,t2 b
where a.cp=b.cp

------解决方案--------------------
換個思路，逐條用exists判斷，似乎效率不夠優，試試用關聯的方法。

借用红尘的數據

declare @a table(cp int,name varchar(10))
insert into @a select 1, 'a '
insert into @a select 2, 'b '
insert into @a select 3, 'c '
insert into @a select 4, 'd '
declare @b table(cp int,value varchar(10))
insert into @b select 1, 'aa '
insert into @b select 2, 'bb '
insert into @b select 3, 'cc '
insert into @b select 3, 'ccc '
insert into @b select 4, 'dd '

Select
(Case When B.value != C.value Then ' ' Else Rtrim(A.cp) End) cp,
(Case When B.value != C.value Then ' ' Else A.name End) name,
B.value
From
@a A
Inner Join
@b B
On
A.cp = B.cp
Left Join
(Select cp, Min(value) As value From @b Group By cp) C
On A.cp = C.cp
--Result
/*
1 a aa
2 b bb
3 c cc
ccc
4 d dd
*/