使用子查询（MySQL）时分组的替代方案？

发布于 2024-12-14 03:58:27 字数 790 浏览 0 评论 0原文

我正在尝试创建一个相对复杂的 MySQL 查询来生成堆积条形图 - 按月、按部门的请求计数。

我一直尝试使用的方法是通过子查询——但这似乎是一个死胡同，因为按月分组不适用于子查询。它对返回的每一行重复相同的总计数，例如，

MONTH     HR    ACCT  CUST SVC
October   5     1     5
November  5     1     5
December  5     1     5

这是我正在使用的查询：

SELECT 
MONTH(request.submissiondate), YEAR(request.submissiondate),
(SELECT count(*) from request where request.DEPARTMENTID='1') as 'Human Resources',
(SELECT count(*) from request where request.DEPARTMENTID='2') as 'Accounting',
(SELECT count(*) from request where request.DEPARTMENTID='3') as 'Customer Service'

FROM request 
WHERE YEAR(request.submissiondate) = 2011
group by MONTH(request.submissiondate), YEAR(request.submissiondate)

希望有一个我只是缺少的相对简单的替代方法。

预先感谢您的帮助！

原文

I'm trying to create a relatively complex MySQL query to generate a stacked bar chart - count of requests by month, by department.

The approach I've been trying to use is via sub-query -- but it seems to be a dead end since the grouping by month doesn't apply in the subquery. It repeats the same total count for each row returned, e.g.

MONTH     HR    ACCT  CUST SVC
October   5     1     5
November  5     1     5
December  5     1     5

Here is the query I'm using:

SELECT 
MONTH(request.submissiondate), YEAR(request.submissiondate),
(SELECT count(*) from request where request.DEPARTMENTID='1') as 'Human Resources',
(SELECT count(*) from request where request.DEPARTMENTID='2') as 'Accounting',
(SELECT count(*) from request where request.DEPARTMENTID='3') as 'Customer Service'

FROM request 
WHERE YEAR(request.submissiondate) = 2011
group by MONTH(request.submissiondate), YEAR(request.submissiondate)

Hoping there's a relatively simple alternative approach that I'm just missing.

Thanks in advance for your help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

第七度阳光i 2024-12-21 03:58:27

也许这样的东西会起作用？

SELECT 
MONTH(request.submissiondate), YEAR(request.submissiondate), request.DEPARTMENTID, COUNT(*) as 'total'
FROM request 
WHERE YEAR(request.submissiondate) = 2011
group by MONTH(request.submissiondate), YEAR(request.submissiondate), request.DEPARTMENTID
ORDER BY request.submissiondate

我不知道是否可以准确地获得上面列出的输出，但是您应该能够循环返回的行并使用您使用的任何服务器端语言重新排列输出。

Maybe something like this would work?

SELECT 
MONTH(request.submissiondate), YEAR(request.submissiondate), request.DEPARTMENTID, COUNT(*) as 'total'
FROM request 
WHERE YEAR(request.submissiondate) = 2011
group by MONTH(request.submissiondate), YEAR(request.submissiondate), request.DEPARTMENTID
ORDER BY request.submissiondate

I don't know if it's possible to get exactly the output as you listed above, but you should be able to loop through the returned rows and rearrange the output with whatever server-side lamguage you are using.

回复收藏 0 原文

~没有更多了~