旧的 SQL 查询
SELECT
ISNULL(SUM(ColValue1), 0.00) AS Rejection
FROM
Tabel1 a, Table2 b, Table3 c
WHERE
b.col1 =* a.col2
AND c.col1 = a.col3
AND b.colx = 'xxxxxxx'
AND YEAR(TDate) = 2017
AND MONTH(TDate) = 11
GROUP BY
c.columnz
ORDER BY
c.columnZ
这将基于 c.columnz 返回 15 行:
Rejection
-----------
0.02897429
0.02215681
0.00000000
0.00000000
0.00000000
0.58119017
0.24542928
1.17601530
1.41633147
0.00000000
0.00000000
0.51131100
0.00000000
1.10613613
0.09033161
在我将查询转换为 SQL Server 2008 之后:
SELECT
ISNULL(SUM(ColValue1), 0.00) AS Rejection
FROM
Table2 b
RIGHT OUTER JOIN
Tabel1 a ON b.col1 = a.col2
,Table3 c
WHERE
c.col1 = a.col3
AND b.colx = 'xxxxxxx'
AND YEAR(TDate) = 2017
AND MONTH(TDate) = 11
GROUP BY
c.columnz
ORDER BY
c.columnZ
查询只返回 9 行(忽略所有空行)
Rejection
----------
0.02897429
0.02215681
0.58119017
0.24542928
1.17601530
1.41633147
0.51131100
1.10613613
0.09033161
请帮我修复新查询并让它返回所有 15 行。
所有三个表和列
SELECT
[REJ_CODE], [REJ_GROUPING], [TYPE]
FROM
[QAApr2006].[dbo].[Reject_Group];
SELECT
[REJ_RKEY], [REJECT_CODE],
[REJECT_DESCRIPTION], [REJECT_ABBRV]
FROM
[QAApr2006].[dbo].[Reject_Code];
SELECT
[PR_GRP_CODE], [PROD_CODE],
[CUSTOMER_PART_DESC], [TDATE],
[REJ_CODE], [REJ_PARTS], [REJ_M2],
[P_TYPE], [WO_Number],
[REJ_CODE_ABBRV], [REJECT_DESCRIPTION]
FROM
[QAApr2006].[dbo].[QA_Rej_Det1];
在“c.REJ_GROUPING 有 15 行和 b.[Tdate]”下面的评论中,所以我按如下方式颠倒了查询:
SELECT
ISNULL(SUM(REJ_M2), 0.00) AS rejection
FROM [QAApr2006].[dbo].Reject_Group c
LEFT JOIN [QAApr2006].[dbo].Reject_Code a ON c.REJ_CODE = a.REJECT_CODE
LEFT JOIN [QAApr2006].[dbo].QA_Rej_Det1 b ON a.REJ_RKEY = b.REJ_CODE
AND b.CUSTOMER_PART_DESC = '0115761002'
AND b.[Tdate] >= '20171101'
AND b.[Tdate] < '20171201'
GROUP BY
c.REJ_GROUPING
ORDER BY
c.REJ_GROUPING
它起作用了(尽管在其他地方添加了确认评论)。
原文:别名 b 用在 the 中,where clause
所以它最有可能是我们在 the 中首先使用的表,from clause
所以外连接现在是左连接。
SELECT
ISNULL(SUM(REJ_M2), 0.00) AS rejection
FROM [QAApr2006].[dbo].QA_Rej_Det1 b
LEFT JOIN [QAApr2006].[dbo].Reject_Code a ON b.REJ_CODE = a.REJ_RKEY
LEFT JOIN [QAApr2006].[dbo].Reject_Group c ON a.REJECT_CODE = c.REJ_CODE
WHERE b.CUSTOMER_PART_DESC = '0115761002'
AND TDate >= '20171101'
AND TDate < '20171201'
GROUP BY
REJ_GROUPING
ORDER BY
Rej_Grouping
没有充分的理由使用 YEAR() 和 MONTH() 来实现准确的日期范围过滤器,只需指定您想要的月份的第一个日期和下个月的第一个日期。
此查询严重受害的另一件事是REJ_M2
,REJ_GROUPING
并且Tdate
没有表别名,所以我真的不知道左连接是否有效。使用表别名(或表名,如果未提供别名)引用EVER列。
作为个人笔记,我真的很讨厌在查询 (a,b,c...) 中指示序列的别名,因为如果我们突然需要重新排序,这些别名真的很痛苦。我更喜欢“第一个字母”或“每个单词的第一个字母”作为别名方法,例如
SELECT
ISNULL(SUM(rc.REJ_M2), 0.00) AS rejection
FROM [QAApr2006].[dbo].QA_Rej_Det1 q
LEFT JOIN [QAApr2006].[dbo].Reject_Code rc ON q.REJ_CODE = rc.REJ_RKEY
LEFT JOIN [QAApr2006].[dbo].Reject_Group rg ON rc.REJECT_CODE = rg.REJ_CODE
WHERE q.CUSTOMER_PART_DESC = '0115761002'
AND q.TDate >= '20171101'
AND q.TDate < '20171201'
GROUP BY
q.REJ_GROUPING
ORDER BY
q.Rej_Grouping
这个变体可能不起作用,因为列别名只是猜测,我没有解决这个问题。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句