MySQL联合查询多张表的记录值，并将表名和各自统计的数量作为字段展示

最近水群看到有小伙伴提问除了联合查询，还有没有别的方法可以查询多张表的记录值。

其实方法很简单，比如在事先得知需要查询的表名后，创建一个map对象集合，通过for循环查库可以分别对需要查询的表进行单次查询，每次循环过程中将结果中的表名作为key，将其记录值作为value存到该map中。

具体实现就不探究了，这里记录下联表查询的sql该怎么写。

实现一
如果需要保留记录值为0的表，可以不使用GROUP BY

SELECT
	'coupon' AS table_name,
	COUNT(*) AS count 
FROM
	coupon UNION ALL
SELECT
	'promotion' AS table_name,
	COUNT(*) AS count 
FROM
	promotion UNION ALL
SELECT
	'coupon_scope' AS table_name,
	COUNT(*) AS count 
FROM
	coupon_scope UNION ALL
SELECT
	'exchange_code' AS table_name,
	COUNT(*) AS count 
FROM
	exchange_code UNION ALL
SELECT
	'user_coupon' AS table_name,
	COUNT(*) AS count 
FROM
	user_coupon 

可以看到保留了记录值为0的表promotion

实现二
如果不需要保留记录值为0的表，需要使用GROUP BY

SELECT
	t.table_name AS table_name,
	count(*) AS count
FROM
	(
	SELECT
		'coupon' AS table_name
	FROM
		coupon UNION ALL	
	SELECT
		'promotion' AS table_name
	FROM
		promotion UNION ALL
	SELECT
		'coupon_scope' AS table_name
	FROM
		coupon_scope UNION ALL
	SELECT
		'exchange_code' AS table_name
	FROM
		exchange_code UNION ALL
	SELECT
		'user_coupon' AS table_name
	FROM
	user_coupon 
	) AS t GROUP BY t.table_name

此时就忽略了记录值为0的表
结果如下

实现三
当然也可以对每一张表进行GROUP BY，效果是一样的

SELECT
	'coupon' AS table_name,
	COUNT(*) AS count 
FROM
	coupon 
GROUP BY
	table_name UNION ALL
SELECT
	'promotion' AS table_name,
	COUNT(*) AS count 
FROM
	promotion 
GROUP BY
	table_name UNION ALL
SELECT
	'coupon_scope' AS table_name,
	COUNT(*) AS count 
FROM
	coupon_scope 
GROUP BY
	table_name UNION ALL
SELECT
	'exchange_code' AS table_name,
	COUNT(*) AS count 
FROM
	exchange_code 
GROUP BY
	table_name UNION ALL
SELECT
	'user_coupon' AS table_name,
	COUNT(*) AS count 
FROM
	user_coupon 
GROUP BY
	table_name

结果如下