PHPmysql查询语句

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了PHPmysql查询语句，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3422字，纯文字阅读大概需要5分钟。

内容图文

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

回复内容：

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

我很在意！！！！
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  |
+------------+----+----+----+------+

1. 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;

没测试。

内容总结

以上是互联网集市为您收集整理的PHPmysql查询语句全部内容，希望文章能够帮你解决PHPmysql查询语句所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端