有三表如下:
A表
name record_A 张三 1210
李四 4060
高骏 005090
张三 1210
张三 1210
王民 1077
王大学 104090
李四 4060
陈小华 1210
李大华 1210
张三 1210
李大华 1210
林大三 53127
黄晓华 446024
马宁 7740
B表 name record_B 陈小华 1210
农家乐 l403072
陈小华 749024
张三 1210
李华 4024
张三三 1210104
张三 121023
张三 1210C表 name record_C 农林 4044
黄向东 442740
张三 1210
周宝 7730
张三 1210
张三三 1210
李宏 4421
陈中国 1210
张三三 1210
张三 43201
欲通过A、B、C表的字段record_A、record_B、record_C查询某一代码,把查询到三表的name字段记录输出,要求消除相同行,另加上三个BS_A、BS_B、BS_C标识字段,标上各表的代码,如A表标识为'A',B表标识为'B',C表标识为'C'。如查询三表record_A、record_B、record_C字段的值等于1210时,三表相同部分,如下:
A表
name record_A 张三 1210
张三 1210
张三 1210
陈小华 1210
李大华 1210
张三 1210
李大华 1210
B表 name record_B 陈小华 1210
张三 1210
张三 1210C表 name record_C
张三 1210
张三 1210
张三三 1210
张三三 1210 要求输出结果集如下:
name BS_A BS_B BS_C
张三 A B C /*张三在A、B、C表有记录,分别标上A、B、C。*/
陈小华 A B /*陈小华在A、B表有记录,分别标上A、B。*/
李大华 A /*李大华在A表有记录,标上A。*/
张三三 C /*张三三在C表有记录,标上C。*/
要得到这样结果集,过程语句如何写?谢谢!!!
A表
name record_A 张三 1210
李四 4060
高骏 005090
张三 1210
张三 1210
王民 1077
王大学 104090
李四 4060
陈小华 1210
李大华 1210
张三 1210
李大华 1210
林大三 53127
黄晓华 446024
马宁 7740
B表 name record_B 陈小华 1210
农家乐 l403072
陈小华 749024
张三 1210
李华 4024
张三三 1210104
张三 121023
张三 1210C表 name record_C 农林 4044
黄向东 442740
张三 1210
周宝 7730
张三 1210
张三三 1210
李宏 4421
陈中国 1210
张三三 1210
张三 43201
欲通过A、B、C表的字段record_A、record_B、record_C查询某一代码,把查询到三表的name字段记录输出,要求消除相同行,另加上三个BS_A、BS_B、BS_C标识字段,标上各表的代码,如A表标识为'A',B表标识为'B',C表标识为'C'。如查询三表record_A、record_B、record_C字段的值等于1210时,三表相同部分,如下:
A表
name record_A 张三 1210
张三 1210
张三 1210
陈小华 1210
李大华 1210
张三 1210
李大华 1210
B表 name record_B 陈小华 1210
张三 1210
张三 1210C表 name record_C
张三 1210
张三 1210
张三三 1210
张三三 1210 要求输出结果集如下:
name BS_A BS_B BS_C
张三 A B C /*张三在A、B、C表有记录,分别标上A、B、C。*/
陈小华 A B /*陈小华在A、B表有记录,分别标上A、B。*/
李大华 A /*李大华在A表有记录,标上A。*/
张三三 C /*张三三在C表有记录,标上C。*/
要得到这样结果集,过程语句如何写?谢谢!!!
BS_A=(case when exists(select 1 from A where [name]=t.[name] and record_A
=t.record_A) then 'A' else ''end),
BS_B=(case when exists(select 1 from B where [name]=t.[name] and record_B
=t.record_A) then 'A' else ''end),
BS_C=(case when exists(select 1 from C where [name]=t.[name] and record_C
=t.record_A) then 'A' else ''end)
from (select distinct [name],record_A from A where record_A='1210')t
BS_A=(case when exists(select 1 from A where [name]=t.[name] and record_A
=t.record_A) then 'A' else ''end),
BS_B=(case when exists(select 1 from B where [name]=t.[name] and record_B
=t.record_A) then 'B' else ''end),
BS_C=(case when exists(select 1 from C where [name]=t.[name] and record_C
=t.record_A) then 'C' else ''end)
from (select distinct [name],record_A from A where record_A='1210')t
BS_A=max(case when a.name is null then '' else a.name end),
BS_B=max(case when b.name is null then '' else b.name end),
BS_C=max(case when c.name is null then '' else c.name end)
from ta a inner join tb b
on a.record_a=b.record_b
inner join tc c
on a.record_a=c.record_c
where a.record_a=1210
group by name
改一下:select [name],
BS_A=(case when exists(select 1 from A where [name]=t.[name] and record_A
=t.record_A) then 'A' else ''end),
BS_B=(case when exists(select 1 from B where [name]=t.[name] and record_B
=t.record_A) then 'B' else ''end),
BS_C=(case when exists(select 1 from C where [name]=t.[name] and record_C
=t.record_A) then 'C' else ''end)
from (
select [name],record_A from A where record_A='1210'
union
select [name],record_B from B where record_B='1210'
union
select [name],record_C from C where record_C='1210'
)t
select [name]=case when a.name is not null then a.name
when b.name is not null then b.name
else c.name end,
BS_A=max(case when a.name is null then '' else 'A' end),
BS_B=max(case when b.name is null then '' else 'B' end),
BS_C=max(case when c.name is null then '' else 'C' end)
from ta a inner join tb b
on a.record_a=b.record_b
inner join tc c
on a.record_a=c.record_c
where a.record_a=1210
group by name
BS_A=(case when exists(select 1 from A where [name]=t.[name] and record_A
=t.record_A) then 'A' else ''end),
BS_B=(case when exists(select 1 from B where [name]=t.[name] and record_B
=t.record_A) then 'B' else ''end),
BS_C=(case when exists(select 1 from C where [name]=t.[name] and record_C
=t.record_A) then 'C' else ''end)
from
(select distinct [name],record_A from A where record_A='1210'
union select distinct [name],record_B from B where record_B='1210'
union select distinct [name],record_C from C where record_C='1210')t
when b.name is not null then b.name
else c.name end,
BS_A=max(case when a.name is null then '' else 'A' end),
BS_B=max(case when b.name is null then '' else 'B' end),
BS_C=max(case when c.name is null then '' else 'C' end)
from ta a inner join tb b
on a.record_a=b.record_b
inner join tc c
on a.record_a=c.record_c
where a.record_a=1210
group by case when a.name is not null then a.name
when b.name is not null then b.name
else c.name end
to: lsqkeke(可可) ( ) 信誉:100 select [name],
BS_A=(case when exists(select 1 from A where [name]=t.[name] and record_A
=t.record_A) then 'A' else ''end),
BS_B=(case when exists(select 1 from B where [name]=t.[name] and record_B
=t.record_A) then 'B' else ''end),
BS_C=(case when exists(select 1 from C where [name]=t.[name] and record_C
=t.record_A) then 'C' else ''end)
from (
select [name],record_A from A where record_A='1210'
union
select [name],record_B from B where record_B='1210'
union
select [name],record_C from C where record_C='1210'
)t
使用了union
select [name],record_A from A where record_A='1210'
union
select [name],record_B from B where record_B='1210'
union
select [name],record_C from C where record_C='1210'
)t
会提示
列名'record_B'、'record_C'无效
to: wgsasd311(自强不息) ( ) 信誉:100select [name]=case when a.name is not null then a.name
when b.name is not null then b.name
else c.name end,
BS_A=max(case when a.name is null then '' else 'A' end),
BS_B=max(case when b.name is null then '' else 'B' end),
BS_C=max(case when c.name is null then '' else 'C' end)
from ta a inner join tb b
on a.record_a=b.record_b
inner join tc c
on a.record_a=c.record_c
where a.record_a=1210
group by case when a.name is not null then a.name
when b.name is not null then b.name
else c.name end会得到这样结果集:name BS_A BS_B BS_C
-------------------- ---- ---- ----
张三三 A B C
陈小华 A B C
李大华 A B C
张三 A B C(4 row(s) affected)