一共两个数据库
表1:pa
pa_id pa_name pb_id
1 cloth 10
1 cloth 11
2 cap 20
2 cap 21表2:pb
pb_id pb_name
10 bigcloth
11 minicloth
20 bigcap
21 minicap通过SQL语句查询得到结果,如下表: pa_id pa_name pb_name
1 cloth bigcloth
minicloth
2 cap bigcap
minicap
表1:pa
pa_id pa_name pb_id
1 cloth 10
1 cloth 11
2 cap 20
2 cap 21表2:pb
pb_id pb_name
10 bigcloth
11 minicloth
20 bigcap
21 minicap通过SQL语句查询得到结果,如下表: pa_id pa_name pb_name
1 cloth bigcloth
minicloth
2 cap bigcap
minicap
from pa a,pb b
where a.pa_id =b.pa_id
select a.pa_id,a.pa_name,b.pb_name
from pa a,pb b
where a.pb_id =b.pb_id
on a.pb_id=b.pb_id
这样只能得到pa_id pa_name pb_name
----------- ---------- --------------------
1 cloth bigcloth
1 cloth minicloth
2 cap bigcap
2 cap minicap
declare @pb table(pb_id int,pb_name varchar(20))
Insert into @pa
Select 1 ,'cloth', 10
union Select 1 ,'cloth', 11
union Select 2 ,'cap', 20
union Select 2 ,'cap', 21Insert into @pb
Select 10 ,'bigcloth'
union Select 11 ,'minicloth'
union Select 20 ,'bigcap'
union Select 21 ,'minicap'
Select pa_id =(case when exists(Select 1 from (Select pa_id,pa_name,pb_name
from @pa x inner Join @pb y on x.pb_id=y.pb_id) as t
where pa_id=a.pa_id and pa_name=a.pa_name and pb_name<b.pb_name)
then '' else rtrim(a.pa_id) end),
pa_name=(case when exists(Select 1 from (Select pa_id,pa_name,pb_name
from @pa x inner Join @pb y on x.pb_id=y.pb_id) as t
where pa_id=a.pa_id and pa_name=a.pa_name and pb_name<b.pb_name)
then '' else pa_name end), b.pb_name
from @pa as a inner join @pb as b on a.pb_id=b.pb_id
having grouping(a.pa_name)=1 and grouping(a.pa_id)=1Select (Case When Exists(Select 1 From @tb Where 类别=A.类别 And 名称<A.名称)
Then '。' else 类别 End) As 类别,名称
From (select TOP 100 Percent * From @tb Order By 类别,名称) as A
--结果
pa_id pa_name pb_name
---------------------------------------
1 cloth bigcloth
minicloth
2 cap bigcap
minicap
declare @pb table(pb_id int,pb_name varchar(20))
Insert into @pa
Select 1 ,'cloth', 10
union Select 1 ,'cloth', 11
union Select 2 ,'cap', 20
union Select 2 ,'cap', 21Insert into @pb
Select 10 ,'bigcloth'
union Select 11 ,'minicloth'
union Select 20 ,'bigcap'
union Select 21 ,'minicap'
Select pa_id =(case when exists(Select 1 from (Select pa_id,pa_name,pb_name
from @pa x inner Join @pb y on x.pb_id=y.pb_id) as t
where pa_id=a.pa_id and pa_name=a.pa_name and pb_name<b.pb_name)
then '' else rtrim(a.pa_id) end),
pa_name=(case when exists(Select 1 from (Select pa_id,pa_name,pb_name
from @pa x inner Join @pb y on x.pb_id=y.pb_id) as t
where pa_id=a.pa_id and pa_name=a.pa_name and pb_name<b.pb_name)
then '' else pa_name end), b.pb_name
from @pa as a inner join @pb as b on a.pb_id=b.pb_id
--结果
pa_id pa_name pb_name
---------------------------------------
1 cloth bigcloth
minicloth
2 cap bigcap
minicap