求SQL正解

Question

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 
GOCREATE 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 行受影响)
--循环不多的话左连接就成，如果多的话得用递归，但传的参数得特别多。

Answer 1

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'

Answer 2

弄个正解
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

Answer 3

谢谢各位支持，本人发现SQL2005有新关键字可用,与各位分享Declare @from as varchar(1);
Declare @to as varchar(1);
select @from='A',@to='E';with TT( Cartype, [From],[To],Paths, Price)
as
(
select Cartype
, [From]
,[To]
, Paths
, Price
from travel 
where [From]=@from
UNION all
SELECT  TT.Cartype
, T.[From]
, T.[To]
, TT.Paths+'-' +T.[To]   Paths
, TT.Price +T.Price  Price
from travel T
INNER JOIN TT
on TT.[To]=T.[From]
and TT.CarType=T.carType
)select CarType, Paths, Price
from TT
where left(Paths,1)=@from and right(paths,1)=@to
order by CarType,Paths