三表联取
# 表的结构 `table1`
#
CREATE TABLE `table1` (
`user_login_name` varchar(10) NOT NULL,
`user_charge` varchar(5) NOT NULL,
`check_end_date` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `table1` (`user_login_name`, `user_charge`, `check_end_date`) VALUES ('a', '2', '2010-11-01 12:00:00'),
('b', '2', '2010-11-01 11:00:00'),
('c', '3', '2010-11-01 09:00:00'),
('d', '2', '2010-11-02 08:00:00'),
('e', '2', '2010-11-02 00:00:00');
# --------------------
#
# 表的结构 `table2`
#
CREATE TABLE `table2` (
`user_login_name` varchar(20) NOT NULL,
`user_login_time` datetime DEFAULT '0000-00-00 00:00:00',
`user_charge` varchar(5) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
#
# 导出表中的数据 `table2`
#
INSERT INTO `user_login` (`user_login_name`, `user_login_time`, `user_charge`) VALUES ('a', '2010-11-01 23:00:00', '1'),
('b', '2010-11-02 11:00:00', '2');
# --------------------
#
# 表的结构 `table3`
#
CREATE TABLE `table3` (
`user_saving_num` varchar(5) NOT NULL,
`user_saving_day` date NOT NULL,
`user_login_name` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
#
# 导出表中的数据 `table3`
#
INSERT INTO `user_saving` (`user_saving_num`, `user_saving_day`, `user_login_name`) VALUES ('2', '2010-11-01', 'a'),
('3', '2010-11-02', 'c'),
('10', '2010-11-01', 'b');
select sum(t.k),t.day,sum(c.num), c.d from (
Select sum(user_charge) as k,date(check_end_date) as day from table1 group by date(check_end_date)
Union all
Select sum(user_charge) as k,date(user_login_time) as day from table2 group by date(user_login_time)
) t,
(select sum(user_saving_num) as num, date(user_saving_day) as d from table3 group by date(user_saving_day) desc) c
group by t.day,c.d desc;
请看下这条语句哪错了，执行上边语句的结果是：
+----------+------------+------------+------------+
| sum(t.k) | day | sum(c.num) | d |
+----------+------------+------------+------------+
| 8 | 2010-11-01 | 6 | 2010-11-02 |
| 8 | 2010-11-01 | 24 | 2010-11-01 |
| 6 | 2010-11-02 | 6 | 2010-11-02 |
| 6 | 2010-11-02 | 24 | 2010-11-01 |
+----------+------------+------------+------------+
4 rows in set (0.01 sec)
正确的应该是如下：
+----------+------------+------------+------------+
| sum(t.k) | day | sum(c.num) | d |
+----------+------------+------------+------------+
| 6 | 2010-11-02 | 3 | 2010-11-02 |
| 8 | 2010-11-01 | 12 | 2010-11-01 |
+----------+------------+------------+------------+
[解决办法]
SELECT * FROM (
SELECT newday,SUM(newje) 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
) a1
LEFT 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
[解决办法]
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
) a1
LEFT 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
