SELECT * FROM (
SELECT newday,SUM(newje) AS ma FROM (
SELECT DATE(check_end_date) AS newday ,SUM(a.user_charge+0) AS newje FROM table1 a GROUP BY DATE(check_end_date )
UNION ALL
SELECT DATE(user_login_time),SUM(a.user_charge+0) FROM table2 a GROUP BY DATE(user_login_time)) a GROUP BY newday
) a1LEFT JOIN
(SELECT DATE(user_saving_day) AS newday,SUM(user_saving_num+0)
FROM table3 GROUP BY DATE(user_saving_day)) b1
ON a1.newday=b1.newday
UNION ALL
SELECT 'total',
(SELECT SUM(newje) AS ma FROM (
SELECT DATE(check_end_date) AS newday ,SUM(a.user_charge+0) AS newje FROM table1 a GROUP BY DATE(check_end_date )
UNION ALL
SELECT DATE(user_login_time),SUM(a.user_charge+0) FROM table2 a GROUP BY DATE(user_login_time)) a) s2,
'total',
(SELECT SUM(ma) FROM
(SELECT DATE(user_saving_day) AS newday,SUM(user_saving_num+0) AS ma
FROM table3 GROUP BY DATE(user_saving_day)) a1)
FROM DUAL以上是wwwwa老师帮我解答的问题,我还有几个小问题请教:1 我发现加个newday倒序就不知道加哪了
2 FROM DUAL是什么意思(双重?)
3 SUM(a.user_charge+0)是啥意思?
SELECT newday,SUM(newje) AS ma FROM (
SELECT DATE(check_end_date) AS newday ,SUM(a.user_charge+0) AS newje FROM table1 a GROUP BY DATE(check_end_date )
UNION ALL
SELECT DATE(user_login_time),SUM(a.user_charge+0) FROM table2 a GROUP BY DATE(user_login_time)) a GROUP BY newday
) a1LEFT JOIN
(SELECT DATE(user_saving_day) AS newday,SUM(user_saving_num+0)
FROM table3 GROUP BY DATE(user_saving_day)) b1
ON a1.newday=b1.newday
UNION ALL
SELECT 'total',
(SELECT SUM(newje) AS ma FROM (
SELECT DATE(check_end_date) AS newday ,SUM(a.user_charge+0) AS newje FROM table1 a GROUP BY DATE(check_end_date )
UNION ALL
SELECT DATE(user_login_time),SUM(a.user_charge+0) FROM table2 a GROUP BY DATE(user_login_time)) a) s2,
'total',
(SELECT SUM(ma) FROM
(SELECT DATE(user_saving_day) AS newday,SUM(user_saving_num+0) AS ma
FROM table3 GROUP BY DATE(user_saving_day)) a1)
FROM DUAL以上是wwwwa老师帮我解答的问题,我还有几个小问题请教:1 我发现加个newday倒序就不知道加哪了
2 FROM DUAL是什么意思(双重?)
3 SUM(a.user_charge+0)是啥意思?
2、 虚拟表
3、我的user_charge是字符型,转换成数字型,如果本身就是数字型,去掉+0
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM DUAL' at line 21
(我的条件语句是:$where="...";我想在整个语句外加: where ."$where;经测试不行。是不是只能加到table3的语句里?)
a1 ORDER BY newday DESC) FROM DUAL
SELECT newday,SUM(newje) AS ma FROM (
SELECT DATE(check_end_date) AS newday ,SUM(a.user_charge+0) AS newje FROM table1 a GROUP BY DATE(check_end_date )
UNION ALL
SELECT DATE(user_login_time),SUM(a.user_charge+0) FROM table2 a GROUP BY DATE(user_login_time)) a GROUP BY newday
) a1LEFT JOIN
(SELECT DATE(user_saving_day) AS newday1,SUM(user_saving_num+0)
FROM table3 GROUP BY DATE(user_saving_day)) b1
ON a1.newday=b1.newday1UNION ALL
SELECT DISTINCT 'total',
(SELECT SUM(newje) AS ma FROM (
SELECT DATE(check_end_date) AS newday ,SUM(a.user_charge+0) AS newje FROM table1 a GROUP BY DATE(check_end_date )
UNION ALL
SELECT DATE(user_login_time),SUM(a.user_charge+0) FROM table2 a GROUP BY DATE(user_login_time)) a) s2,
'total',
(SELECT SUM(ma) FROM
(SELECT DATE(user_saving_day) AS newday,SUM(user_saving_num+0) AS ma
FROM table3 GROUP BY DATE(user_saving_day)) a1) FROM test /*可修改为任意表,DUAL不支持排序*/
ORDER BY newday aSC