sql-use inside'where''over(partition by…)

of1yzvn4 于 2021-06-15 发布在 Mysql

关注(0)|答案(3)|浏览(327)

如何从标题多次运行的sql查询中获取结果？这显示了正确的值用于“ppp”但过滤 AND (count(*) over (partition by TITLE)) > 1 不起作用。
任何帮助都将不胜感激。

SELECT
    AAA,
    BBB,
    CCC,
    count(*) over (partition by TITLE) as PPP
FROM 
    my_tabel
WHERE
    AAA IS NOT NULL
    AND BBB = 'SOMETHING'
    AND (count(*) over (partition by TITLE)) > 1
;

返回的错误

ERROR: window functions are not allowed in WHERE

当我使用

...
    WHERE
        AAA IS NOT NULL
        AND BBB = 'SOMETHING'
;

所有内容都正确显示，但我需要一个ppp>1的列

sql mysql

来源：https://stackoverflow.com/questions/54112277/sql-use-inside-where-over-partition-by

3条答案

按热度按时间

afdcj2ne1#

窗口函数不能用于 WHERE 子句作为其结果在满足 WHERE 条款已提取。 Package 另一个 SELECT 在它周围过滤 WHERE 关于这个 SELECT .

SELECT aaa,
       bbb,
       ccc,
       ppp
       FROM (SELECT aaa,
                    bbb,
                    ccc,
                    count(*) OVER (PARTITION BY title) ppp
                    FROM my_tabel
                    WHERE aaa IS NOT NULL
                          AND bbb = 'SOMETHING') x
       WHERE ppp > 1;

赞(0）回复(0）举报 2021-06-16

1szpjjfi2#

如果每一行都有一个唯一的标识符——您应该这样做——只需使用 exists :

select t.*
from my_table t
where aaa is not null and bbb = 'something' and
      exists (select 1
              from my_table t2
              where t2.title = t.title and
                    t2.aaa is not null and t2.bbb = t.bbb and
                    t2.id <> t.id
            );

这假设您实际上不需要将计数用于其他目的。

赞(0）回复(0）举报 2021-06-15

oknrviil3#

您还可以如下所示创建cte，并使用来自cte的ppp添加过滤器：

WITH cte AS(
    SELECT
        AAA,
        BBB,
        CCC,
        count(*) over (partition by TITLE) as PPP
    FROM 
        my_tabel
    WHERE
        AAA IS NOT NULL
        AND BBB = 'SOMETHING'
        )
    Select *
    FROM CTE
    WHERE PPP > 1;

赞(0）回复(0）举报 2021-06-15

我来回答

sql-use inside'where''over(partition by…)

3条答案

相关问题

热门标签

最新问答