表结构
depid depname
1 部门A
101 部门A-1
102 部门A-2
103 部门A-3
2 部门B
3 部门C
301 部门C-1
302 部门C-2
30201部门C-2-1我怎么用一条语句可以查出
101 部门A-1
102 部门A-2
103 部门A-3
2 部门B
301 部门C-1
30201部门C-2-1
也就是在同一张表只查询出没有下级部门的数据,谢谢!
depid depname
1 部门A
101 部门A-1
102 部门A-2
103 部门A-3
2 部门B
3 部门C
301 部门C-1
302 部门C-2
30201部门C-2-1我怎么用一条语句可以查出
101 部门A-1
102 部门A-2
103 部门A-3
2 部门B
301 部门C-1
30201部门C-2-1
也就是在同一张表只查询出没有下级部门的数据,谢谢!
FROM TB T1
LEFT JOIN TB T2 ON T1.depid<>T2.depid AND T2.depid LIKE T1.depid+'%'
WHERE T2.depid IS NULL
少了个匹配符加上"_"或用长度或用depid不等
SELECT * FROM dept AS a WHERE NOT EXISTS(SELECT 1 FROM dept WHERE depid LIKE a.depid+'_%')or
SELECT * FROM dept AS a WHERE NOT EXISTS(SELECT 1 FROM dept WHERE depid!=a.depid AND depid LIKE a.depid+'_%')
DepID Int,
DepName NVarchar(100))
Insert Into @TmpData
Select 1,N'部门A'
Union
Select 101,N'部门A-1'
Union
Select 102,N'部门A-2'
Union
Select 103,N'部门A-3'
Union
Select 2,N'部门B'
Union
Select 3,N'部门C'
Union
Select 301,N'部门C-1'
Union
Select 302,N'部门C-2'
Union
Select 30201,N'部门C-2-1'
Select * From @TmpData A
Left Join @TmpData B On B.DepID/100 = A.DepID
Where IsNull(B.DepID,0) = 0