时间戳

首页 > 解决方案 > 仅当计数匹配时才计数行

sql - 仅当计数匹配时才计数行

问题描述

我怎样才能实现这样的目标？

select count(distinct a.name) 
from table_a as a 
where (
    select count(*) 
    from table_b as b 
    where b.id = a.id
  ) = (
    select count(*) 
    from table_c as c 
    where c.id = a.id
);

仅当与第一个相关的两个不同表中的计数具有相同的 ID 计数时，我才想计算行数。假设我们使用的是 PostgreSQL。

标签： sqlpostgresqlperformance

解决方案

我试图模拟你描述的情况。

CREATE TABLE a (name VARCHAR, id INT8);
CREATE TABLE b (id INT8);
CREATE TABLE c (id INT8);
INSERT
  INTO a
VALUES ('none', 1),
       ('equal', 2),
       ('more_b', 3),
       ('more_c', 4),
       ('no_b', 5),
       ('no_c', 6);
INSERT INTO b VALUES (2), (3), (3), (4), (6);
INSERT INTO c VALUES (2), (3), (4), (4), (5);
SELECT name
  FROM (
          WITH b_agg AS (
                        SELECT a.id,
                               name,
                               CASE
                               WHEN b.id IS NULL THEN 0
                               ELSE count(*)
                               END AS smart_count
                          FROM a LEFT JOIN b ON a.id = b.id
                      GROUP BY a.id, a.name, b.id
                     ),
               c_agg AS (
                        SELECT a.id,
                               name,
                               CASE
                               WHEN c.id IS NULL THEN 0
                               ELSE count(*)
                               END AS smart_count
                          FROM a LEFT JOIN c ON a.id = c.id
                      GROUP BY a.id, a.name, c.id
                     )
        SELECT a.name,
               a.id,
               b_agg.smart_count AS b_count,
               c_agg.smart_count AS c_count
          FROM a JOIN b_agg ON a.id = b_agg.id JOIN c_agg ON a.id = c_agg.id
       ) AS t
 WHERE b_count = c_count;
DROP TABLE a;
DROP TABLE b;
DROP TABLE c;

运行时，这会产生

 name
-------
 equal
 none
(2 rows)

推荐阅读