mysql - 按月获取记录-MySQL

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 |

以上数据清楚你，我的问题是什么，但如果你仍然不明白我的问题请随时问我。
另外，我想要一个正确的方式来显示结果。这是可行的，但没有给出正确的结果。

最佳答案

你可以这样吃
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的建议创建了此查询
完全需要：

关于mysql - 按月获取记录-MySQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24300968/