求SQL正解
有一个表Travel
车型 出发 目的 路径 价钱
Car1 A E A-E 200
Car1 A D A-D 180
Car1 A B A-B 70
Car1 B C B-C 60
Car1 C E C-E 90
Car1 C D C-D 50
Car1 B E B-E 120
Car1 A C A-C 80
Car2 A E A-E 260
Car2 A B A-B 60
Car2 B E B-E 180
现在要写出SQL查询，可以输入两个参数，@from, @to,开始和目的地，然后取出，从开始地点到目的地的所有路径以及价钱。
同时，不同车型的车不能换乘。
从A到E的结果为：
车型 路径 车费
Car1 A-B-C-E 220
Car1 A-B-E 190
Car1 A-C-E 170
Car1 A-E 200
Car2 A-B-E 240
Car2 A-E 260
[解决办法]
create table a
(
车型 varchar(20),
出发 varchar(5),
目的 varchar(5),
路径 varchar(20),
价钱 int
)
insert into a
select 'Car1 ', 'A ', 'E ', 'A-E ',200 union all
select 'Car1 ', 'A ', 'D ', 'A-D ',180 union all
select 'Car1 ', 'A ', 'B ', 'A-B ',70 union all
select 'Car1 ', 'B ', 'C ', 'B-C ',60 union all
select 'Car1 ', 'C ', 'E ', 'C-E ',90 union all
select 'Car1 ', 'C ', 'D ', 'C-D ',50 union all
select 'Car1 ', 'B ', 'E ', 'B-E ',120 union all
select 'Car1 ', 'A ', 'C ', 'A-C ',80 union all
select 'Car2 ', 'A ', 'E ', 'A-E ',260 union all
select 'Car2 ', 'A ', 'B ', 'A-B ',60 union all
select 'Car2 ', 'B ', 'E ', 'B-E ',180
GO
CREATE PROCEDURE PROTEST
@START VARCHAR(5),
@END VARCHAR(5)
SELECT * FROM
(
SELECT
AA.车型,
路线=AA.出发+ '- '+AA.目的+CASE WHEN ISNULL(BB.目的, ' ')= ' ' THEN ' ' ELSE '- '+BB.目的+CASE WHEN ISNULL(CC.目的, ' ')= ' ' THEN ' ' ELSE '- '+CC.目的 END END ,
车费=AA.价钱+ISNULL(BB.价钱,0)+ISNULL(CC.价钱,0)
FROM A AA left JOIN A BB ON AA.车型=BB.车型 AND AA.目的= BB.出发 left join A CC ON BB.车型=CC.车型 AND BB.目的= CC.出发
) DD
WHERE LEFT(DD.路线,1)=@START AND RIGHT(DD.路线,1)=@END
--结果
车型 路线 车费
-------------------- ----------------------- -----------
Car1 A-E 200
Car1 A-B-C-E 220
Car1 A-B-E 190
Car1 A-C-E 170
Car2 A-E 260
Car2 A-B-E 240
(6 行受影响)
--循环不多的话左连接就成，如果多的话得用递归，但传的参数得特别多。
[解决办法]
CREATE TABLE [dbo].[a] (
[a0] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[a1] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[a2] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[a3] [int] NULL
) ON [PRIMARY]
INSERT INTO a
SELECT 'Car1 ', 'A ', 'E ', '200 ' UNION ALL
SELECT 'Car1 ', 'A ', 'D ', '180 ' UNION ALL
SELECT 'Car1 ', 'A ', 'B ', '70 ' UNION ALL
SELECT 'Car1 ', 'B ', 'C ', '60 ' UNION ALL
SELECT 'Car1 ', 'C ', 'E ', '90 ' UNION ALL
SELECT 'Car1 ', 'C ', 'D ', '50 ' UNION ALL
SELECT 'Car1 ', 'B ', 'E ', '120 ' UNION ALL
SELECT 'Car1 ', 'A ', 'C ', '80 ' UNION ALL
SELECT 'Car2 ', 'A ', 'E ', '260 ' UNION ALL
SELECT 'Car2 ', 'A ', 'B ', '60 ' UNION ALL
SELECT 'Car2 ', 'B ', 'E ', '180 '
GO
CREATE PROCEDURE Proca
@START VARCHAR(5),
@END VARCHAR(5)
AS
BEGIN
DECLARE @SQL VARCHAR(8000)
DECLARE @CNT INT
SELECT @CNT=COUNT(DISTINCT A1) FROM a
SELECT @SQL= 'SELECT a0,a1, a2, a1 + ' '- ' '+ a2 AS 结果, a3 FROM a WHERE (a1 = ' ' '+@START+ ' ' ') '
WHILE @CNT > 0
BEGIN
SELECT @SQL = 'SELECT A.a0,A.a1, CASE WHEN a_1.a2 IS NULL THEN A.a2 ELSE a_1.A2 END AS A2,
CASE WHEN a_1.a2 IS NULL
THEN a.结果 ELSE a.结果 + ' '- ' ' + a_1.A2 END AS 结果, CASE WHEN a_1.a2 IS NULL
THEN a.a3 ELSE a.a3 + a_1.A3 END AS a3
FROM ( '+@SQL+ ') A LEFT OUTER JOIN
a a_1 ON A.a2 = a_1.a1 and A.a0 = a_1.a0 '
SELECT @CNT=@CNT-1
END
SELECT @SQL= 'SELECT * FROM ( '+@SQL+ ') A WHERE A.A2= ' ' '+@END+ ' ' ' '
exec (@SQL)
END
GO
EXEC Proca 'A ', 'E '
[解决办法]
估计你的数据量应该不小，考虑递归吧
[解决办法]
弄个正解
create table Travel
(
车型 varchar(20),
出发 varchar(5),
目的 varchar(5),
路径 varchar(20),
价钱 int
)
insert into Travel
select 'Car1 ', 'A ', 'E ', 'A-E ',200 union all
select 'Car1 ', 'A ', 'D ', 'A-D ',180 union all
select 'Car1 ', 'A ', 'B ', 'A-B ',70 union all
select 'Car1 ', 'B ', 'C ', 'B-C ',60 union all
select 'Car1 ', 'C ', 'E ', 'C-E ',90 union all
select 'Car1 ', 'C ', 'D ', 'C-D ',50 union all
select 'Car1 ', 'B ', 'E ', 'B-E ',120 union all
select 'Car1 ', 'A ', 'C ', 'A-C ',80 union all
select 'Car2 ', 'A ', 'E ', 'A-E ',260 union all
select 'Car2 ', 'A ', 'B ', 'A-B ',60 union all
select 'Car2 ', 'B ', 'E ', 'B-E ',180
GO
--建立函数，第一个参数是出发地点。第二个是路程，第三个总价，第四个是车型
CREATE FUNCTION dbo.SEEKSUB(@start varchar(5),@pro varchar(50)= ' ',@price decimal =0,@type varchar(20)=null)
RETURNS @T TABLE(typ varchar(20),start varchar(5),paths varchar(50),price decimal)
AS
begin
DECLARE @typ varchar(20),@END varchar(5),@path varchar(50),@pri decimal
DECLARE CUR CURSOR FOR
SELECT 车型,目的,@pro+ '- '+目的,@price+价钱 FROM Travel WHERE 出发=@start and 车型=isnull(@type,车型)
OPEN CUR
FETCH CUR INTO @typ,@END,@path,@pri
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @T SELECT @typ,@END,@path,@pri
IF @@NESTLEVEL <32
BEGIN
INSERT INTO @T SELECT * FROM dbo.SEEKSUB(@END,@path,@pri,@typ)
END
FETCH CUR INTO @typ,@END,@path,@pri
END
RETURN
END
GO
--使用
declare @st varchar(5),@end varchar(5)
set @st= 'A '
set @end = 'E '
select * from dbo.SEEKSUB(@st,@st,default,default) b where right(b.paths,1)=@end order by b.typ
--结果
typ start paths price
-------------------- ----- -------------------------------------------------- -------
Car1 E A-E 200
Car1 E A-B-C-E 220
Car1 E A-B-E 190
Car1 E A-C-E 170
Car2 E A-E 260
Car2 E A-B-E 240
