问题:
代码,名称
1 a
2 b
11 c
(代码11表示为1的下级)
通过一条句子,得出如下结果:
代码,名称,有无下级
1 a 有
2 b 无
11 c 无
建立表:
Create table TabTest(t_Code varchar(10),t_Name varchar(10));
insert into TabTest values('1','a');
insert into TabTest values('2','b');
insert into TabTest values('11','c');
+--------+--------+
| t_Code | t_Name |
+--------+--------+
| 1 | a |
| 2 | b |
| 11 | c |
+--------+--------+
Sql:
select tt1.t_Code,tt1.t_name,(
case
when exists (select 1 from TabTest tt2
where tt2.t_code like CONCAT(tt1.t_code,'%') and
tt2.t_code <> tt1.t_code )
then 'you'
else 'wu'
end
) as you_wu from TabTest tt1;
+--------+--------+--------+
| t_Code | t_name | you_wu |
+--------+--------+--------+
| 1 | a | you |
| 2 | b | wu |
| 11 | c | wu |
+--------+--------+--------+