oracle-sql:通过拥有来计数组

Question

我有两个表A和B。

两个表都有一个列Prod_id，即两个表可以在这个列上联接，如

A.Prod_id = B.Prod_id

Prod_Id不是这两个表中任何一个表的主键。两个表都有不同的主键列。

Prod_id列具有以下值：

prod_id--------------111222111333222

B Prod_id列具有以下值：

prod_id--------------111222111111333222333

与A表中相同Prod_Id的行数相比，B表中任何Prod_Id的行数将总是更多（111333）或相同（222）。

我必须编写一个查询来找出那些Prod_id，其中B表的行数比a表多。

我有其他条件，我必须在where子句中使用其他列。但这些在这里并不相关。

你能帮帮我吗？我是SQL的新手。

Answer 1

试试这个：

SELECT b.prod_id
FROM (
  SELECT prod_id, COUNT(*) AS prodCount
  FROM tableA
  GROUP BY prod_id
  ) a
INNER JOIN (
  SELECT prod_id, COUNT(*) AS prodCount
  FROM tableB
  GROUP BY prod_id
  ) b ON a.prod_id = b.prod_id
WHERE b.prodCount > a.prodCount

sqlfiddle演示

这将获得表A中每个prod_id的计数，在表B中，通过prod_id将它们连接起来，并验证B中出现次数比A中多的是什么。

正如Gordon所建议的，这里有一个版本考虑了tat-tableA没有prod_id记录的可能性：

SELECT b.prod_id
FROM (
  SELECT prod_id, COUNT(*) AS prodCount
  FROM tableA
  GROUP BY prod_id
  ) a
RIGHT JOIN (
  SELECT prod_id, COUNT(*) AS prodCount
  FROM tableB
  GROUP BY prod_id
  ) b ON a.prod_id = b.prod_id
WHERE b.prodCount > a.prodCount
  OR a.prod_id IS NULL

对于这些记录，.prod_is将为NULL，因为我们正在执行RIGHT JOIN。

sqlfiddle演示

Answer 2

假设您的主键名为pk，这将产生您想要的结果：

select
  b.prod_id,
  count(distinct a.pk),
  count(distinct b.pk)
from
  b left join a on a.prod_id = b.prod_id
group by
  b.prod_id
having
  count(distinct b.pk) > count(distinct a.pk)

SQLFiddle此处

Answer 3

SQL Fiddle

Oracle 11g R2架构设置:

CREATE TABLE a ( prod_id ) AS
          SELECT 111 FROM DUAL
UNION ALL SELECT 222 FROM DUAL
UNION ALL SELECT 111 FROM DUAL
UNION ALL SELECT 333 FROM DUAL
UNION ALL SELECT 222 FROM DUAL;
CREATE TABLE b ( prod_id ) AS
          SELECT 111 FROM DUAL
UNION ALL SELECT 222 FROM DUAL
UNION ALL SELECT 111 FROM DUAL
UNION ALL SELECT 111 FROM DUAL
UNION ALL SELECT 333 FROM DUAL
UNION ALL SELECT 222 FROM DUAL
UNION ALL SELECT 333 FROM DUAL;

查询1：

SELECT   prod_id
FROM     a
GROUP BY prod_id
HAVING   COUNT(1) < (SELECT COUNT(1) FROM b WHERE a.prod_id = b.prod_id )

结果：

| PROD_ID |
|---------|
|     333 |
|     111 |

Answer 4

select t2.prod_id
(select count(*) amount, A.prod_id from A group by prod_id) t1,
(select count(*) amount, B.prod_id from B group by prod_id) t2
where t1.prod_id = t2.prod_id
and t2.amount > t1.amount