mysql - mysql 中不包含特定值的行数

Question

我有一个这样的表，就是mysql。我需要获取不包含特定属性的不同 doc_id 的计数。例如，如果属性是“product”，则结果应为1，(即只有第4个doc_id不包含product)

+--------+-----------+--------+
| doc_id | attribute | value  |
+--------+-----------+--------+
|     1 | product   | mobile |
|     1 | model     | lumia  |
|     1 | camera    | 5mp    |
|     2 | product   | mobile |
|     2 | model     | lumia  |
|     2 | ram       | 1gb    |
|     3 | product   | mobile |
|     3 | year      | 2014   |
|     3 | made-in   | china  |
|     4 | brand     | apple  |
|     4 | model     | iphone |
|     4 | camera    | 5mp    |
|     5 | product   | camera |
|     5 | brand     | canon  |
|     5 | price     | 20000  |

Answer 1

您可以使用 count(distinct) 在没有子查询的情况下完成此操作:

select count(distinct doc_id) - count(distinct case when attribute = 'product' then doc_id end)
from table t;

使用子查询，您首先按 doc_id 聚合，然后进行计数:

select count(*)
from (select doc_id, max(attribute = 'product') as has_product
      from table t
      group by doc_id
     ) t
where has_product = 0;