英文:
How to see the month with the top number for every year in Microsoft Access SQL?
问题
如何使用Microsoft Access中的SQL找到每年犯罪最多的月份以及该年该月犯罪数量？我尝试了以下查询，希望能得到我想要的结果：
SELECT COUNT() AS CRIME_COUNT, CRIME_MONTH, CRIME_YEAR
FROM CRIMEDATA
GROUP BY CRIME_YEAR, CRIME_MONTH
ORDER BY COUNT() DESC
但是它提示我为[distinct crime_month]输入参数值，所以我只输入了1，然后我得到了一个按犯罪数量降序排列的表格，显示了对应的月份和年份，但是它显示了每年的每个月份。即使不使用[distinct crime_month]，我得到的结果也是一样的。我该如何得到我想要的结果？
英文:
How do I find the month with the most crime in every year and the number only of the crimes in that month of that year using SQL in Microsoft Access?
I tried this, hoping it would give me the result I wanted.
SELECT COUNT(*) AS CRIME_COUNT, CRIME_MONTH, CRIME_YEAR
FROM CRIMEDATA
GROUP BY CRIME_YEAR, [DISTINCT CRIME_MONTH], CRIME_MONTH
ORDER BY COUNT(*) DESC
It prompted me to enter a parameter value for [distinct crime_month], so I just entered 1, and I got a table with the rows ordered by descending crime counts and the corresponding months and years, but it showed every month in every year. I get the same result not using [distinct crime_month]. How can I get the result I'm looking for?
答案1
得分: 2
考虑以下内容：
构建聚合查询，并将该查询用作 TOP N 的数据源。
查询1
SELECT COUNT(*) AS CRIME_COUNT, CRIME_MONTH, CRIME_YEAR
FROM CRIMEDATA
GROUP BY CRIME_YEAR, CRIME_MONTH;
查询2
SELECT Query1.* FROM Query1 WHERE CRIME_MONTH IN(
SELECT TOP 1 CRIME_MONTH FROM Query1 AS Dupe
WHERE Dupe.CRIME_YEAR = Query1.CRIME_YEAR
ORDER BY Dupe.CRIME_COUNT DESC)
ORDER BY CRIME_YEAR, CRIME_MONTH;
或者查询2的另一种替代方法
SELECT Query1.CRIME_YEAR, Query1.CRIME_MONTH, Query1.CRIME_COUNT
FROM Query1 INNER JOIN (
SELECT Max(CRIME_COUNT) AS MCC, CRIME_YEAR FROM Query1
GROUP BY CRIME_YEAR) AS Q2
ON Query1.CRIME_YEAR = Q2.CRIME_YEAR AND Query1.CRIME_COUNT = Q2.MCC;
请注意，这两种方法都无法消除并列数据。在一年中具有相同最高计数的月份将被检索出来。
英文:
Consider:
Build aggregate query and use that query as source for TOP N.
Query1
SELECT COUNT(*) AS CRIME_COUNT, CRIME_MONTH, CRIME_YEAR
FROM CRIMEDATA
GROUP BY CRIME_YEAR, CRIME_MONTH;
Query2
SELECT Query1.* FROM Query1 WHERE CRIME_MONTH IN(
SELECT TOP 1 CRIME_MONTH FROM Query1 AS Dupe
WHERE Dupe.CRIME_YEAR = Query1.CRIME_YEAR
ORDER BY Dupe.CRIME_COUNT DESC)
ORDER BY CRIME_YEAR, CRIME_MONTH;
Or this alternative for Query2
SELECT Query1.CRIME_YEAR, Query1.CRIME_MONTH, Query1.CRIME_COUNT
FROM Query1 INNER JOIN (
SELECT Max(CRIME_COUNT) AS MCC, CRIME_YEAR FROM Query1
GROUP BY CRIME_YEAR) AS Q2
ON Query1.CRIME_YEAR = Q2.CRIME_YEAR AND Query1.CRIME_COUNT = Q2.MCC;
Be aware neither will eliminate tied data. Months with same highest count in a year will retrieve.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论