mysql查询语句在哪里编写

语句在哪里编写"/>

mysql查询语句在哪里编写

纯sql

SELECT a_name,SUM(pg) '苹果',SUM(jz) '橘子',SUM(yz) '柚子' FROM (

SELECT a_id,

CASE b_id WHEN 1 THEN 1 ELSE 0 END as pg,

CASE b_id WHEN 3 THEN 1 ELSE 0 END as jz,

CASE b_id WHEN 5 THEN 1 ELSE 0 END as yz,

DATE_FORMAT(c_time,'%Y-%m') time FROM C WHERE DATE_FORMAT(c_time,'%Y-%m') = '2016-11'

) d LEFT JOIN A ON d.a_id = A.a_id

GROUP BY d.a_id

输出

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

| a_name | 苹果 | 橘子 | 柚子 |

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

| 小明 | 0 | 1 | 0 |

| 小红 | 1 | 0 | 1 |

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

你这样结合PHP/C#/Java 的话,提供思路，数据分组查询出来后

SELECT * FROM C WHERE DATE_FORMAT(c_time,'%Y-%m') = '2016-11'

结果

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

| c_id | a_id | b_id | c_time |

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

| 2 | 1 | 3 | 2016-11-24 |

| 5 | 2 | 5 | 2016-11-21 |

| 7 | 2 | 1 | 2016-11-25 |

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

代码在做处理，循环 这个结果，省略 for(...){if(...){...}} 相同的 a_id 增加到一个Array中 像这样的结构[{aid:1,pg:0,jz:1,yz:0},{aid:2,pg:1,jz:0,yz:1}]