全部

sql union order by datetime

wwttqq85538649 2011-01-29 10:54:34

select * from
(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(getdate()) order by 3 ) a

此句无法按时间字段exportTime 进行排序，
如果是只有下面这么一段就可以排序
select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(getdate()) order by 3

谢谢高手帮忙解答。

...全文

197 点赞收藏 14

写回复

14 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

feixianxxx 2011-01-30

select * from
(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(getdate()) order by 3 ) a

===》

理由很简单，
select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(getdate()) order by 3
这部分是查询是一个结果集，作为一个表出现在from后面
sql server中的结果集是无序的它只是个集合。。也就说它即使查出来时排序好的，但是作为一个结果集的时候又被打乱了。
所以如果你要保证查询顺序要在最外层重新指定排序

select * from

(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(getdate()) order by 3 ) a

order by exportTime;

发现了么？这样的话 top 100 percent .....order by 毫无意义了。。
所以sql 语句少出现这样的语句

回复赞

Shawn 2011-01-30

主只要把union换成union all就好了.

;WITH temp1 AS

(

	SELECT id = 1, exportTime = CAST('2011-1-1' AS DATETIME) UNION ALL

	SELECT 2, '2011-1-3' UNION ALL

	SELECT 3, '2011-1-2'

),

temp2 AS

(

	SELECT id = 1, exportTime = CAST('2010-1-7' AS DATETIME) UNION ALL

	SELECT 2, '2010-1-6' UNION ALL

	SELECT 3, '2010-1-5'

)

SELECT * FROM

(SELECT TOP(2) * FROM temp1 ORDER BY 2 DESC) A

UNION ALL

SELECT * FROM

(SELECT TOP(2) * FROM temp2 ORDER BY 2 DESC) B

回复赞

wwttqq85538649 2011-01-29

[Quote=引用 5 楼 ssp2009 的回复:]
SQL code

select * from (
(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(getdate()))
union
select * from
(select……
[/Quote]

select * from
(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(getdate()) order by 3 ) a
union
select * from
(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 2 and year(exportTime) = year(getdate()) order by 3 ) b

我的意图是这样的，结果不能按exportTime字段进行排序
我要分开排序的，谢谢。

有人回复了我的帖子，竟然没发现。用《Csdn收音机》告别烦恼！

回复赞

兔子党党务院院长 2011-01-29



select * from

(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner

from counter where type = 1 and year(exportTime) = year(getdate())

order by 3  

union

select top 100 percent id, businessNum,exportTime,shipSide,shipOwner

from counter where type = 2 and year(exportTime) = year(getdate())

order by 3)U

回复赞

快溜 2011-01-29



select * from (

(select top 100 0 as ord,percent id, businessNum,exportTime,shipSide,shipOwner from counter 

where type = 1 and year(exportTime) = year(getdate()))   

union

select * from

(select top 100 1 as ord,percent id, businessNum,exportTime,shipSide,shipOwner from counter 

where type = 2 and year(exportTime) = year(getdate()))) b 

order by ord,exportTime

回复赞

wwttqq85538649 2011-01-29

[Quote=引用 8 楼 acherat 的回复:]

select * from
(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(getdate())) a
order by 3
union
select * from
(select……
[/Quote]
你好，这样我也试过了，会报错。

回复赞

兔子党党务院院长 2011-01-29

select * from
(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(getdate())) a
order by 3
union
select * from
(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 2 and year(exportTime) = year(getdate())) b
order by 3

回复赞

wwttqq85538649 2011-01-29

回复赞

叶子 2011-01-29

order by 是一定要放在最外层的。

回复赞

快溜 2011-01-29



select * from (

(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(getdate()))   

union

select * from

(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 2 and year(exportTime) = year(getdate()))) b order by 3

回复赞

wwttqq85538649 2011-01-29

[Quote=引用 2 楼 jinfengyiye 的回复:]
排序是一条sql语句的倒数第二步吧（最后一步是输出select from之间的）
这个应该是放在最后你可以
select * from
(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(get……
[/Quote]

噢不好意思，我的目的是要使用union的
select * from
(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(getdate()) order by 3 ) a
union
select * from
(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 2 and year(exportTime) = year(getdate()) order by 3 ) b

我的意图是这样的，结果不能按exportTime字段进行排序

回复赞

兔子党党务院院长 2011-01-29



select * from

(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(getdate()) order by 3 ) a

--这样的语句在子查询里对某个字段排序是没有必要的，SQL当然会提示你无法按...排序！

--你可以把3换成1、2、4等其他的试试。

回复赞

gw6328 2011-01-29

排序是一条sql语句的倒数第二步吧（最后一步是输出select from之间的）
这个应该是放在最后你可以
select * from
(select top 100 percent id, businessNum,exportTime,shipSide,shipOwner from counter where type = 1 and year(exportTime) = year(getdate()) ) a order by 3
这样子

回复赞

快溜 2011-01-29