MySQL选择id，其中至少有一个值不在列表中

I searched everywhere but couldn't find an answer. So right to the problem.

我到处搜索但找不到答案。所以问题正确。

lets say I have a table like this

假设我有一张这样的桌子

table1
+------+--------+
| id   | attr   |
+------+--------+
| 1    | weight |
| 1    | width  |
| 1    | length |
| 2    | width  |
| 2    | length |
+------+--------+

I would like a query to get the id of the row that is missing an attr. In this case the id 2.

我想要一个查询来获取缺少attr的行的id。在这种情况下，id 2。

The attr to check against can be a list e.g. ('weight','width','length','height') or a select e.g. SELECT DISTINCT attr FROM table1 WHERE id=1.

要检查的attr可以是例如列表。（'重量'，'宽度'，'长度'，'高度'）或选择例如SELECT DISTINCT attr FROM table1 WHERE id = 1。

I have tried with NOT IN and NOT EXISTS but I can only get 0 or all results.

我尝试过NOT IN和NOT EXISTS但我只能得到0或所有结果。

SELECT id FROM table1
 WHERE attr NOT IN ('weight', 'length', 'width','height')

and

和

SELECT  id
FROM    table1 t1
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    table1 t2
        WHERE   t2.name NOT IN('weight', 'length', 'width','height')
    )

EDIT

编辑

if table is like

如果表是这样的

+------+--------+
| id   | attr   |
+------+--------+
| 1    | weight |
| 1    | width  |
| 1    | length |
| 1    | height |
| 2    | width  |
| 2    | length |
| 2    | other  |
+------+--------+

How can I get the following? ie. the missing attr for row id (id as I explained in the comments is just an alias not a primary key id)

我怎样才能得到以下内容？即。行id的缺失attr（我在评论中解释的id只是别名而不是主键id）

+------+--------+
| id   | attr   |
+------+--------+
| 2    | width  |
| 2    | length |
+------+--------+

2 个解决方案

#1

There are several ways to do this. Here's one using aggregation:

有几种方法可以做到这一点。这是使用聚合的一个：

select id
from table1
where attr in ('weight','length','width')
group by id
having count(distinct attr) <> 3

#2

You can use group by with having clause, e.g.:

你可以使用group by with having子句，例如：

select id
from table
where attr in ('weight','width','length')
group by id
having count(distinct(attr)) < 3

#1