Question

Mssql问题(请高手帮忙)

我用的是mssql 想显示出内容
=============================================
DayNo("第一天,第二天....")[指天数],
City("北京,广州.....")[指城市]
No(1,2,3,4,5,6,7,8.....)[指不重复的序号],
ConfirmType1[就只有1,2 两个1表示出发城市,2表示到达城市]
=============================================
原Table1名称ItemSchedule[里面内容有(No,DayNo,SCityCode,ECityCode,
Name ,ConfirmType1,ConfirmType2)]
原Table2名称City[CityCode,Name]
=================================================
select distinct a.DayNo,a.CityCode from
(
select e.No as No,DayNo,SCityCode,f.Name as City,e.ConfirmType1
from
(ItemSchedule as e join City as f on e.SCityCode = f.CityCode ) join City as g on e.ECityCode = g.CityCode
where OrderCode = 'RJ2008-06-2301 ' And ItemNo = 1
Union all
select e.No as No,DayNo,ECityCode ,g.Name as EndCity,ConfirmType2
from
(ItemSchedule as e join City as f on e.SCityCode = f.CityCode ) join City as g on e.ECityCode = g.CityCode
where OrderCode = 'RJ2008-06-2301 ' And ItemNo = 1 Order by DayNo,No,e.ConfirmType1) a
===========================================================
我现在的的问题是 在 第一个from 后面生成的虚拟Table里面不能用Order by语句.
但又把 Order by 语句放到外边的话,得在最前面的(select distinct a.DayNo,a.CityCode from )语句里在 a.DayNo,a.No 才行, 这样显示的内容里有很多从复的 City 内容,但我知识希望每个DayNo里显示不重复的City 名称.
=============================================================
我想要显示的内容是把 Table1里面的出发城市(SCityCode)和到达城市(ECityCode) 显示成 一个城市列表 是按 天(DayNo),No,ConfirmType[按-出发城市->到达城市->出发城市......]显示的,排序的每天只有不重复的城市名称. 排序
说了一大帮话,希望能解决这个问题. 请大家帮忙...

Answer 1

select distinct a.DayNo,a.CityCode from
(
select e.No as No,DayNo,SCityCode,f.Name as City,e.ConfirmType1
from
(ItemSchedule as e inner join City as f on e.SCityCode = f.CityCode ) inner join City as g on e.ECityCode = g.CityCode
where OrderCode = 'RJ2008-06-2301 ' And ItemNo = 1
Union all
select e.No as No,DayNo,ECityCode ,g.Name as EndCity,ConfirmType2
from
(ItemSchedule as e inner join City as f on e.SCityCode = f.CityCode ) inner join City as g on e.ECityCode = g.CityCode
where OrderCode = 'RJ2008-06-2301 ' And ItemNo = 1 Order by DayNo,No,e.ConfirmType1) a
这样试试