如果指定了 SELECT DISTINCT，那么 ORDER BY 子句中的项就必须出现在选择列表中

[微醺_zZ]

declare @date datetime,@kgcode uniqueidentifier
declare @print varchar(max)
select   @print='今日食谱：'
set @date = '2009-05-12'
set @kgcode = 'b997cb81-e5fe-40ef-98dc-61173bc86b5c'


if object_id('tempdb..#foodnames') is not null
drop table #foodnames;with [foodname] 
as
(select TOP 100 b.typename,a.cookname,a.fooddate,a.displayorder
from kd_FoodWgMenu a join dic_FoodType b on  a.guidfoodtype=b.guidfoodtype
where a.guidfood='00000000-0000-0000-0000-000000000000' and a.fooddate=@date and a.kindercode=@kgcode order by charindex(TypeName,'早餐,午餐,晚餐,早点,间点,晚点'),DisplayOrder) 
select distinct fooddate,
foodnames=typename+':'+stuff((select ','+cookname  from [foodname] b where a.fooddate=b.fooddate and a.typename=b.typename  order by DisplayOrder for xml path('')),1,1,'') into #foodnames from [foodname] a order by charindex(TYPENAME,'早餐,午餐,晚餐,早点,间点,晚点'),DisplayOrder;select @print=@print+foodnames from #foodnames;

select @print

帮我看看这个该怎么改...执行时报错如题，我不知道这个ORDER BY 子句中的项就必须出现在选择列表中是什么意思，谢谢各位高手了

[claro]

declare @date datetime,@kgcode uniqueidentifier
declare @print varchar(max)
select   @print='今日食谱：'
set @date = '2009-05-12'
set @kgcode = 'b997cb81-e5fe-40ef-98dc-61173bc86b5c'


if object_id('tempdb..#foodnames') is not null
drop table #foodnames

;with [foodname] 
as
(select TOP 100 b.typename,a.cookname,a.fooddate,a.displayorder
from kd_FoodWgMenu a
	 join dic_FoodType b on  a.guidfoodtype=b.guidfoodtype
where a.guidfood='00000000-0000-0000-0000-000000000000' and
 a.fooddate=@date and
 a.kindercode=@kgcode
 order by charindex(TypeName,'早餐,午餐,晚餐,早点,间点,晚点'),DisplayOrder) 

select distinct fooddate
,foodnames=typename+':'+stuff((select ','+cookname
								  from [foodname] b
									where a.fooddate=b.fooddate and a.typename=b.typename
									order by DisplayOrder for xml path('')),1,1,'')
 into #foodnames
 from [foodname] a
-- order by charindex(TYPENAME,'早餐,午餐,晚餐,早点,间点,晚点'),DisplayOrder
 --此结果集已经在with [foodname] 中进行了排序，所以这里排序是无效且错误的。
--可以用order by 1 或 order by 2 进行排序。

select @print=@print+foodnames
 from #foodnames

select @print

[claro]

--try ?
declare @date datetime,@kgcode uniqueidentifier
declare @print varchar(max)
select   @print='今日食谱：'
set @date = '2009-05-12'
set @kgcode = 'b997cb81-e5fe-40ef-98dc-61173bc86b5c'


if object_id('tempdb..#foodnames') is not null
drop table #foodnames

;with [foodname] 
as
(select TOP 100 b.typename,a.cookname,a.fooddate,a.displayorder
from kd_FoodWgMenu a
	 join dic_FoodType b on  a.guidfoodtype=b.guidfoodtype
where a.guidfood='00000000-0000-0000-0000-000000000000' and
 a.fooddate=@date and
 a.kindercode=@kgcode
 order by charindex(TypeName,'早餐,午餐,晚餐,早点,间点,晚点'),DisplayOrder) 

select distinct fooddate
,foodnames=typename+':'+stuff((select ','+cookname
								  from [foodname] b
									where a.fooddate=b.fooddate and a.typename=b.typename for xml path('')),1,1,''
									order by DisplayOrder)
 into #foodnames
 from [foodname] a
--建议用order by 1 或 order by 2 进行排序。

select @print=@print+foodnames
 from #foodnames

select @print

[微醺_zZ]

终于有个正八经回答问题的了，谢谢10楼的这位大哥
我试了你10楼的那个方法，语法错误，order by这句必须放在for xml path('')),1,1,''之前
order by 1 或者 order by 2是什么意思，我没明白，能不能写个例子教教小弟，小弟追分学习一下，谢谢了

我用一个笨办法解决的，如下：

if object_id('tempdb..#foodnames') is not null
drop table #foodnames;with [foodname] 
as
(select TOP 100 b.typename,a.cookname,a.fooddate,a.displayorder
from kd_FoodWgMenu a join dic_FoodType b on  a.guidfoodtype=b.guidfoodtype
where a.guidfood='00000000-0000-0000-0000-000000000000' and a.fooddate=@date and a.kindercode=@kgcode order by charindex(TypeName,'早餐,午餐,晚餐,早点,间点,晚点'),DisplayOrder) 
select distinct fooddate,TYPENAME,
foodnames=typename+':'+stuff((select ','+cookname  from [foodname] b where a.fooddate=b.fooddate and a.typename=b.typename  order by DisplayOrder for xml path('')),1,1,'') into #foodnames from [foodname] a ;
--既然distinct 语句不允许我写order by 那么我这句就不写，重新定义一个表重新进行排序，哈哈，耍了点小聪明
if object_id('tempdb..#foodData') is not null
drop table #foodData;
select foodnames,typename  into #foodData from #foodnames order by charindex(TYPENAME,'早餐,早点,午餐,间点,晚餐,晚点');
select @print=@print+' '+foodnames from #foodData

[微醺_zZ]

order by 1 这种排序满足不了我的要求，虽然不出错了，但结果还是不是我想要的，总之还要谢谢你

[让你望见影子的墙]

select distinct id from tb order by id
也就是order by后面的id必须出现在select 列表当中

[htl258_Tony]

[Quote=引用 1 楼 HEROWANG 的回复:]
select distinct id from tb order by id
也就是order by后面的id必须出现在select 列表当中
[/Quote]
没错，ORDER BY 后面的各项要在 SELECT 列表中有出现。

[微醺_zZ]

select distinct fooddate,TYPENAME,DisplayOrder,
foodnames=typename+':'+stuff((select ','+cookname  from [foodname] b where a.fooddate=b.fooddate and a.typename=b.typename  order by DisplayOrder for xml path('')),1,1,'') into #foodnames from [foodname] a order by charindex(TYPENAME,'早餐,午餐,晚餐,早点,间点,晚点'),DisplayOrder;

我把TYPENAME,DisplayOrder加上了还是提示这个错误...该怎么改呢？

[--小F--]

怎么有2个order by??

[微醺_zZ]

跟子查询里的order by 无关，我把子查询里的order by 删除了还是一样
各位大哥大姐帮帮小弟吧...搞不懂...

[htl258_Tony]

[Quote=引用 5 楼 liyb5619 的回复:]
跟子查询里的order by 无关，我把子查询里的order by 删除了还是一样
各位大哥大姐帮帮小弟吧...搞不懂...
[/Quote]两个ORDER BY 都去了吗

[微醺_zZ]

select distinct fooddate,TYPENAME,DisplayOrder,
foodnames=typename+':'+stuff((select ','+cookname  from [foodname] b where a.fooddate=b.fooddate and a.typename=b.typename  for xml path('')),1,1,'') into #foodnames from [foodname] a order by charindex(TYPENAME,'早餐,午餐,晚餐,早点,间点,晚点'),DisplayOrder;

只能把子查询里面的order by 给去掉，
外面的order by charindex(TYPENAME,'早餐,午餐,晚餐,早点,间点,晚点'),DisplayOrder是要按照这个进行排序，我改这段SQL的目的就是重新排序...
如果都去掉就不会问这个问题了...呵呵

[微醺_zZ]

自己解决了...竟然没有一个能大概给出点思路的...失望
暂不结贴，没法给分，等着高手来接分