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