SQL MS Access查询的排除条件 [英] Exclusion Condition for SQL MS Access Query
问题描述
要求:生成一个查询，该查询根据用户输入的StartDate，EndDate和Upper Data Threshold输入基于电话号码的数据.
Requirement: Generate a query that outputs data based on phone numbers in response to user inputs being StartDate, EndDate and Upper Data Threshold.
这些输入变量是查询的界限.因此，SELECT语句相应地写在下面.
These input variables are bounds for the query. As such, the SELECT statement was written below accordingly.
唯一的警告是，如果一个电话号码的单个记录超过了上限数据阈值"，那么与该违规电话号码相关联的所有电话号码记录都不应输出，无论该相同电话号码的其他记录是否如此. t违反数据阈值.下面是示例输入和预期输出:
The only caveat is that if a phone number has a single record that exceeds the Upper Data Threshold, then ALL phone number records associated to that violating phone number should not be outputted regardless of other records of that same phone number that don't violate the data threshold. Below is sample input and expected output:
User input Start Date: 1/15/2015
User input End Date: 11/15/2015
User input Upper Data Threshold in kB: 50
[Master] Table in Access:
Invc Date Mobile Nbr PktDtVol
--------- ---------- --------
1/15/15 647-409-8206 48kB
2/15/15 647-409-8206 33kB
3/15/15 647-409-8206 8000kB
4/15/15 647-409-8206 20kB
5/15/15 647-409-8206 10kB
6/15/15 647-409-8206 0kB
7/15/15 718-500-2311 3kB
8/15/15 718-500-2311 45kB
9/15/15 718-500-2311 25kB
10/15/15 514-300-3311 33kB
11/15/15 514-300-3311 20kB
[Temp_Table]中的预期输出:
Expected Output in [Temp_Table]:
Invc Date Mobile Nbr PktDtVol Difference in Days
--------- ---------- -------- -------------------
7/15/15 718-500-2311 3kB 304
8/15/15 718-500-2311 45kB 304
9/15/15 718-500-2311 25kB 304
10/15/15 514-300-3311 33kB 304
11/15/15 514-300-3311 20kB 304
我当前的解决方案:
PARAMETERS [Start Date] DateTime, [End Date] DateTime, [Upper Bound
Usage in KB] IEEEDouble;
SELECT [Master].[Invc Date], [Master].PktDtVol, [Master].[Mobile Nbr],
DateDiff("d",[Start Date],[End Date]) AS [Difference in days] INTO
Temp_Table
FROM [Master]
WHERE ((([Master].[Invc Date]) >= [Start Date] And
([Master].[Invc Date])<=[End Date]) AND
(([Master].PktDtVol)<= [Upper Bound Usage in KB]));
在输出中不希望有647-409-8206记录，因为只用了1条记录就超过了50kB(是第3条记录)，因此将相应地省略所有647-409-8206条记录.
The 647-409-8206 record is not expected in the output since it took only 1 record to surpass the 50kB (being the 3rd record), then ALL 647-409-8206 records would be omitted accordingly.
请多多帮助！谢谢！
推荐答案
首先编写一个子查询，选择一行违反阈值的所有(不同的)移动电话号码.然后从表中选择所有行WHERE [Mobile Nbr] NOT IN (subquery).
First write a subquery selecting all (distinct) mobile numbers for which a row violates the threshold. Afterwards select from the table all rows WHERE [Mobile Nbr] NOT IN (subquery).
这篇关于SQL MS Access查询的排除条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
