狡猾的头衔,我知道。
我知道我可以很轻松地在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/