mysql - 如何加入mysql？

Question

所有，我在加入mysql时遇到问题。我想得到一个staff_id使用 GROUP BY 计算唯一数量的字段的结果。如果我运行这个查询，我会一步一步得到我想要的结果。

SELECT id,staff_id,note,warning_date FROM tbl_warning GROUP BY staff_id HAVING (count(staff_id) > 0);

接下来我想加入另外两个表来获取诸如tbl_employment. com_id如comid, tbl_staff. name, tbl_staff. gender但结果是重复的。

`SELECT` `tbl_warning`.`id`, `tbl_warning`.`staff_id`, `tbl_warning`.`note`, `tbl_warning`.`warning_date`,`tbl_employment`.`com_id` as `comid`, `tbl_staff`.`name`, `tbl_staff`.`gender` FROM `tbl_warning` JOIN `tbl_employment` ON `tbl_employment`.`staff_id` = `tbl_warning`.`staff_id` JOIN `tbl_staff` ON `tbl_staff`.`id` = `tbl_warning`.`staff_id` HAVING (SELECT `staff_id` FROM `tbl_warning` GROUP BY `staff_id` HAVING (count(staff_id) > 1));

我想要独特的结果与第一个屏幕截图相同。谢谢！

标签： mysql

Answer 1

您应该加入一个子查询，该查询按计数查找匹配的人员：

SELECT
    t1.id,
    t1.staff_id,
    t1.note,
    t1.warning_date,
    t2.com_id as `comid`,
    t3.name,
    t3.gender
FROM tbl_warning t1 
INNER JOIN tbl_employment t2
    ON t2.staff_id = t1.staff_id
INNER JOIN tbl_staff t3
    ON t3.id = t1.staff_id
INNER JOIN
(
    SELECT staff_id
    FROM tbl_warning
    GROUP BY staff_id
    HAVING COUNT(*) > 1
) t
    ON t.staff_id = t2.staff_id;