求这条SQL语句如何写
目前有表 train
结构如下：
车次 所在站 站序
D3001 上海 1
D3001 苏州 2
D3001 镇江 5
D3001 合肥 9
Z90 上海 1
Z90 苏州 2
G7900 上海 1
G7900 北京 2
D1001 上海 1
D1001 合肥 2
要求用一条SQL语句查出 上海-合肥的 车次，即为
D3001和D1001 请问如何写SQL语句
[最优解释]
--if object_id('train') is not null
--drop table train
--Go
--Create table train([车次] nvarchar(5),[所在站] nvarchar(2),[站序] smallint)
--Insert into train
--Select N'D3001',N'上海',1
--Union all Select N'D3001',N'苏州',2
--Union all Select N'D3001',N'镇江',5
--Union all Select N'D3001',N'合肥',9
--Union all Select N'Z90',N'上海',1
--Union all Select N'Z90',N'苏州',2
--Union all Select N'G7900',N'上海',1
--Union all Select N'G7900',N'北京',2
--Union all Select N'D1001',N'上海',1
--Union all Select N'D1001',N'合肥',2
DECLARE @Station_Start NVARCHAR(50),@Station_End NVARCHAR(50)
SELECT
@Station_Start=N'上海'
,@Station_End=N'合肥'
--1.
SELECT a.车次 FROM train AS a
WHERE a.所在站=@Station_Start
AND EXISTS(SELECT 1 FROM train AS x
WHERE x.所在站=@Station_End
AND a.车次=x.车次
)
/*
车次
-----
D3001
D1001
*/
--2.
SELECT c.* FROM train AS a
INNER JOIN train AS b ON a.车次=b.车次
INNER JOIN train AS c ON a.车次=c.车次 AND c.站序 BETWEEN a.站序 AND b.站序
WHERE a.所在站=@Station_Start
AND b.所在站=@Station_End
ORDER BY a.车次 ASC,a.站序 ASC
/*
车次所在站站序
---------------------
D1001上海1
D1001合肥2
D3001上海1
D3001苏州2
D3001镇江5
D3001合肥9
*/
--你写的对的，求得出来的，毕竟和站序没关系
select 车次 from train
where 所在站 in ('上海','合肥')
group by 车次
having count(1)=2;
CREATE TABLE #train (CODE NVARCHAR(50),NAMES NVARCHAR(50),IDS INT)
INSERT INTO #train
SELECT 'D3001',N'上海',1 UNION ALL
SELECT 'D3001',N'苏州',2 UNION ALL
SELECT 'D3001',N'镇江',5 UNION ALL
SELECT 'D3001',N'合肥',9 UNION ALL
SELECT 'Z90',N'上海',1 UNION ALL
SELECT 'Z90',N'苏州',2 UNION ALL
SELECT 'G7900',N'上海',1 UNION ALL
SELECT 'G7900',N'北京',2 UNION ALL
SELECT 'D1001',N'上海',1 UNION ALL
SELECT 'D1001',N'合肥',2
;
WITH LIST AS(
SELECT RID=ROW_NUMBER()OVER(PARTITION BY CODE ORDER BY IDS)
,*
FROM #TRAIN
),CTE (CODE,NAMES,IDS,RID,PATHS,ENDS)AS(
SELECT CODE,NAMES,IDS,RID,PATHS=CAST(NAMES AS NVARCHAR(500)),ENDS=CAST(NAMES AS NVARCHAR(500))
FROM LIST WHERE RID=1
UNION ALL
SELECT LT.CODE,CE.NAMES,LT.IDS,LT.RID,PATHS=CAST(CE.PATHS+'->'+LT.NAMES AS NVARCHAR(500))
,ENDS=CAST(LT.NAMES AS NVARCHAR(500))
FROM LIST LT
INNER JOIN CTE CE ON LT.CODE=CE.CODE AND LT.RID=CE.RID+1
)
SELECT *
FROM CTE
WHERE NAMES=N'上海' AND ENDS=N'合肥'
----------------------------
-- Author :TravyLee(努力工作中！！！)
-- Date :2012-07-31 09:10:42
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
--Apr 2 2010 15:48:46
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go
create table [test](
[车次] varchar(5),
[所在站] varchar(4),
[站序] int
)
go
insert [test]
select 'D3001','上海',1 union all
select 'D3001','苏州',2 union all
select 'D3001','镇江',5 union all
select 'D3001','合肥',9 union all
select 'Z90','上海',1 union all
select 'Z90','苏州',2 union all
select 'G7900','上海',1 union all
select 'G7900','北京',2 union all
select 'D1001','上海',1 union all
select 'D1001','合肥',2
go
--------------开始查询--------------------------
;with t
as(
select
distinct [车次],
[所在站]=(select ','+[所在站] from test b where a.车次=b.车次 for xml path(''))
from
test a
)
select
[车次],
[所在站]=right([所在站],LEN([所在站])-1)
from
t
where
charindex('上海',[所在站])>0
and charindex('合肥',[所在站])>0
and charindex('上海',[所在站])<charindex('合肥',[所在站])
----------------结果----------------------------
/*
车次所在站
D1001上海,合肥
D3001上海,苏州,镇江,合肥
*/
select a.code from train a,
(select * from train where NAMES = '合肥') b
where a.code = B.code
and a.NAMES = '上海'
and a.IDS < B.IDS
select
t1.no
from
train t1
where t1.stop='上海'
and t1.noStep<(select t2.noStep from train t2 where t1.no=t2.no and t2.stop='合肥')
WITH train(s,m,x)
AS
(
SELECT 'D3001','上海',1 UNION ALL
SELECT 'D3001','苏州',2 UNION ALL
SELECT 'D3001','镇江',5 UNION ALL
SELECT 'D3001','合肥',9 UNION ALL
SELECT 'Z90','上海',1 UNION ALL
SELECT 'Z90','苏州',2 UNION ALL
SELECT 'G7900','上海',1 UNION ALL
SELECT 'G7900','北京',2 UNION ALL
SELECT 'D1001','上海',1 UNION ALL
SELECT 'D1001','合肥',2
)
select distinct a.s from train a,train b where a.s = b.s and a.m = '上海' and b.m = '合肥'
