为什么UNION结果不会出现在底部?
最初我正在处理两个查询, 其中一个回来了学生的分数, 秒回到了总分。为什么UNION结果不会出现在底部?
现在我决定联合这两个查询,但我的Grand Total行出现在顶部,我想在底部尝试更改两个排序,但对结果没有影响。请建议,我的查询是:
SELECT AISECTRESULT_Schemes.SUBNO,
AISECTRESULT_Schemes.SUBJECT,
AISECTRESULT_Schemes.CE_TotalMarks,
AISECTRESULT_ExamMarksData.TotalMarksObt_C,
AISECTRESULT_Schemes.TE_TotalMarks,
AISECTRESULT_ExamMarksData.TotalMarksObt,
MAXMARKS,
AISECTRESULT_ExamMarksData.OverAllMarks
FROM AISECTRESULT_Schemes
INNER JOIN AISECTRESULT_ExamMarksData
ON AISECTRESULT_Schemes.[EXAMSCHEME ID] = AISECTRESULT_ExamMarksData.EXAMSCHEMEID
WHERE (AISECTRESULT_ExamMarksData.REGISTRATIONID = '201192145')
UNION
SELECT ''AS SUBNO,
''AS SUBJECT,
SUM(AISECTRESULT_Schemes.CE_TotalMarks)CE_TotalMarks,
SUM(AISECTRESULT_ExamMarksData.TotalMarksObt_C)TotalMarksObt_C,
SUM(AISECTRESULT_Schemes.TE_TotalMarks)TE_TotalMarks,
SUM(AISECTRESULT_ExamMarksData.TotalMarksObt)TotalMarksObt,
SUM(AISECTRESULT_Schemes.MAXMARKS)MAXMARKS,
SUM(AISECTRESULT_ExamMarksData.OverAllMarks)OverAllMarks
FROM AISECTRESULT_Schemes
INNER JOIN AISECTRESULT_ExamMarksData
ON AISECTRESULT_Schemes.[EXAMSCHEME ID] = AISECTRESULT_ExamMarksData.EXAMSCHEMEID
WHERE (AISECTRESULT_ExamMarksData.REGISTRATIONID = '201192145')
您对您的查询需要一个ORDER BY
。这应该这样做:
SELECT SUBNO,
SUBJECT,
CE_TotalMarks,
TotalMarksObt_C,
TE_TotalMarks,
TotalMarksObt,
MAXMARKS,
OverAllMark
FROM ( SELECT AISECTRESULT_Schemes.SUBNO,
AISECTRESULT_Schemes.SUBJECT,
AISECTRESULT_Schemes.CE_TotalMarks,
AISECTRESULT_ExamMarksData.TotalMarksObt_C,
AISECTRESULT_Schemes.TE_TotalMarks,
AISECTRESULT_ExamMarksData.TotalMarksObt,
MAXMARKS,
AISECTRESULT_ExamMarksData.OverAllMarks,
1 ColOrder
FROM AISECTRESULT_Schemes
INNER JOIN AISECTRESULT_ExamMarksData
ON AISECTRESULT_Schemes.[EXAMSCHEME ID] = AISECTRESULT_ExamMarksData.EXAMSCHEMEID
WHERE AISECTRESULT_ExamMarksData.REGISTRATIONID = '201192145'
UNION
SELECT ''AS SUBNO,''AS SUBJECT,
SUM(AISECTRESULT_Schemes.CE_TotalMarks)CE_TotalMarks,
SUM(AISECTRESULT_ExamMarksData.TotalMarksObt_C)TotalMarksObt_C,
SUM(AISECTRESULT_Schemes.TE_TotalMarks)TE_TotalMarks,
SUM(AISECTRESULT_ExamMarksData.TotalMarksObt)TotalMarksObt,
SUM(AISECTRESULT_Schemes.MAXMARKS)MAXMARKS,
SUM(AISECTRESULT_Exa![enter image description here][1]mMarksData.OverAllMarks)OverAllMarks,
2
FROM AISECTRESULT_Schemes
INNER JOIN AISECTRESULT_ExamMarksData
ON AISECTRESULT_Schemes.[EXAMSCHEME ID] = AISECTRESULT_ExamMarksData.EXAMSCHEMEID
WHERE AISECTRESULT_ExamMarksData.REGISTRATIONID = '201192145') AS Data
ORDER BY ColOrder
您的查询解决了我的问题,让我检查你做了什么。 – 2012-07-31 14:25:05
http://*.com/用户/ 519216/lamak,你能否请你解释一下你的代码... – 2012-07-31 14:30:38
@AjitD - 就像@Lieven对他的回答所说的那样。我把你的查询作为派生表使用,但是首先我添加了一个排序顺序的列。然后我在查询结尾添加了一个ORDER BY – Lamak 2012-07-31 14:39:51
查询结果的排序是没有一个明确的ORDER BY
随心所欲。
现在您可能会注意到订单总是似乎是相同的但休息保证:添加数据或更改聚集索引或者不会使您认为理所当然的订单失效。
通常情况下,你会被
- 添加一个虚拟列解决这个问题进行排序(如果没有合理的现有列挑),该列
- 明确排序
示例
SELECT 1 AS ToSortOn, ... FROM ...
UNION ALL SELECT 2 AS ToSortOn, ... FROM ...
ORDER BY ToSortOn
我认为你是绝对正确的,我将subno设置为100,并通过SUBNO完成订单,它的工作... – 2012-07-31 14:33:57
虽然不是最好的方法......
UNION ALL
对于什么数据库?使查询可读的一点格式可能会有所帮助。 – 2012-07-31 14:05:22
顶部组件在'SELECT'中有8个字段,而底部有7个字段。 – 2012-07-31 14:08:04
@Michael - 我在 – Lamak 2012-07-31 14:09:38