关联查询标签数量

需求

　　产品表：product

　　标签表：tag

　　中间表：tagassoc

　　关联关系：product.id = tagassoc.objectID,    

　　　　　　　tag.id = tagassoc.tagid

　　一个产品可以有多个标签, 标签删除后, tagassoc.tagid值为0

　　查询产品中有标签的数量和没有标签的数量

实现参考

SELECT
 IF (t.id IS NULL, -1, t.id) AS id,
 count(p.id) AS count
FROM
    product AS p
LEFT JOIN `tagassoc` AS tc ON tc.objectType = 2 AND tc.objectID = p.id AND tc.tagid != 0
LEFT JOIN `tag` AS t ON t.id = tc.tagid
WHERE
    p.deleted = '0'
AND 
    CASE
        WHEN t.deleted IS NULL THEN
            '0'
        ELSE
            t.deleted
    END = '0'
AND p. STATUS = '0'
GROUP BY
    t.id

结果

id	count
-1	13045
1	2
2	3

 

转载于:https://www.cnblogs.com/muyibg/p/9263945.html