使用SQL显示组中的最小计数和最大计数 [英] Use SQL to display count of min and count of max within group
本文介绍了使用SQL显示组中的最小计数和最大计数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在寻找一种方法来计算等于组的最小值或最大值的项目数.我每隔几天收到一次商品,每两周输入一次.我需要查看每个EntryDate的最小值和最大值ReceivedDate,以及最小值和最大值处的项目计数.在MS Access中使用SQL.
I am looking for a way to count the number of items that are equal to the min or max of a group. I have items that are received every few days and are entered bi-weekly. I need to see the Min and Max ReceivedDate for each EntryDate, and the Count of items at the Min and at the Max. Using SQL in MS Access.
tblItem
| EntryDate | ReceivedDate |
--------------------------------------
| 01/01/2016 | 16/12/2015 |
| 01/01/2016 | 15/12/2015 |
| 01/01/2016 | 10/12/2015 |
| 01/01/2016 | 10/12/2015 |
| 01/01/2016 | 10/12/2015 |
| 01/01/2016 | 10/12/2015 |
| 15/01/2016 | 05/01/2016 |
| 15/01/2016 | 05/01/2016 |
| 15/01/2016 | 04/01/2016 |
| 15/01/2016 | 03/01/2016 |
| 15/01/2016 | 03/01/2016 |
| 15/01/2016 | 03/01/2016 |
当前查询
SELECT tblItem.EntryDate,
Min(tblItem.ReceivedDate) AS MinReceivedDate,
Max(tblItem.ReceivedDate) AS MaxReceivedDate
FROM tblItem
GROUP BY tblItem.EntryDate;
电流输出
Query1
| EntryDate | MinReceivedDate | MaxReceivedDate |
--------------------------------------------------
| 01/01/2016 | 10/12/2015 | 16/12/2015 |
| 15/01/2016 | 03/01/2016 | 05/01/2016 |
所需的输出
Query1
| EntryDate | MinReceivedDate | CountOfMin | MaxReceivedDate | CountOfMax |
---------------------------------------------------------------------------
| 01/01/2016 | 10/12/2015 | 4 | 16/12/2015 | 1 |
| 15/01/2016 | 03/01/2016 | 3 | 05/01/2016 | 2 |
推荐答案
我不知道MS Access是否允许这样的子查询.如果这不起作用,请让我知道,我将删除答案.否则,则:
I don't know if MS Access allows subqueries like this. If this doesn't work, please let me know and I'll delete the answer. Otherwise though:
SELECT
SQ.EntryDate,
SQ.MinReceivedDate,
SUM(IIF(I.ReceivedDate = SQ.MinReceivedDate, 1, 0)) AS CountOfMin,
SQ.MaxReceivedDate,
SUM(IIF(I.ReceivedDate = SQ.MaxReceivedDate, 1, 0)) AS CountOfMax
FROM (
SELECT
SQI.EntryDate,
MIN(SQI.ReceivedDate) AS MinReceivedDate,
MAX(SQI.ReceivedDate) AS MaxReceivedDate
FROM
tblItem SQI
GROUP BY
SQI.EntryDate
) SQ
INNER JOIN tblItem I ON I.EntryDate = SQ.EntryDate
GROUP BY
SQ.EntryDate,
SQ.MinReceivedDate,
SQ.MaxReceivedDate
这篇关于使用SQL显示组中的最小计数和最大计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文