我目前设置了一个表,用于报告历史数据。我有过
SELECT ACTIONTYPE,
BINNUM,
DSID,
LOCATIONNAME,
LOCATIONTYPE,
ORDNO,
ORIGREC,
convert(varchar(10),TIMEOFACTION, 101) +
right(convert(varchar(32),TIMEOFACTION,100),8) as TIMEOFACTION,
TOTALLIFE
FROM DLOCATIONHISTORY
ORDER BY TIMEOFACTION DESC
我已经编辑了TIMEOFACTION列,以便它将日期格式的字段显示为mm/dd/yyyy hh:mmAM/PM
。但是,引用我的查询的程序将AM放在PM之前,因为A
在P
之前。解决我的查询以防止这种情况发生的最佳方法是什么?有没有不同的排序技术我可以使用?
我当前的报表查询显示:
**TIMEOFACTION**
12/13/2017 7:29AM
12/12/2017 10:07AM
12/12/2017 9:58AM
12/12/2017 1:51PM
12/12/2017 2:02PM
12/11/2017 11:01AM
当它应该显示时:
**TIMEOFACTION**
12/13/2017 7:29AM
12/12/2017 2:02PM
12/12/2017 1:51PM
12/12/2017 10:07AM
12/12/2017 9:58AM
12/11/2017 11:01AM
发布于 2017-12-13 22:14:58
LOL,仅包含表名或表别名的完整引用列:
SELECT ACTIONTYPE,
BINNUM,
DSID,
LOCATIONNAME,
LOCATIONTYPE,
ORDNO,
ORIGREC,
convert(varchar(10),TIMEOFACTION, 101) +
right(convert(varchar(32),TIMEOFACTION,100),8) as TIMEOFACTION,
TOTALLIFE
FROM DLOCATIONHISTORY
ORDER BY DLOCATIONHISTORY.TIMEOFACTION DESC --<-- here!
Simplified sample
MS SQL Server 2014架构安装程序
create table t ( i int, a char(1) );
insert into t values
(1,'a'),
(2,'b'),
(3,'c');
查询1
select -1*i as i, a
from t
order by t.i
| i | a |
|----|---|
| -1 | a |
| -2 | b |
| -3 | c |
查询2
select -1*i as i, a
from t
order by i
| i | a |
|----|---|
| -3 | c |
| -2 | b |
| -1 | a |
发布于 2017-12-13 22:18:19
可以使用具有列别名的派生表,然后重命名回原始列名。例如:
DECLARE @table TABLE(TIMEOFACTION datetime)
INSERT INTO @table VALUES
('2017-12-13 07:29:00')
,('2017-12-12 10:07:00')
,('2017-12-12 09:58:00')
,('2017-12-12 13:51:00')
,('2017-12-12 14:02:00')
,('2017-12-11 11:01:00')
SELECT convert(varchar(10),dT.TIMEOFACTION2, 101)
+ right(convert(varchar(32),TIMEOFACTION2,100),8) as TIMEOFACTION
FROM (
SELECT TIMEOFACTION AS TIMEOFACTION2
FROM @table
) AS dT
ORDER BY TIMEOFACTION2 DESC
产生:
TIMEOFACTION
12/13/2017 7:29AM
12/12/2017 2:02PM
12/12/2017 1:51PM
12/12/2017 10:07AM
12/12/2017 9:58AM
12/11/2017 11:01AM
https://stackoverflow.com/questions/47794651
复制相似问题