SQL:查询在一个字段中查找具有公共值的多个项

Question

我有一个数据库，其结构如下：

UserInfo
----------
Id (INT PK AI)
UserId (INT)
InfoTypeId (INT FK -> InfoType.Id)
Value (varchar)

InfoType
----------
Id (INT PK AI)
InfoTypeName (VARCHAR)

行表示给定用户的信息。例如，InfoType表中可能有以下行：

Id | InfoTypeName
-----------------
1  | "First Name"
2  | "Last Name"
3  | "Age"

UserInfo中的行如下所示：

Id | UserId | InfoTypeId | Value
--------------------------------
1  | 1      | 1          | "John"
2  | 1      | 2          | "Smith"
3  | 1      | 3          | "20"
4  | 2      | 1          | "John"
5  | 2      | 2          | "Doe"
6  | 2      | 3          | "30"
7  | 3      | 1          | "Jane"
8  | 3      | 2          | "Doe"
9  | 3      | 3          | "25"
10 | 4      | 1          | "John"
11 | 4      | 2          | "Smith"
12 | 4      | 3          | "25"

我想要创建一个查询，从UserInfo表中删除引用名为"John“而姓"Smith”的用户的所有行。这意味着我想要删除第1、2、3、10、11和12行。

编辑：

我能够获得一个用户I列表

SELECT DISTINCT ui1.UserId 
FROM UserInfo ui1
    INNER JOIN UserInfo ui2 
    ON ui1.UserId = ui2.UserId
WHERE (ui1.InfoTypeId = 1 AND ui1.Value = "John" AND ui2.InfoTypeId = 2 AND ui2.Value = "Smith");

此外，还要删除的行列表。

SELECT * FROM UserInfo ui WHERE ui.UserId IN
    (SELECT DISTINCT ui1.UserId 
    FROM UserInfo ui1
        INNER JOIN UserInfo ui2 
        ON ui1.UserId = ui2.UserId
    WHERE (ui1.InfoTypeId = 1 AND ui1.Value = "John" AND ui2.InfoTypeId = 2 AND ui2.Value = "Smith"));

但是，当我将SELECT替换为这样的DELETE时：

DELETE FROM UserInfo WHERE UserId IN
    (SELECT DISTINCT ui1.UserId 
    FROM UserInfo ui1
        INNER JOIN UserInfo ui2 
        ON ui1.UserId = ui2.UserId
    WHERE (ui1.InfoTypeId = 1 AND ui1.Value = "John" AND ui2.InfoTypeId = 2 AND ui2.Value = "Smith"));

我得到了

SQL Error [1093] [HY000]: You can't specify target table 'UserInfo' for update in FROM clause

Answer 1

您可以在这里使用join：

delete ui
    from UserInfo ui join
         (select ui.UserId,
                 max(case when it.InfoTypeName = 'First name' then value end) as first_name,
                 max(case when it.InfoTypeName = 'Last name' then value end) as last_name
          from UserInfo ui join
               InfoType it
               on ui.InfoTypeId = it.id
          group by ui.UserId
         ) uu
         using (UserId)
    where uu.first_name = 'John' and uu.last_name = 'Smith';