php mysql语句_PHP mysql 查询语句

能一次性用数据库查询出来吗?

回复内容：

能一次性用数据库查询出来吗?

我很在意！！！！

1. first step

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

output:

+------------+----+----+----+------+

| 日期 | A | B | C | 总计 |

+------------+----+----+----+------+

| 2015-08-04 | 88 | 44 | 0 | 132 |

| 2015-08-03 | 88 | 77 | 66 | 231 |

+------------+----+----+----+------+

second step

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

) tt

GROUP BY tt.`日期` WITH ROLLUP

output:

+------------+-----+-----+----+------+

| 日期 | A | B | C | 总计 |

+------------+-----+-----+----+------+

| 2015-08-03 | 88 | 77 | 66 | 231 |

| 2015-08-04 | 88 | 44 | 0 | 132 |

| 总计 | 176 | 121 | 66 | 363 |

+------------+-----+-----+----+------+

附原始数据表：

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

总感觉数据哪里有问题，是题主算错了，还是我算错了。

要做转置，处理起来相当的麻烦，基本思路是先用一个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,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中文网，转载请注明出处，感谢您的尊重！