带有左连接和 having 子句的 MySQL 查询

Question

我的查询有问题，你能告诉我哪里错了吗？
我有 3 个表:

users:

 `users` (
  `id` int(11)  AUTO_INCREMENT,
  `first_name` varchar(16),
  `last_name` varchar(16) 
  `email` varchar(16)
  `password` varchar(32)
  `phone` varchar(13)
  `age` tinyint(4)
  `gender` varchar(6) 
  )

users_eyes:

  `users_eyes` (
  `user_id` int(4),
  `user_eyescolor_id` tinyint(2),
  UNIQUE KEY `user_id` (`user_id`,`user_eyescolor_id`)
) 

users_eyestype:

  `users_eyestype` (
  `user_id` int(4),
  `user_eyestype_id` tinyint(2),
  UNIQUE KEY `user_id` (`user_id`,`user_eyestype_id`)
) 

这是我的查询

SELECT 
                        SQL_CALC_FOUND_ROWS
                        u.first_name, 
                        u.last_name,
                        u.age,
                        u.gender,
                        u.phone,
                        u.id
, GROUP_CONCAT(DISTINCT ue.user_eyescolor_id SEPARATOR  ' ' ) as eyes_color 
, GROUP_CONCAT(DISTINCT uet.user_eyestype_id SEPARATOR  ' ' ) as eyes_type
FROM users u
LEFT JOIN users_eyes ue ON u.id = ue.user_id
LEFT JOIN users_eyestype uet ON u.id = uet.user_id
WHERE
ue.user_eyescolor_id IN (1,2,3,4)
GROUP BY u.id
HAVING
COUNT(ue.user_id) = 4

结果是一个有两种眼睛的人，而不是这个有四种眼睛的人在我加入和 eyes_type 之前，一切都很完美。

Answer 1

最好有测试数据来让你正确。可能您需要 COUNT(distinct ue.user_id)。

而且不需要 LEFT JOIN users_eyes - 内部连接更适合 users_eyes 字段用于 where 和 having 子句。

更新:参见 a fixed query

说明:如果用户有例如4 users_eyes 和 3 users_eyestype 加入后他/她得到 12 条记录(笛卡尔积)。这就是 COUNT(ue.user_id) 失败的原因。

解决方案:使用 COUNT(user_eyescolor_id) = 4 代替。将来我会建议更改查询，但没有简单的解决方法。