Question

sql多表联合查询

菜鸟跪求大神指导：怎样用sql语句实现将下面的10个表联合查询？
我有10个表aut_user_recharge_record_1，aut_user_recharge_record_2 ........字段一样，如图

查寻出来如下效果





Answer 1

十张表加起来的数据量 大概是多少？ 若数据量 不是很大的话，可以按照 古舟蓑笠翁 的做法来
你SQL 中的from_unixtime 函数，这样 跑法，速率应该不是 很高吧!?

建议 分步骤去做，先把时间戳 换成 datetime 后，再一步一步汇总统计，统计规则，你是很清楚的

Answer 2

select datanum,pay_type,datatime
from (select * from aut_user_recharge_record_1
union all select * from aut_user_recharge_record_2
union all select * from aut_user_recharge_record_3
union all select * from aut_user_recharge_record_4
union all select * from aut_user_recharge_record_5
union all select * from aut_user_recharge_record_6
union all select * from aut_user_recharge_record_7
union all select * from aut_user_recharge_record_8
union all select * from aut_user_recharge_record_9
union all select * from aut_user_recharge_record_10)

追问

create_time是时间戳 还报（ # 1248 -每一个派生表必须有它自己的别名）错

追答

那你的日期值是从哪来的？
select datanum,pay_type,datatime
from (select * from aut_user_recharge_record_1
union all select * from aut_user_recharge_record_2
union all select * from aut_user_recharge_record_3
union all select * from aut_user_recharge_record_4
union all select * from aut_user_recharge_record_5
union all select * from aut_user_recharge_record_6
union all select * from aut_user_recharge_record_7
union all select * from aut_user_recharge_record_8
union all select * from aut_user_recharge_record_9
union all select * from aut_user_recharge_record_10) A

追问

日期值是我自己手写上的

追答

从时间戳要转换为日期很麻烦的。这个我也没做过。

追问

追答

SELECT count(a.account) datanum,pay_type,Date(from_unixtime(a.create_time))
datatime FROM (select * from aut_user_recharge_record_1
union all select * from aut_user_recharge_record_2
union all select * from aut_user_recharge_record_3
union all select * from aut_user_recharge_record_4
union all select * from aut_user_recharge_record_5
union all select * from aut_user_recharge_record_6
union all select * from aut_user_recharge_record_7
union all select * from aut_user_recharge_record_8
union all select * from aut_user_recharge_record_9
union all select * from aut_user_recharge_record_10) a
where 1=1 group by Date(from_unixtime(a.create_time)),pay_type order by a.create_time asc