DECLARE @tb TABLE(A CHAR(20)) INSERT @tb SELECT '01' UNION SELECT '0102' UNION SELECT '010201 *'SELECT * FROM @tb WHERE A LIKE '%*%' SELECT * FROM @tb WHERE CHARINDEX('*',A)>0 SELECT * FROM @tb WHERE A LIKE '%[0-9] [*]%'
select * from table1 where A like '%*'
Select * From table1 Where Right(A,1) = '*' select * from table1 where A like '%*'
Create Table table1 (A Varchar(20)) Insert table1 Select '01' Union All Select '0102' Union All Select '010201' Union All Select '0103' Union All Select '010301' Union All Select '01030102' Union All Select '010302' Union All Select '0104' GO Select * From table1 A Where Not Exists(Select 1 From table1 Where A Like A.A+'%' And A<>A.A) GO Drop Table table1 GO --Result /* A 010201 01030102 010302 0104 */
Create Table table1 (A Varchar(20)) Insert table1 Select '01' Union All Select '0102' Union All Select '010201' Union All Select '0103' Union All Select '010301' Union All Select '01030102' Union All Select '010302' Union All Select '0104' Union All Select '02' Union All Select '0201' GO --得到最底層的節點 Select * From table1 A Where Not Exists(Select 1 From table1 Where A Like A.A+'%' And A<>A.A) --得到最上層的節點 Select * From table1 A Where Not Exists(Select 1 From table1 Where A.A Like A+'%' And A<>A.A) GO Drop Table table1 GO --Result /* A 010201 01030102 010302 0104 0201A 01 02 */
INSERT @tb
SELECT
'01'
UNION SELECT
'0102'
UNION SELECT
'010201 *'SELECT * FROM @tb WHERE A LIKE '%*%'
SELECT * FROM @tb WHERE CHARINDEX('*',A)>0
SELECT * FROM @tb WHERE A LIKE '%[0-9] [*]%'
select * from table1 where A like '%*'
(A Varchar(20))
Insert table1 Select '01'
Union All Select '0102'
Union All Select '010201'
Union All Select '0103'
Union All Select '010301'
Union All Select '01030102'
Union All Select '010302'
Union All Select '0104'
GO
Select * From table1 A Where Not Exists(Select 1 From table1 Where A Like A.A+'%' And A<>A.A)
GO
Drop Table table1
GO
--Result
/*
A
010201
01030102
010302
0104
*/
(A Varchar(20))
Insert table1 Select '01'
Union All Select '0102'
Union All Select '010201'
Union All Select '0103'
Union All Select '010301'
Union All Select '01030102'
Union All Select '010302'
Union All Select '0104'
Union All Select '02'
Union All Select '0201'
GO
--得到最底層的節點
Select * From table1 A Where Not Exists(Select 1 From table1 Where A Like A.A+'%' And A<>A.A)
--得到最上層的節點
Select * From table1 A Where Not Exists(Select 1 From table1 Where A.A Like A+'%' And A<>A.A)
GO
Drop Table table1
GO
--Result
/*
A
010201
01030102
010302
0104
0201A
01
02
*/