mysql 查询中的 DISTINCT 从结果集中删除记录 [英] DISTINCT in mysql query removing the records from resultset

问题描述

DISTINCT 在 mysql 查询中从结果集中删除记录

DISTINCT in mysql query removing the records from resultset

我有三张桌子

TBL1       TBL2         TBL3
----       ------       --------    
tbl1_id    tbl2_id      tbl3_id                       
cid        fkcid        fkcid
           fktbl1_id    fktbl2_id

我有查询以获取 TBL3 的记录

I have query to get records of TBL3

select distinct tbl3.* from TBL3 tbl3 
inner join TBL2 tbl2 on tbl2.tbl2_id = tbl3.fktbl2_id and tbl2.fkcid = tbl3.fkcid 
inner join TBL1 tbl1 on tbl1.tbl1_id = tbl2.fktbl1_id and tbl2.fkcid = tbl1.cid;

这个查询给了我大约 1000 条记录.

This query gives me around 1000 records.

但是当我从查询中删除不同的时，它给了我大约 1100 条记录.表中没有重复记录.另外，我确认这额外的 100 条记录不是重复的.请注意，在 query with distinct keyword 中找不到这额外的 100 条记录.

But when I removes distinct from query it gives me around 1100 records. There is no duplicate records in table.Also I confirmed that these extra 100 are not duplicate.Please note That these extra 100 records are not found in query with distinct keyword.

为什么此查询的行为异常.如果我犯了错误，请帮助我更清楚地理解并纠正我.谢谢

Why this query is behaving unexpectedly.Please help me to understand more clearly and correct me if i am making mistake. Thank you

推荐答案

您在 tbl1 或 tbl2 中有多个记录映射到同一个 tbl3，并且由于您只在输出中选择了 tbl3.*，DISTINCT 会删除重复项.要查找重复项，请删除 DISTINCT，将 COUNT(*) 添加到 SELECT 子句，并在末尾添加 GROUP BY 和 HAVING，例如:

You have multiple records in tbl1 or tbl2 that map to the same tbl3, and since you're only selecting tbl3.* in your output, DISTINCT removes the duplication. To instead find what the duplicates are, remove the DISTINCT, add a COUNT(*) to the SELECT clause, and add at the end a GROUP BY and HAVING, such as:

select tbl3.*, count(*) 
from TBL3 tbl3  
inner join TBL2 tbl2 on tbl2.tbl2_id = tbl3.fktbl2_id and tbl2.fkcid = tbl3.fkcid  
inner join TBL1 tbl1 on tbl1.tbl1_id = tbl2.fktbl1_id and tbl2.fkcid = tbl1.cid 
group by tbl3.tbl3_id, tbl3.fkcid, tbl3.fktbl2_id having count(*) > 1;

这篇关于mysql 查询中的 DISTINCT 从结果集中删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！