狡猾的头衔,我知道。

我知道我可以很轻松地在PHP中执行此操作,但想知道是否可以使用我的SQL查询。

我正在制作一个条形图,显示我的网站的新成员。

JAN  |  FEB  |  MAR  |  APR
-----|-------|-------|-------------
101  |  163  |  282  |  233


到目前为止,这是我所做的:

SELECT DATE_FORMAT(created, '%Y') as 'year',
DATE_FORMAT(created, '%b') as 'month',
IFNULL(COUNT(user_id),0) as 'total'
FROM users
WHERE DATE_FORMAT(created, %Y) = 2013
GROUP BY DATE_FORMAT(created, '%Y%m')


但是要制作条形图,我需要知道最高计数是多少。在上面的示例中,我想返回282作为最高计数,因此我可以计算出柱线的百分比。

这有可能吗?我在想,也许我需要一个子查询,但是对性能的最佳方法不太确定。

这是我想返回的内容:

JAN  |  FEB  |  MAR  |  APR  | Highest_Count
-----|-------|-------|-------|--------------
101  |  163  |  282  |  233  | 282

最佳答案

SQL Fiddle

MySQL 5.5.32模式设置:

CREATE TABLE users
    (`user_id` int, `created` date)
;

INSERT INTO users
    (`user_id`, `created`)
VALUES
    (1, '2013-01-01'),
    (2, '2013-01-03'),
    (3, '2013-01-07'),
    (4, '2013-02-01'),
    (5, '2013-02-04'),
    (6, '2013-03-04')
;


查询1:

SELECT DATE_FORMAT(u.created, '%Y') as 'year',
       DATE_FORMAT(u.created, '%b') as 'month',
       IFNULL(COUNT(u.user_id),0) as 'total',
       r2.Highest_Count,
       IFNULL(COUNT(u.user_id),0) / r2.Highest_Count as 'percentage'
FROM users u
INNER JOIN (
    SELECT r1.year, max(r1.Total) as Highest_Count
    FROM (
        SELECT DATE_FORMAT(created, '%Y') as 'year',
        DATE_FORMAT(created, '%b') as 'month',
        IFNULL(COUNT(user_id),0) as 'total'
        FROM users
        WHERE DATE_FORMAT(created, '%Y') = 2013
        GROUP BY DATE_FORMAT(created, '%Y%m')) AS r1
    GROUP BY r1.year) AS r2 ON r2.year = DATE_FORMAT(u.created, '%Y')
WHERE DATE_FORMAT(u.created, '%Y') = 2013
GROUP BY DATE_FORMAT(u.created, '%Y%m')


Results

| YEAR | MONTH | TOTAL | HIGHEST_COUNT | PERCENTAGE |
|------|-------|-------|---------------|------------|
| 2013 |   Jan |     3 |             3 |          1 |
| 2013 |   Feb |     2 |             3 |     0.6667 |
| 2013 |   Mar |     1 |             3 |     0.3333 |

关于mysql - 从每月计数查询中获得最高计数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18668101/

10-12 12:51
查看更多