mysql - 从 2 个 sql 查询的联合中获取不同的值

Question

早上好，

我试图从两个 sql 查询的联合中获取所有不同的值，该查询基于它们的标签。

SELECT t.id, t.tag, bd.owner, bp.param_value as role
FROM baseData db
LEFT JOIN baseParam bp ON bp.`status` = "active" AND bp.param_name = "role" AND bp.base_data_id = bd.id
RIGHT JOIN tag t ON t.base_data_id = bp.base_data_id AND t.`status` = "active"
WHERE bd.`status` = "active" AND bd.application = "template" 
AND (((bp.param_value = "public" OR bp.param_value IS NULL)))
GROUP BY t.tag

UNION

SELECT t.id, t.tag, bd.owner, bp.param_value as role
FROM baseData bd
LEFT JOIN baseParam bp ON bp.`status` = "active" AND bp.param_name = "role" AND bp.base_data_id = bd.id
RIGHT JOIN tag t ON t.base_data_id = bp.base_data_id AND t.`status` = "active"
INNER JOIN resTemplate cr ON cr.base_data_id = bd.id
WHERE bd.`status` = "active" AND bd.application = "template" AND cr.`status` = "active"
AND (((bp.param_value = "private" OR bp.param_value IS NULL))) 
GROUP BY t.tag;

两个查询都来自同一个表，唯一的区别是它们的角色是“公共(public)”和“私有(private)”。 Private 也有另一个表的考虑。此查询返回此表

在此表中，如红框中所示，在 Tag 列中，Group 是重复的。以及其他一些标签名称。如何更改我的 sql 查询以对其进行过滤以使标签列不同？

Answer 1

请尝试一下；)

SELECT * FROM
    (SELECT t.id, t.tag, bd.owner, bp.param_value as role
    FROM baseData db
    LEFT JOIN baseParam bp ON bp.`status` = "active" AND bp.param_name = "role" AND bp.base_data_id = bd.id
    RIGHT JOIN tag t ON t.base_data_id = bp.base_data_id AND t.`status` = "active"
    WHERE bd.`status` = "active" AND bd.application = "template" 
    AND (((bp.param_value = "public" OR bp.param_value IS NULL)))

    UNION

    SELECT t.id, t.tag, bd.owner, bp.param_value as role
    FROM baseData bd
    LEFT JOIN baseParam bp ON bp.`status` = "active" AND bp.param_name = "role" AND bp.base_data_id = bd.id
    RIGHT JOIN tag t ON t.base_data_id = bp.base_data_id AND t.`status` = "active"
    INNER JOIN resTemplate cr ON cr.base_data_id = bd.id
    WHERE bd.`status` = "active" AND bd.application = "template" AND cr.`status` = "active"
    AND (((bp.param_value = "private" OR bp.param_value IS NULL)))) TMP
GROUP BY tag;