<body>
能一次性用数据库查询出来吗?
回复内容：
<body>
能一次性用数据库查询出来吗?
我很在意！！！！
1. first step
<code>SELECT t.* FROM (SELECT t2.`etime` AS `日期`, SUM(CASE WHEN t2.`epid` = t1_1.`pid` THEN t2.`ecost` ELSE 0 END) AS `A`, SUM(CASE WHEN t2.`epid` = t1_2.`pid` THEN t2.`ecost` ELSE 0 END) AS `B`, SUM(CASE WHEN t2.`epid` = t1_3.`pid` THEN t2.`ecost` ELSE 0 END) AS `C`, SUM(t2.`ecost`) AS `总计`FROM table2 t2LEFT JOIN table1 t1_1 ON t1_1.`pid` = t2.`epid` AND t1_1.`pid` = 1LEFT JOIN table1 t1_2 ON t1_2.`pid` = t2.`epid` AND t1_2.`pid` = 2LEFT JOIN table1 t1_3 ON t1_3.`pid` = t2.`epid` AND t1_3.`pid` = 3GROUP BY t2.`etime`) tORDER BY t.`日期` DESC</code>
output:
<code>+------------+----+----+----+------+| 日期 | A | B | C | 总计 |+------------+----+----+----+------+| 2015-08-04 | 88 | 44 | 0 | 132 || 2015-08-03 | 88 | 77 | 66 | 231 |+------------+----+----+----+------+</code>
- second step
<code>SELECT IFNULL(tt.`日期`, '总计') AS `日期`, SUM(tt.`A`) AS `A`, SUM(tt.`B`) AS `B`, SUM(tt.`C`) AS `C`,SUM(tt.`总计`) AS `总计` FROM ( SELECT t.* FROM ( SELECT t2.`etime` AS `日期`, SUM(CASE WHEN t2.`epid` = t1_1.`pid` THEN t2.`ecost` ELSE 0 END) AS `A`, SUM(CASE WHEN t2.`epid` = t1_2.`pid` THEN t2.`ecost` ELSE 0 END) AS `B`, SUM(CASE WHEN t2.`epid` = t1_3.`pid` THEN t2.`ecost` ELSE 0 END) AS `C`, SUM(t2.`ecost`) AS `总计` FROM table2 t2 LEFT JOIN table1 t1_1 ON t1_1.`pid` = t2.`epid` AND t1_1.`pid` = 1 LEFT JOIN table1 t1_2 ON t1_2.`pid` = t2.`epid` AND t1_2.`pid` = 2 LEFT JOIN table1 t1_3 ON t1_3.`pid` = t2.`epid` AND t1_3.`pid` = 3 GROUP BY t2.`etime` ) t ORDER BY t.`日期` DESC) ttGROUP BY tt.`日期` WITH ROLLUP</code>
output:
<code>+------------+-----+-----+----+------+| 日期 | A | B | C | 总计 |+------------+-----+-----+----+------+| 2015-08-03 | 88 | 77 | 66 | 231 || 2015-08-04 | 88 | 44 | 0 | 132 || 总计 | 176 | 121 | 66 | 363 |+------------+-----+-----+----+------+</code>
附原始数据表：
<code>mysql> select * from table1;+-----+-------+| pid | pname |+-----+-------+| 1 | A || 2 | B || 3 | C |+-----+-------+mysql> select * from table2;+-----+------------+------+-------+| eid | etime | epid | ecost |+-----+------------+------+-------+| 1 | 2015-08-03 | 1 | 88 || 2 | 2015-08-03 | 2 | 77 || 3 | 2015-08-03 | 3 | 66 || 4 | 2015-08-04 | 1 | 55 || 5 | 2015-08-04 | 2 | 44 || 6 | 2015-08-04 | 1 | 33 |+-----+------------+------+-------+6 rows in set</code>
总感觉数据哪里有问题，是题主算错了，还是我算错了。
要做转置，处理起来相当的麻烦，基本思路是先用一个SQL查出列（A、B、C等），再循环，生成另一个SQL，再执行生成的SQL得到结果……太麻烦不写了，自己百度搜转置
简单的方法就是用一般的SQL统计查询，然后再在程序里来拼出你想要的表。
表1为主表 左联接加入表2
SELECT b.etime,a.pname,SUM(b.ecost) as sum_cost FROM table1 as a,table2 as b WHERE a.pid=b.epid GROUP BY b.etime,b.epid
执行后结果如图：
拿回来自己再算总计。
楼主的图画得不错，差点把我绕进去了…
select * from ( select * from table2 left join table1 on table2.epid =table1.pid order by eid asc,etime asc) as sumTable group by etime,eid;
得
panme etime ecost
A 08-04 x
A 08-05 x
B 08-04 x
B 08-05 x
好了，到了这步，楼主不会用php转成那种格式吗？要用sql也可以，不过也是需要一样的逻辑，PHP难道不比sql好使吗？PHP是世界上最好的语言喔
SELECT etime,
搞代gaodaima码SUM(IF(epid=1,ecost,0)) AS A,
SUM(IF(epid=2,ecost,0)) AS B,
SUM(IF(epid=3,ecost,0)) AS C, SUM(ecost) AS Total
FROM mytable GROUP BY etime;
没测试。
转载请注明原文链接：PHP mysql 查询语句
