Mysql联合查询

一、union select多表合并查询

1、union select 和 union all select 的区别
union select 会去除两个表中重复的项目，比如选择的字段username,money在两个表中均出现过相同的值，那么union select 会将其中一个去除；
union all select 不会去除重复项，他会将两个表中选择的所有字段全部保留

2、demo实例
select username,sum(money) as total
from
(
select c_bet.username,c_bet.money from c_bet
union all select c_bet_3.username,c_bet_3.money from c_bet_3
union all select c_bet_7.username,c_bet_7.money from c_bet_7
)t 
group by username order by total desc

3、在查询结果中新增一列表示序号
SELECT
	a.username,
	a.total,
	@row_num := @row_num + 1 AS row_num 
FROM
	(
	SELECT
		username,
		sum( money ) AS total 
	FROM
		(
		SELECT
			c_bet.username,
			c_bet.money 
		FROM
			c_bet UNION ALL
		SELECT
			c_bet_3.username,
			c_bet_3.money 
		FROM
			c_bet_3 UNION ALL
		SELECT
			c_bet_7.username,
			c_bet_7.money 
		FROM
			c_bet_7 
		) t 
	GROUP BY
		username 
	ORDER BY
		total DESC 
		) a,(
	SELECT
	@row_num := 0 
	)b