首页 文章

SQLite LEFT JOIN计数（*）？

提问于
浏览 次
0

我需要连接两个表（实际上是两个视图），这样对于左视图的每个选定行，右视图中都有一个行数 . 听起来像LEFT JOIN，但是在SQLite（这个测试database）和LEFT JOIN查询中：

SELECT TARGET.session_id session_id, TARGET.labeltype_id labeltype_id, TARGET.label_id label_id, count(SECONDARY.label_id) NOlabels 
FROM segment_extended TARGET LEFT JOIN segment_extended SECONDARY
WHERE TARGET.session_id = SECONDARY.session_id AND TARGET.lt_name= "Word" AND SECONDARY.lt_name ="Comments" 
AND ((SECONDARY.start <= TARGET.start AND TARGET.END <= SECONDARY.END) OR (TARGET.start <= SECONDARY.start AND SECONDARY.END <= TARGET.END))
AND TARGET.label != '' AND SECONDARY.label != '' 
GROUP BY TARGET.session_id,TARGET.labeltype_id, TARGET.label_id;

我得到的只是我期望的一小部分：

2   3   3   1
2   3   9   1

更扩展的查询会给出正确的结果：

SELECT session_id, labeltype_id, label_id, max(NOlabels) NOlabels 
FROM (SELECT TARGET.session_id session_id, TARGET.labeltype_id labeltype_id, TARGET.label_id label_id, count(SECONDARY.label_id) NOlabels 
    FROM segment_extended TARGET , segment_extended SECONDARY
    WHERE TARGET.session_id = SECONDARY.session_id AND TARGET.lt_name= "Word" AND SECONDARY.lt_name ="Comments" 
    AND ((SECONDARY.start <= TARGET.start AND TARGET.END <= SECONDARY.END) OR (TARGET.start <= SECONDARY.start AND SECONDARY.END <= TARGET.END))
    AND TARGET.label != '' AND SECONDARY.label != '' 
    GROUP BY TARGET.session_id,TARGET.labeltype_id, TARGET.label_id
    UNION
    SELECT TARGET.session_id session_id, TARGET.labeltype_id labeltype_id, TARGET.label_id label_id, 0 NOlabels 
    FROM segment_extended TARGET
    WHERE TARGET.lt_name= "Word"
    AND TARGET.label != '' 
    GROUP BY TARGET.session_id,TARGET.labeltype_id, TARGET.label_id)
GROUP BY session_id, labeltype_id, label_id
ORDER BY session_id,labeltype_id, label_id


session_id  labeltype_id    label_id    NOlabels
2   3   2   0
2   3   3   1
2   3   4   0
2   3   5   0
2   3   7   0
2   3   8   0
2   3   9   1
2   3   10  0

但似乎不必要地复杂化 . 左连接我做错了什么？

sqlite join sqlite3 count left-join

2 回答

  • 1

    您的加入不是左连接 .

    如果没有与连接条件匹配的行，则左连接会为右表添加NULL值 . 但是，查询没有连接条件，并且WHERE条件不受LEFT JOIN子句的影响 .

    用ON替换WHERE .

    回复于
  • 0

    在进行左连接时，您必须将左连接中的空值计为0记录，但仍包含它们 . 您可以在内部查询中使用CASE构造完成此操作，然后在外部group-by中使用SUM聚合函数 .

    SELECT session_id, labeltype_id, label_id, sum(has_label) NOlabels
FROM (
    SELECT TARGET.session_id session_id, TARGET.labeltype_id labeltype_id, TARGET.label_id label_id, CASE WHEN SECONDARY.label_id is NULL then 0 else 1 END has_label
    FROM
        segment_extended TARGET
        LEFT JOIN
            segment_extended SECONDARY on
                TARGET.session_id = SECONDARY.session_id
                AND SECONDARY.lt_name ="Comments"
                AND ((
                    SECONDARY.start <= TARGET.start AND TARGET.END <= SECONDARY.END)
                    OR (TARGET.start <= SECONDARY.start AND SECONDARY.END <= TARGET.END))
                AND SECONDARY.label != ''
    WHERE TARGET.lt_name= "Word" AND TARGET.label != '')
GROUP BY session_id, labeltype_id, label_id
    回复于

相关问题