请教查询语句，从 [UNION 联合查询结果] 中再查出指定字段重复的数据？

SELECT t1,t2,t3,t4,t5 FROM A1 WHERE t3='237' OR t4='237' UNION
SELECT t1,t2,t3,aa as t4,t5 FROM B3 WHERE t3='237' OR t4='237' UNION
SELECT xx as t1,zz as t2,t3,t4,t5 FROM H5 WHERE t3='237' OR t4='237'

从这个联合查询结果中，再查出 t5 有重复(重复次数）的数据？(就是* + 1 个 COUNT 列，也就是 t1,t2,t3,t4,t5,count 这样字段的结果)

Union

select

Where

字段

5 条回复 2022-02-09 13:24:50 +08:00

qeqv

2022-02-09 01:07:57 +08:00

SELECT ANY_VALUE(t1), ..., COUNT(*) AS `count` FROM (...) GROUP BY t5 HAVING `count`>1

pppguest3962

2022-02-09 01:30:14 +08:00

SELECT t1,t2,t3,t4,t5,COUNT(*) AS `count` FROM
(SELECT t1,t2,t3,t4,t5 FROM A1 WHERE t3='237' OR t4='237' UNION
SELECT t1,t2,t3,aa as t4,t5 FROM B3 WHERE t3='237' OR t4='237' UNION
SELECT xx as t1,zz as t2,t3,t4,t5 FROM H5 WHERE t3='237' OR t4='237')
GROUP BY `t5` HAVING `count`>1

提示：1248 - Every derived table must have its own alias

似乎这样不行？@sevefev

xuanbg

2022-02-09 08:20:00 +08:00

select t5 from
(SELECT t1,t2,t3,t4,t5 FROM A1 WHERE t3='237' OR t4='237' UNION
SELECT t1,t2,t3,aa as t4,t5 FROM B3 WHERE t3='237' OR t4='237' UNION
SELECT xx as t1,zz as t2,t3,t4,t5 FROM H5 WHERE t3='237' OR t4='237') t
group by t5
having count(*) > 1

但这样似乎并没有什么鸟用啊。只能知道 t5 有哪几个值重复而已。

qeqv

2022-02-09 11:04:39 +08:00

注意上面我用了 ANY_VALUE 函数，如果是 mysql 5.6 可以不用，mysql 5.7 及以上的版本支持这个函数，而且默认环境参数下是必须要用这个函数的。
另外，提示里面已经说得很明显了，每一个临时表都得有自己的别名

SELECT ANY_VALUE(`t1`), ANY_VALUE(`t2`), ANY_VALUE(`t3`), ANY_VALUE(`t4`), t5, COUNT(*) AS `count` FROM (...) AS `tmp_table` GROUP BY `t5` HAVING `count`>1

c6h6benzene

2022-02-09 13:24:50 +08:00 via iPhone

你这个结果集加 count 那列是既要显示详细结果，又要显示聚合结果吗…