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

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