任何人都可以解释结果不同的原因吗？ [英] Can any one explain the reason why results are different?
问题描述
我有一张数据库表，其记录如下：
I m having a DB table, having records as below:
id letterdate
1 2015-02-23 18:43:00.000
2 2015-02-27 10:36:00.000
3 2015-02-23 18:43:00.000
4 2015-02-23 18:43:00.000
5 2015-02-27 10:36:00.000
6 2015-02-27 10:36:00.000
7 2015-02-27 10:36:00.000
8 2015-02-27 10:36:00.000
9 2015-02-27 10:36:00.000
10 2015-02-23 18:43:00.000
11 2015-02-27 10:36:00.000
12 2015-02-27 10:36:00.000
13 2015-02-23 18:43:00.000
14 2015-02-27 10:36:00.000
15 2015-02-27 10:36:00.000
16 2015-02-23 18:43:00.000
17 2015-02-27 10:36:00.000
18 2015-02-27 10:36:00.000
19 2015-02-27 10:36:00.000
20 2015-02-27 10:36:00.000
22 2015-02-23 18:43:00.000
23 2015-02-27 10:36:00.000
24 2015-02-27 10:36:00.000
25 2015-02-27 10:36:08.000
26 2015-02-23 19:26:33.000
27 2015-03-01 16:42:10.000
28 2015-03-01 16:42:10.000
29 2015-03-01 16:42:10.000
30 2015-03-01 16:42:10.000
31 2015-03-01 16:42:10.000
32 2015-03-01 16:42:10.000
33 2015-03-01 16:42:10.000
34 2015-03-01 16:42:10.000
35 2015-03-01 16:42:10.000
36 2015-03-01 16:42:10.000
37 2015-03-01 16:42:10.000
38 2015-03-01 16:42:10.000
39 2015-03-01 16:42:10.000
40 2015-03-01 16:42:10.000
41 2015-03-01 16:42:10.000
42 2015-03-01 16:42:10.000
43 2015-03-01 16:42:10.000
44 2015-03-01 16:42:10.000
45 2015-03-01 16:42:10.000
46 2015-03-01 16:42:10.000
47 2015-03-01 16:42:10.000
48 2015-03-01 16:42:10.000
49 2015-03-01 16:42:10.000
50 2015-03-01 16:42:10.000
51 2015-03-01 16:42:10.000
52 2015-03-01 16:42:10.000
53 2015-03-02 14:34:31.000
54 2015-03-02 14:34:31.000
55 2015-03-02 14:34:31.000
56 2015-03-02 14:34:31.000
57 2015-03-02 14:34:31.000
58 2015-03-02 13:25:59.000
59 2015-03-02 13:26:00.000
60 2015-03-02 13:26:01.000
61 2015-03-11 15:07:20.000
62 2015-03-11 13:51:15.000
63 2015-03-11 13:39:05.000
64 2015-03-11 13:39:05.000
65 2015-03-12 17:31:21.000
以下查询给出了不同的结果。 />
声明@RefCode nvarchar（50）='ALP002';
查询1：
below queries gives me different result.
declare @RefCode nvarchar(50)='ALP002';
Query 1:
with dates AS
(
SELECT DISTINCT cast([LetterDate] AS date) AS
LetterDate
FROM t_LettersForPrint where isactive=1 and RefCode=@RefCode
)
SELECT
convert(char(12), LetterDate, 103) AS LetterDate
FROM dates order by LetterDate desc;
结果：
LetterDate
27/02/2015
23/02/2015
11/03/2015
02/03/2015
01/03/2015
查询2：
Result:
LetterDate
27/02/2015
23/02/2015
11/03/2015
02/03/2015
01/03/2015
Query 2:
with dates AS
(
SELECT DISTINCT cast([LetterDate] AS date) AS
LetterDate
FROM t_LettersForPrint where isactive=1 and RefCode=@RefCode
)
SELECT
convert(char(12), LetterDate, 103) AS LD
FROM dates order by LetterDate desc
结果：
LD
11/03/2015
02/03/2015
01/03/2015
27/02 / 2015
23/02/2015
两个查询中只有一个区别，即列的别名。
任何人都可以解释结果不同的原因吗？
注意：第二个满了我的要求。
推荐答案
在第一个选项中，日期转换的别名是LetterDate，您按该字段排序，但在第二个查询中，别名是LD，您仍然通过LetterDate排序！
In the first option the alias of the date-casting is LetterDate and you order by that field, but in the second query the alias is LD and you still ordering by LetterDate!
第一个查询是
First query is
SELECT
convert(char(12), LetterDate, 103) AS LetterDate
FROM dates order by LetterDate desc
在上面的查询中，您将LatterDate转换为char并将别名设置为LetterDate。现在你的LatterDate变成了char列，这就是为什么当你按顺序执行时，它没有通过LatterDate给出结果顺序（预期日期明智），而是由LatterDate给出结果顺序（因为它现在变成了char）。 />
我希望我回答你的问题。
In the above query your are converting LatterDate to char and setting alias as LetterDate. Now your LatterDate became char column and that is why when you are doing order by, it is not giving you result order by LatterDate(expected date wise), instead it is giving result order by LatterDate(as it now became char).
I hope I answered your query.
你首先查询按char（12）排序结果，但第二个按日期排序因为第一个查询采用别名进行排序而不是来自CTE
You first query sorting the results by char(12) but second one is sorting by date because the first query taking the alias for sorting rather than from CTE
这篇关于任何人都可以解释结果不同的原因吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
