mysql - 按月获取记录 - mysql

Question

Fiddle

查询，

SELECT
  IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=1,COUNT(MONTH(FROM_UNIXTIME(p.created_timestamp))),0) AS '1',
  IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=2,COUNT(MONTH(FROM_UNIXTIME(p.created_timestamp))),0) AS '2',
  IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=3,COUNT(MONTH(FROM_UNIXTIME(p.created_timestamp))),0) AS '3',
  IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=4,COUNT(MONTH(FROM_UNIXTIME(p.created_timestamp))),0) AS '4',
  IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=5,COUNT(MONTH(FROM_UNIXTIME(p.created_timestamp))),0) AS '5',
  IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=6,COUNT(MONTH(FROM_UNIXTIME(p.created_timestamp))),0) AS '6',
  IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=7,COUNT(MONTH(FROM_UNIXTIME(p.created_timestamp))),0) AS '7',
  IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=8,COUNT(MONTH(FROM_UNIXTIME(p.created_timestamp))),0) AS '8',
  IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=9,COUNT(MONTH(FROM_UNIXTIME(p.created_timestamp))),0) AS '9',
  IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=10,COUNT(MONTH(FROM_UNIXTIME(p.created_timestamp))),0) AS '10',
  IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=11,COUNT(MONTH(FROM_UNIXTIME(p.created_timestamp))),0) AS '11',
  IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=12,COUNT(MONTH(FROM_UNIXTIME(p.created_timestamp))),0) AS '12'
FROM `tyr_profile_view` p
WHERE p.user_id != '59'
    AND p.`user_id` != 0
    AND YEAR(FROM_UNIXTIME(p.created_timestamp)) = YEAR(CURDATE())

这样录制节目，

上述查询的输出

|  1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|----|---|---|---|---|---|---|---|---|----|----|----|
| 18 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  0 |  0 |  0 | 

以上数据清楚你，我的问题是什么，但如果你仍然不明白我的问题，请随时问我。

附言我想要一种能准确显示结果的正确方法。这是有效的，但没有给出正确的结果。

Answer 1

你可以这样玩

Fiddle

查询

SELECT
    COUNT(IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=1, 1, NULL)) AS '1',
    COUNT(IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=2, 1, NULL)) AS '2',
    COUNT(IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=3, 1, NULL)) AS '3',
    COUNT(IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=4, 1, NULL)) AS '4',
    COUNT(IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=5, 1, NULL)) AS '5',
    COUNT(IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=6, 1, NULL)) AS '6',
    COUNT(IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=7, 1, NULL)) AS '7',
    COUNT(IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=8, 1, NULL)) AS '8',
    COUNT(IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=9, 1, NULL)) AS '9',
    COUNT(IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=10, 1, NULL)) AS '10',
    COUNT(IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=11, 1, NULL)) AS '11',
    COUNT(IF(MONTH(FROM_UNIXTIME(p.created_timestamp))=12, 1, NULL)) AS '12'
FROM `tyr_profile_view` p
WHERE p.user_id != '59'
    AND p.`user_id` != 0
    AND YEAR(FROM_UNIXTIME(p.created_timestamp)) = YEAR(CURDATE())

输出

| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
|---|---|---|---|---|---|---|---|---|----|----|----|
| 1 | 0 | 0 | 3 | 6 | 8 | 0 | 0 | 0 |  0 |  0 |  0 |

注意:根据 Alma Do 建议创建此查询

确切要求: