mysql - 查询中的 Count() 返回双倍值(12 而不是 6)

Question

如果

，此查询将返回 reg_count 的值 12

cp.field_kurs_kategorie_tid IN ('28', '30', '39', '34', '31')

有超过三个条目。

cp.field_kurs_kategorie_tid IN ('28', '30', '39')

返回 6 的正确结果。有什么想法为什么会发生这种情况吗？

这是完整的查询:

    SELECT n.nid AS nid, n.title AS title, r.entity_id AS entity_id, tr.field_lifestyle_tid AS field_lifestyle_tid, tn.name AS name, ct.field_kurstyp_tid AS field_kurstyp_tid, cp.field_kurs_kategorie_tid AS field_kurs_kategorie_tid, pn.name AS pn_name, ga.field_grund_der_absage_value AS field_grund_der_absage_value, cd.field_kursdatum_value AS field_kursdatum_value, cd.field_kursdatum_value2 AS field_kursdatum_value2, COUNT(r.entity_id) AS reg_count
FROM 
l_node n
LEFT OUTER JOIN l_registration r ON n.nid = r.entity_id AND r.state != 'waitlist'
LEFT OUTER JOIN l_field_data_field_lifestyle tr ON n.nid = tr.entity_id
LEFT OUTER JOIN l_field_data_field_kurstyp ct ON n.nid = ct.entity_id
LEFT OUTER JOIN l_field_data_field_kurs_kategorie cp ON n.nid = cp.entity_id
LEFT OUTER JOIN l_taxonomy_term_data tn ON tr.field_lifestyle_tid = tn.tid
LEFT OUTER JOIN l_taxonomy_term_data pn ON cp.field_kurs_kategorie_tid = pn.tid
LEFT OUTER JOIN l_field_data_field_grund_der_absage ga ON ga.entity_id = n.nid AND ga.field_grund_der_absage_value IS NOT NULL AND ga.field_grund_der_absage_value != '0'
LEFT OUTER JOIN l_field_data_field_kursdatum cd ON n.nid = cd.entity_id AND cd.delta = 0
WHERE  (n.type = 'location') AND (tr.field_lifestyle_tid IN  ('19')) AND (ct.field_kurstyp_tid IN  ('26')) AND (cp.field_kurs_kategorie_tid IN  ('28', '30', '39', '34', '31')) AND (cd.field_kursdatum_value BETWEEN '2016-05-02' AND '2016-05-04 23:00') 
GROUP BY r.entity_id

有什么想法吗？非常感谢!

Answer 1

因为您使用 LEFT OUTER JOIN 进行连接，并且您正在计数的表 (l_registration) 不是最左边的表，所以根据您的数据，您将从 l_registration 返回多行，即链接到选定l_node记录。