使用substr列通过子查询连接组

Question

我试图通过子查询将一个组与另一个表连接起来，但由于子查询包含仅在子查询中有效的别名，所以正在处理问题。问题是，AREA表中只有3位数字的锁存id，作为ARE_CODE列中较长字符串的一部分，这就是我使用substr的原因。以下是子查询组：

SELECT SUBSTR(ARE_CODE,0,3) AS "Locker", COUNT(ARE_UID) AS "Qty"
FROM AREA
WHERE SUBSTR(ARE_CODE,0,1)='L' 
GROUP BY SUBSTR(ARE_CODE,0,3)

它自己运行得很好--问题是我试图将它与主查询中的另一个表连接起来。别名仅在子查询本身中有效，不能像下面所做的那样被引用。我也尝试直接加入substr函数，但这也不起作用。

SELECT P.Locker, P.Permit, L.Qty
FROM PERMITS P
INNER JOIN (SELECT SUBSTR(ARE_CODE,0,3) AS "Locker", COUNT(ARE_UID) AS "Qty"
    FROM AREA_LKP
    WHERE SUBSTR(ARE_CODE,0,1)='L' 
    GROUP BY SUBSTR(ARE_CODE,0,3) L ON L.Locker = P.Locker

关于如何解决这个问题，有什么建议吗？

Answer 1

您只需要从别名中删除双引号，因为它们使别名大小写敏感化，并且必须在查询中使用双引号时使用双引号。

所以最好不要对别名使用双引号，除非您希望给出区分大小写的名称或希望别名中有空格。

SELECT P.Locker, P.Permit, L.Qty
FROM PERMITS P
INNER JOIN (SELECT SUBSTR(ARE_CODE,0,3) AS Locker, COUNT(ARE_UID) AS Qty
    FROM AREA_LKP
    WHERE SUBSTR(ARE_CODE,0,1)='L' 
    GROUP BY SUBSTR(ARE_CODE,0,3) L ON L.Locker = P.Locker

Answer 2

问题是双引号。但是，您的查询还有其他一些怪癖：

在字符串中，第一个字符位于1，而不是0(虽然0是allowed).

• LIKE，但它是进行字符串比较的好方法。

所以：

SELECT P.Locker, P.Permit, L.Qty
FROM PERMITS P INNER JOIN
     (SELECT SUBSTR(ARE_CODE, 1, 3) AS Locker, COUNT(*) AS Qty
      FROM AREA_LKP
      WHERE ARE_CODE LIKE 'L%'
      GROUP BY SUBSTR(ARE_CODE, 1, 3)
     ) L
     ON L.Locker = P.Locker;

我应该注意到，LIKE允许在列上使用索引，但是SUBSTR()通常会阻止这一点。

Answer 3

为什么要在子查询的末尾设置表的别名？

INNER JOIN (SELECT SUBSTR(ARE_CODE,0,3) AS "Locker", COUNT(ARE_UID) AS "Qty"
    FROM AREA_LKP
    WHERE SUBSTR(ARE_CODE,0,1)='L' 
    GROUP BY SUBSTR(ARE_CODE,0,3) L

尝试：

INNER JOIN (SELECT SUBSTR(ARE_CODE,0,3) AS "Locker", COUNT(ARE_UID) AS "Qty"
    FROM AREA_LKP L
    WHERE SUBSTR(ARE_CODE,0,1)='L' 
    GROUP BY SUBSTR(ARE_CODE,0,3)

要在不同语句之间共享表达式，请看一下with-语句：https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/with.html。

最后，你确定：

L.Locker = P.Locker

可以按照预期进行评估吗？那么，L.Locker的减除值与P.Locker相同吗？