mysql - 生成一个按月显示 Android 和 iOs 访问者的表格？数据库

Question

我已经解决了显示月份和 Android 用户的查询的一部分，但我似乎找不到加入 iOS 用户的方法。

这是我试图从查询中创建的表:

/Months/   IOS   /Android
|June     |324    |123  |
|July     |123    |23   |
|August   |521    |342  |
|September|712    |342  |

这就是我得到的

/Months   /  IOS  /
|June     |324    |
|July     |123    |
|August   |521    |
|September|712    |

这是我一直在使用的查询

SELECT MONTHNAME(created_at) AS monthy ,
       COUNT(id) as ios 
FROM visitors 
WHERE MID(mobile_uuid,1,3) = 'ios' 
GROUP BY MONTH(created_at);

我想生成数据来填充显示我的应用程序访问者数量的谷歌图表。

我已经尝试了几次连接，现在我已经搜索了 6 个小时。

Answer 1

试试这个查询

SELECT MONTHNAME(created_at) MONTH,
(SELECT COUNT(mobile_uuid)  FROM VISITORS V2 
WHERE MID(mobile_uuid,1,3)='ios' 
AND MONTHNAME(V2.created_at)=MONTHNAME(V1.created_at)) AS IOS,
(SELECT COUNT(mobile_uuid)  from VISITORS v3 
WHERE MID(mobile_uuid,1,3)='adr' 
AND MONTHNAME(V3.created_at)=MONTHNAME(V1.created_at)) AS ANDROID
FROM VISITORS V1 GROUP BY MONTHNAME(created_at) ORDER BY created_at

完整代码

CREATE TABLE visitors (
  id INT NOT NULL AUTO_INCREMENT,
  mobile_uuid VARCHAR(45) NULL,
  created_at DATETIME NULL,
  PRIMARY KEY (id));

INSERT INTO visitors (mobile_uuid, created_at) VALUES ('adrsdf873', '2016-06-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('adrsdf763', '2016-07-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('adrdg6h84', '2016-08-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('iossdf87g', '2016-03-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('adrety895', '2016-03-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('adrd9g87d', '2016-03-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('iossde88g', '2016-06-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('iossge98g', '2016-07-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('iossue78g', '2016-07-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('adrsref763','2016-07-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('iosscf67g', '2016-08-15 23:27:07');

SELECT MONTHNAME(created_at) MONTH,
(SELECT COUNT(mobile_uuid)  FROM VISITORS V2 
WHERE MID(mobile_uuid,1,3)='ios' 
AND MONTHNAME(V2.created_at)=MONTHNAME(V1.created_at)) AS IOS,
(SELECT COUNT(mobile_uuid)  from VISITORS v3 
WHERE MID(mobile_uuid,1,3)='adr' 
AND MONTHNAME(V3.created_at)=MONTHNAME(V1.created_at)) AS ANDROID
FROM VISITORS V1 GROUP BY MONTHNAME(created_at) ORDER BY created_at

查询对我有用