需要帮助理解这个SQL查询

时间:2022-06-30 21:31:44

Can someone please help me in understanding this SQL code? This is what is actually confusing me more - END = G.CODE

谁能帮助我理解这个SQL代码吗?这就是让我更困惑的- END = G.CODE

SELECT something, something
FROM ABCD A
JOIN GHIJ G ON 
    CASE    
        WHEN A.CODE = 'not available' THEN 'NA_CODE'
        ELSE A.CODE END = G.CODE
                   LEFT JOIN PRODUCT P ON P.ID = A.ID
                   WHERE P.ID IS NULL;
GO          

Thanks Isha

由于Isha

2 个解决方案

#1


3  

When you look at properly formatted code you will understand it.

当您查看正确格式化的代码时,您将理解它。

CASE WHEN A.CODE = 'not available' THEN 'NA_CODE' ELSE A.CODE END = G.CODE

consider the join clause like

将join子句看作

 B.XYZ = G.CODE

Where B.XYZ is a case statement for manipulating joining column A.CODE for values 'not available' to 'NA_CODE' so that it could match same value in G.CODE. Except the value 'not available' everything is fine so the case statement only manipulates 'not available' to 'NA_CODE', so that join clause could match it with value inside G.CODE.

在B。XYZ是一个用于操作连接列a的case语句。为“NA_CODE”的值“不可用”编写代码,以便在g.码中匹配相同的值。除了值“不可用”之外,一切都没问题,所以case语句只对“NA_CODE”操作“不可用”,因此join子句可以将它与G.CODE中的值匹配。

Look for case statement for more details.

更多细节请参阅案例说明。

Hope you understood.

希望你理解。

Here :-

在这里:

B.XYZ =[CASE WHEN A.CODE = 'not available' THEN 'NA_CODE' ELSE A.CODE END]

B。XYZ =[情况。代码= '不可用',然后是'NA_CODE' ELSE A。代码结束)

#2


1  

There is more you need to understand in this query.

在这个查询中,您需要了解的更多。

You got a good answer for the "CASE ... END = G.CODE" - indeed, the point there is that "END" does not exist by itself, it is the last key word in the CASE expression syntax.

你得到了一个很好的答案。结束= G。代码——实际上,关键是“END”本身并不存在,它是CASE表达式语法中的最后一个关键字。

With that out of the way, note that your query will return no rows. This is because at the end you have

有了这些,请注意查询将不会返回任何行。这是因为最后你有

...LEFT JOIN PRODUCT P ON P.ID = A.ID
   WHERE P.ID IS NULL

Rows with P.ID "IS NULL" are not returned in the LEFT JOIN - only rows where P.ID = A.ID and rows where A.ID is NULL are returned. (When P.ID is NULL, the equality with A.ID is UNKNOWN, so the row is NOT returned.)

行和P。ID“为空”在左连接中不返回-只有P的行。ID =。ID和A所在的行。返回ID为NULL。(当P。ID为NULL,等于A。ID是未知的,因此不返回行。

#1


3  

When you look at properly formatted code you will understand it.

当您查看正确格式化的代码时,您将理解它。

CASE WHEN A.CODE = 'not available' THEN 'NA_CODE' ELSE A.CODE END = G.CODE

consider the join clause like

将join子句看作

 B.XYZ = G.CODE

Where B.XYZ is a case statement for manipulating joining column A.CODE for values 'not available' to 'NA_CODE' so that it could match same value in G.CODE. Except the value 'not available' everything is fine so the case statement only manipulates 'not available' to 'NA_CODE', so that join clause could match it with value inside G.CODE.

在B。XYZ是一个用于操作连接列a的case语句。为“NA_CODE”的值“不可用”编写代码,以便在g.码中匹配相同的值。除了值“不可用”之外,一切都没问题,所以case语句只对“NA_CODE”操作“不可用”,因此join子句可以将它与G.CODE中的值匹配。

Look for case statement for more details.

更多细节请参阅案例说明。

Hope you understood.

希望你理解。

Here :-

在这里:

B.XYZ =[CASE WHEN A.CODE = 'not available' THEN 'NA_CODE' ELSE A.CODE END]

B。XYZ =[情况。代码= '不可用',然后是'NA_CODE' ELSE A。代码结束)

#2


1  

There is more you need to understand in this query.

在这个查询中,您需要了解的更多。

You got a good answer for the "CASE ... END = G.CODE" - indeed, the point there is that "END" does not exist by itself, it is the last key word in the CASE expression syntax.

你得到了一个很好的答案。结束= G。代码——实际上,关键是“END”本身并不存在,它是CASE表达式语法中的最后一个关键字。

With that out of the way, note that your query will return no rows. This is because at the end you have

有了这些,请注意查询将不会返回任何行。这是因为最后你有

...LEFT JOIN PRODUCT P ON P.ID = A.ID
   WHERE P.ID IS NULL

Rows with P.ID "IS NULL" are not returned in the LEFT JOIN - only rows where P.ID = A.ID and rows where A.ID is NULL are returned. (When P.ID is NULL, the equality with A.ID is UNKNOWN, so the row is NOT returned.)

行和P。ID“为空”在左连接中不返回-只有P的行。ID =。ID和A所在的行。返回ID为NULL。(当P。ID为NULL,等于A。ID是未知的,因此不返回行。