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 谢谢高手帮忙解答。
(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 谢谢高手帮忙解答。
这个应该是放在最后你可以
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
这样子
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等其他的试试。
噢不好意思,我的目的是要使用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字段进行排序
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
你好,我是打算在这两个select 语句中分别排序。不是对整个查询结果进行排序,谢谢。
(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
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
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
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收音机》告别烦恼!
;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
(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 语句少出现这样的语句