实现:给一个青羊区要查出成都市sql语句如何写
数据
列1 列2 列3 列4
SC 四川 SW 1
SC01 成都 SC 2
SC0101 青羊区 SC01 3
SC0102 锦江区 SC01 3
SC0103 金牛区 SC01 3
数据
列1 列2 列3 列4
SC 四川 SW 1
SC01 成都 SC 2
SC0101 青羊区 SC01 3
SC0102 锦江区 SC01 3
SC0103 金牛区 SC01 3
WITH a1 (c1,c2,c3,c4) AS
(
SELECT 'SC','四川','SW',1 UNION ALL
SELECT 'SC01','成都','SC',2 UNION ALL
SELECT 'SC0101','青羊区','SC01',3 UNION ALL
SELECT 'SC0102','锦江区','SC01',3 UNION ALL
SELECT 'SC0103','金牛区','SC01',3
)
SELECT (SELECT c2 FROM a1 WHERE c1=a.c3) c2
FROM a1 a
WHERE c2='青羊区'
(
select 'SC','四川','SW',1
union all select 'SC01','成都','SC',2
union all select 'SC0101','青羊区','SC01',3
union all select 'SC0102','锦江区','SC01',3
union all select 'SC0103','金牛区','SC01',3
)
select b.col2
from tb a
inner join tb b on a.col3=b.col1
where a.col2='青羊区'
--结果
/*
成都
*/
from table
where 列1 in (select 列3 from table where 列2='青羊区')
AS
(
SELECT 'SC', N'四川', 'SW', 1 UNION ALL
SELECT 'SC01', N'成都', 'SC', 2 UNION ALL
SELECT 'SC0101', N'青羊区', 'SC01', 3 UNION ALL
SELECT 'SC0102', N'锦江区', 'SC01', 3 UNION ALL
SELECT 'SC0103', N'金牛区', 'SC01', 3
)
SELECT B.Name
FROM CTE AS A
JOIN CTE AS B ON A.ParentID=B.ID
WHERE A.Name LIKE N'%青羊区%' 你是成都的Code?
inner join t t2
on t1.[列3] = t2.[列1]
where t1.[列2] = '青羊区'
create table tkc(s1 varchar(10),s2 nvarchar(10),s3 varchar(10),s4 int)insert into tkc
select 'SC','四川','SW',1
union all
select 'SC01','成都','SC',2
union all
select 'SC0101','青羊区','SC01',3
union all
select 'SC0102','锦江区','SC01',3
union all
select 'SC0103','金牛区','SC01',3select s2 from tkc where s1=(select s3 from tkc where s2='青羊区' )