mysql - 解释 SQL 连接语句
问题描述
我应该将此查询从 MYSQL 转换为 SQL Server。然而,加入声明让我失望。我还没有看到这样的连接,我对如何翻译它有点困惑。
SELECT
`Supplier Confirmed Orders` + `Log Tech Confirmed Orders` AS 'orders confirmed',
`Orders in CVN`-`Cancelled Orders` AS 'Orders in CVN',
tblloadingmonths.`month`,
tblvendorindex.`vendorindexid`,
'Service' AS category
FROM
tblloadingmonths
JOIN
tblvendorindex
LEFT JOIN
tblcvn ON tblloadingmonths.`month` = tblcvn.`month`
AND tblvendorindex.vendorindexid = tblcvn.vendorindexid
让我失望的是,loadingmonths 和 vendorindex 表没有任何公共字段,但它们正在被连接,然后与 cvn 连接。我一直被教导要做tableA join tableB ON colA = colB join tableC ON colB = colC,但不是tableA join talbeB left join tableC ON colA = colC AND colB = colC。就目前而言,查询不能以连接方式在 SQL Server 中运行。我不得不这样设置:
SELECT
CVN.[Supplier Confirmed Orders] + CVN.[Log Tech Confirmed Orders] AS 'orders confirmed',
(CVN.[Orders in CVN] - CVN.[Cancelled Orders]) AS 'Orders in CVN',
tblloadingmonths.month,
tblvendorindex.vendorindexid,
'Service' AS category,
'CVN Compliance' as metric
FROM
cvn
JOIN
tblvendorindex ON tblvendorindex.vendorindexid = CVN.vendorindexid
INNER JOIN
tblloadingmonths ON tblloadingmonths.month = CVN.month
对于这个转换后的查询,我得到了不同的结果。任何指导将不胜感激
解决方案
你被正确地教导你应该列出你的连接子句(ON...
)并且第二个查询显然更具可读性并且是首选。类似地,旧式连接只是列出WHERE
子句中的所有内容,INNER JOIN
但同样,这很难阅读。关于你的不同结果。它们是不同的,因为
cvn
表名不同于tblcvn
意味着它是一个不同的对象。如果那是类型-o,那么...- 首先你
LEFT JOINing
要tblloadingmonths
和tblloadingmonths
表......这意味着行必须存在于这两个表中(对于你的连接子句)tblcvn
才能返回行。但是,因为它是 aLEFT JOIN
,tblloadingmonths
所以tblloadingmonths
无论tblcvn
. 在第二个中,您将其cvn
用作基表并INNER JOIN
始终使用。这意味着cvn
要返回的行的连接子句必须存在匹配项。否则,它们将被过滤。
回顾一下,当使用LEFT JOIN
...
FROM
子句中的表格很重要。用LEFT JOIN
表格交换它可能会改变结果(如您所见)- 子句中的条件
WHERE
可以将LEFT
转换为INNER
连接。
我无法告诉您 MySQL 是否会根据键分配自动分配连接,或者它是否正在制作笛卡尔积。在 SQL Server 中使用JOIN
时,您必须列出ON
子句。你可以使用旧式连接......FROM Table1, Table2...
但这应该让你成为没有WHERE
子句的笛卡尔。这应该可以让您完成一些编辑:
SELECT
CVN.[Supplier Confirmed Orders] + CVN.[Log Tech Confirmed Orders] AS 'orders confirmed',
(CVN.[Orders in CVN] - CVN.[Cancelled Orders]) AS 'Orders in CVN',
tblloadingmonths.month,
tblvendorindex.vendorindexid,
'Service' AS category,
'CVN Compliance' as metric
FROM
tblvendorindex
INNER JOIN
tblloadingmonths ON tblvendorindex.??? = tblloadingmonths.???? --find out what the relation is, a foreign key perpahs. Would need a data model to determine.
LEFT JOIN
cvn ON tblloadingmonths.month = cnv.month
AND tblvendorindex.vendorindexid = cvn.vendorindexid
感谢您的评论,您确认 MySQL 连接正在隐式生成笛卡尔积,您可以CROSS JOIN
在 SQL Server 中显式地实现,正如您回答的那样。
SELECT
CVN.[Supplier Confirmed Orders] + CVN.[Log Tech Confirmed Orders] AS 'orders confirmed',
(CVN.[Orders in CVN] - CVN.[Cancelled Orders]) AS 'Orders in CVN',
tblloadingmonths.month,
tblvendorindex.vendorindexid,
'Service' AS category,
'CVN Compliance' as metric
FROM
tblvendorindex
CROSS JOIN tblloadingmonths
LEFT JOIN
cvn ON tblloadingmonths.month = cnv.month
AND tblvendorindex.vendorindexid = cvn.vendorindexid