mysql - 对整个左外连接集执行过滤

Question

我有一个查询，它通过 id 进行左外连接，如下所示(简化):

SELECT `user`.`id`, `user`.`firstName`, `user`.`lastName`, 
       `user_details`.`value` AS `user_details.value`, `user_details`.`key` AS `user_details.key`
FROM `users` AS `user` LEFT OUTER JOIN
     `user_details` AS `details`
     ON `user`.`id` = `user_details`.`userId`

我正在使用 sequelize 来生成这个查询。
我想要做的是根据 user_details 表中的一个特定条目过滤每个用户。例如，我有一些用户在 user_details 表中有三个项目。其中之一具有键 city 和值 Berlin 。我想执行一个查询，该查询返回将所有城市设置为柏林的用户列表，但我不仅想要那些值为柏林的匹配项，而且对于那些用户，我想要与他们的 user_details 项目的所有关系。
这样做的 SQL 方式是什么？ (我正在使用 mysql 8.0.18-google )
这可以通过内置的 Sequelize 功能来完成吗？
我试过这个:

User.findAll({
  include: [UserDetails],
  where: {
            [Op.and]: {
              '$user_details.key$': 'city',
              '$user_details.value$': 'Berlin'
            }
          }
})

但这只是返回用户和匹配城市的一个关系。
编辑:为了解决问题，一些示例数据
用户数据

| name   | age | id |
|--------|-----|----|
| Franz  | 34  | 1  |
| Holger | 42  | 2  |

用户详情

| key    | value     | userId |
|--------|-----------|--------|
| city   | Berlin    | 1      |
| Holger | Frankfurt | 2      |
| gender | male      | 1      |
| gender | male      | 2      |

当前结果(带有 sequelize 查询)

| id |  name | age | key  | value  |
|----|-------|-----|------|--------|
| 1  | Franz | 34  | city | Berlin |

按用户详细信息城市过滤的通缉结果

| id |  name | age | key    | value  |
|----|-------|-----|--------|--------|
| 1  | Franz | 34  | city   | Berlin |
| 1  | Franz | 34  | gender | male   |

当前(非常难看而且肯定很慢)解决方案
将此添加到查询中:

attributes: {
  include: [
    [Sequelize.literal(`(SELECT COUNT(ud.userId) from user_details AS ud WHERE ud.value like '%${keyword}%' and ud.key = 'city' and ud.userId = user_details.userId GROUP BY ud.userId)`), 'num_city']
  ]
},

(user_details 来自“外部”查询)。
然后在下面添加然后检查这个

having: {
  '$num_city$': {
     [Op.gte]: 1
   }
}

Answer 1

您似乎想要用户具有 'city'/'berlin' 的三行的所有行。如果是这样，窗口函数可以这样做:

SELECT u.id, u.firstName, u.lastName, 
       ud.value AS `user_details.value`, ud.key AS `user_details.key`
FROM payees p JOIN
     (SELECT ud.*,
             SUM(value = 'city' and key = 'Berlin') OVER (PARTITION BY ud.userid) as num_city_berlin
      FROM user_details ud
     ) ud
     ON u.id = ud.userId
WHERE num_city_berlin = 3;

请注意 LEFT JOIN 不合适，因为您需要匹配。
如果您想要柏林的任何行，您只需将 = 3 更改为 >= 1 。