MySQL 使用 SQL 比较两个表中标识符的计数

Using SQL to compare counts of identifiers from two tables

提问by Kyle Banerjee

I'm trying to construct a query that compares two tables with identical structure

我正在尝试构建一个比较具有相同结构的两个表的查询

Table 1:
ID | Data 

Table 2:
ID | Data

ID is a nonunique key (Data is repeatable but ID|Data combos are unique). I need a list of IDs where the COUNT of those IDs is greater in Table 2 than in Table 1.

ID 是一个非唯一键（数据是可重复的，但 ID|数据组合是唯一的）。我需要一个 ID 列表，其中表 2 中这些 ID 的 COUNT 大于表 1 中的 ID。

So for example, if

例如，如果

Table 1
a | data
a | data1    
b | data2

Table 2
a | data
a | data1
b | data2
b | data3

would generate the output "b"

将生成输出“b”

This feels like it should be easy, but my head is scrambled right now. I'm doing this in mysql if that affects options.

这感觉应该很容易，但我现在脑子里一片混乱。如果这会影响选项，我会在 mysql 中执行此操作。

采纳答案by Marlin Pierce

To get the count for each key,

要获得每个键的计数，

select count(*) as count, ID from Table1 group by ID

So, use this as a sub-query in the from clause, and join the tables.

因此，将其用作 from 子句中的子查询，并连接表。

select tt1.ID
from (select count(*) as count, ID from Table1 group by ID) tt1
  inner join (select count(*) as count, ID from Table2 group by ID) tt2
     on tt1.ID = tt2.ID
where tt1.count < tt2.count

回答by Tony Shih

Tested in MySQL.

在 MySQL 中测试。

select case when count(d1.id) > count(d2.id) then d1.id end as 'id'  from 
(select id from t1) d1,
(select id from t2) d2
group by d1.id