原始表:
id dictid parentid name
-----------------------------------
1 1 a
2 11 1 b
3 12 1 c
4 2 d
5 ds 2 e
6 gg 2 f
7 3 g
8 sa 3 h
9 la 3 i 查询后的表
id dictid parentid name
-----------------------------------
1 1 a
2 11 1 a
3 12 1 a
4 2 d
5 ds 2 d
6 gg 2 d
7 3 g
8 sa 3 g
9 la 3 g 只有id是主键,其余全是字符串,
怎么实现此功能。
就是只要parentid=dictid的,name都显示为dictid主信息的name
id dictid parentid name
-----------------------------------
1 1 a
2 11 1 b
3 12 1 c
4 2 d
5 ds 2 e
6 gg 2 f
7 3 g
8 sa 3 h
9 la 3 i 查询后的表
id dictid parentid name
-----------------------------------
1 1 a
2 11 1 a
3 12 1 a
4 2 d
5 ds 2 d
6 gg 2 d
7 3 g
8 sa 3 g
9 la 3 g 只有id是主键,其余全是字符串,
怎么实现此功能。
就是只要parentid=dictid的,name都显示为dictid主信息的name
a,id,a.dictid,a.parentid,isnull(b.name,a.name) as name
from 表 a left join 表 b on a.parentid=b.dictid
union all
select n.id , n.dictid , n.parentid , m.name from tb m , tb n where m.parentid is null and n.parentid is not null and n.parentid = m.dictid
order by id
declare @t table(id int,dictid varchar(10),parentid varchar(10),name varchar(10))
insert into @t select 1,'1' ,' ','a'
insert into @t select 2,'11','1','b'
insert into @t select 3,'12','1','c'
insert into @t select 4,'2' ,' ','d'
insert into @t select 5,'ds','2','e'
insert into @t select 6,'gg','2','f'
insert into @t select 7,'3' ,' ','g'
insert into @t select 8,'sa','3','h'
insert into @t select 9,'la','3','i' select
a.id,a.dictid,a.parentid,isnull(b.name,a.name) as name
from @t a left join @t b
on a.parentid=b.dictid/*
id dictid parentid name
----------- ---------- ---------- ----------
1 1 a
2 11 1 a
3 12 1 a
4 2 d
5 ds 2 d
6 gg 2 d
7 3 g
8 sa 3 g
9 la 3 g
*/
from tb a
join tb b on a.parentid=b.dictid
insert into tb values(1 , '1' , null , 'a')
insert into tb values(2 , '11', '1' , 'b')
insert into tb values(3 , '12', '1' , 'c')
insert into tb values(4 , '2' , null , 'd')
insert into tb values(5 , 'ds', '2' , 'e')
insert into tb values(6 , 'gg', '2' , 'f')
insert into tb values(7 , '3' , null , 'g')
insert into tb values(8 , 'sa', '3' , 'h')
insert into tb values(9 , 'la', '3' , 'i')
goselect * from tb where parentid is null
union all
select n.id , n.dictid , n.parentid , m.name from tb m , tb n where m.parentid is null and n.parentid is not null and n.parentid = m.dictid
order by id
drop table tb /*
id dictid parentid name
----------- ---------- ---------- ----------
1 1 NULL a
2 11 1 a
3 12 1 a
4 2 NULL d
5 ds 2 d
6 gg 2 d
7 3 NULL g
8 sa 3 g
9 la 3 g(所影响的行数为 9 行)*/
select a.id,a.dictid,a.parentid,isnull(b.name,a.name) name
from tb a
left join tb b on a.parentid=b.dictid
-----------------------------------
1 j
2 1 a
3 11 1 b
4 12 1 c
5 2 d
6 ds 2 e
7 gg 2 f
8 3 g
9 sa 3 h
10 la 3 i
insert into b values(1 , '1' ,'' , 'a')
insert into b values(2 , '11' ,'1' , 'b')
insert into b values(3 , '2' ,'' , 'd')
insert into b values(5 , 'dg' ,'2' , 'e')
goselect a.id,a.dictid,a.parentid,name=case when (select name from b where dictid=a.parentid) is null then a.name else name end from b as a
insert into tb values(1 , null, null , 'j')
insert into tb values(2 , '1' , null , 'a')
insert into tb values(3 , '11', '1' , 'b')
insert into tb values(4 , '12', '1' , 'c')
insert into tb values(5 , '2' , null , 'd')
insert into tb values(6 , 'ds', '2' , 'e')
insert into tb values(7 , 'gg', '2' , 'f')
insert into tb values(8 , '3' , null , 'g')
insert into tb values(9 , 'sa', '3' , 'h')
insert into tb values(10, 'la', '3' , 'i')
goselect * from tb where parentid is null
union all
select n.id , n.dictid , n.parentid , m.name from tb m , tb n where m.parentid is null and m.dictid is not null and n.parentid is not null and n.parentid = m.dictid
order by id
drop table tb /*
id dictid parentid name
----------- ---------- ---------- ----------
1 NULL NULL j
2 1 NULL a
3 11 1 a
4 12 1 a
5 2 NULL d
6 ds 2 d
7 gg 2 d
8 3 NULL g
9 sa 3 g
10 la 3 g(所影响的行数为 10 行)*/
insert into b values(1 , '1' ,'' , 'a')
insert into b values(2 , '11' ,'1' , 'b')
insert into b values(3 , '2' ,'' , 'd')
insert into b values(5 , 'dg' ,'2' , 'e')
goselect a.id,a.dictid,a.parentid,name=case when (select name from b where dictid=a.parentid) is null then a.name else name end from b as a
from
@t t1
left outer join
@t t2
on
(t1.parentid = t2.dictid)
insert into @t select 1,'1' ,' ','a'
insert into @t select 2,'11','1','b'
insert into @t select 3,'12','1','c'
insert into @t select 4,'2' ,' ','d'
insert into @t select 5,'ds','2','e'
insert into @t select 6,'gg','2','f'
insert into @t select 7,'3' ,' ','g'
insert into @t select 8,'sa','3','h'
insert into @t select 9,'la','3','i' SELECT dictid,parentid,
ISNULL((SELECT name FROM @t WHERE aa.parentid=dictid),aa.name) AS name
FROM @t AS aa