sql - 使用 row_number() 的列名无效 OVER(PARTITION BY )

Question

我有两个表 TBL_CONTACT 和 TBL_PHONE 我正在尝试加入。

TBL_PHONE 表包含具有多个电话号码的联系人的重复行，因此我对这些进行分区并尝试仅选择第一个实例。

这是我的代码:

SELECT
    (CONTACT.CONTACTID),
    (CONTACT.FULLNAME),
    (PHONE.contactid),
    (PHONE.numberdisplay),
    (row_number() OVER(PARTITION BY PHONE.Contactid ORDER BY PHONE.Contactid)) prn
FROM
    "ACT2015Demo"."dbo"."TBL_CONTACT" AS CONTACT
INNER JOIN
    TBL_PHONE AS PHONE
    ON CONTACT.Contactid = PHONE.Contactid
WHERE
    CAST(Editdate AS DATE) = CAST(GETDATE() AS DATE)

这给出了以下内容:

CONTACTID   FULLNAME   CONTACTID    NUMBERDISPLAY   PRN
1001        Name1      1001         Tel1001 1       1
1001        Name1      1001         Tel1001 2       2
1002        Name2      1002         Tel1002 1       1
1003        Name3      1003         Tel1003 1       1
1003        Name3      1003         Tel1003 2       2
1003        Name3      1003         Tel1003 3       3

然后我想使用 PRN 列将输出限制为仅 PRN = 1 的行。我尝试了以下方法，因为这在过去对不太复杂的连接有用:

SELECT
    (CONTACT.CONTACTID),
    (CONTACT.FULLNAME),
    (PHONE.contactid),
    (PHONE.numberdisplay),
    (row_number() OVER(PARTITION BY PHONE.Contactid ORDER BY PHONE.Contactid)) prn
FROM
    "ACT2015Demo"."dbo"."TBL_CONTACT" AS CONTACT
INNER JOIN
    TBL_PHONE AS PHONE
    ON CONTACT.Contactid = PHONE.Contactid AND PRN = 1
WHERE
    CAST(Editdate AS DATE) = CAST(GETDATE() AS DATE)

但是，这给我带来了 PRN 的无效列名错误？我也尝试使用 PRN = 1 作为 WHERE 的一部分，但出现了同样的错误。

如何让 PRN 用作列名并限制输出？

Answer 1

有些人对 SQL 感到有点奇怪和困惑的是，您在选择列表中定义的列别名不能在 FROM 子句或 WHERE 子句中引用。

这是令人困惑的，因为列别名似乎是在查询的早期定义的(因为选择列表在 FROM 子句之前)。但是语法的顺序不是执行的顺序。

您可以通过将查询作为派生表 子查询运行来解决此问题:

SELECT * 
FROM (
    SELECT
        CONTACT.CONTACTID,
        CONTACT.FULLNAME,
        PHONE.contactid,
        PHONE.numberdisplay,
        ROW_NUMBER() OVER(PARTITION BY PHONE.Contactid ORDER BY PHONE.Contactid) AS PRN
    FROM
        "ACT2015Demo"."dbo"."TBL_CONTACT" AS CONTACT
    INNER JOIN
        TBL_PHONE AS PHONE
        ON CONTACT.Contactid = PHONE.Contactid
    WHERE
        CAST(Editdate AS DATE) = CAST(GETDATE() AS DATE)
    ) AS DerivedTable
WHERE PRN = 1

PS:MySQL 5.7 及以下版本不支持窗口函数。已经宣布此功能正在开发中，希望它会在 MySQL 8 中准备就绪。我认为您可能错误地标记了您的问题。

您使用的模式限定符 dbo 让我觉得您使用的是 Microsoft SQL Server 或 Sybase，这对吗？