如何为UNION查询添加Season参数实现数据过滤

用户问题 #

我找到以下查询并认为其很有用。在适配我的数据库后，我希望为其添加一个参数以过滤不需要的记录。所需关联的表为Season，与members表的关联条件为seasons.idseasons = members.members id no，过滤条件为WHERE (((seasons.Season)=[enter season]))。不清楚该参数应添加到代码的哪个位置，能否提供帮助？

原SQL查询 #

Select  (tMin.Initial1 + ' and ' + tMax.Initial1) As initial, tMax.surname1 As surname, tMax.[first line address], tMax.[second line address], tMax.town, tMax.postcode, tMax.GroupCount 
From (
    Select Distinct Max(initial) As initial1, Max(surname) As surname1,[first line address], [second line address], town, postcode, Count() As  GroupCount 
    From members 
    Group By [first line address], [second line address], town, postcode 
    Having Count() = 2
) As tMax 
Inner Join (
    Select Distinct Min(initial) As initial1, Min(surname) As surname1, [first line address],[second line address], town, postcode, Count(*) As GroupCount
    From members
    Group By [first line address], [second line address], town, postcode
    Having Count(*) = 2
) As tMin
On (tMax.[first line address] = tMin.[first line address]) And 
   (IIf(IsNull(tMax.[second line address]), '', tMax.[second line address]) = IIf(IsNull(tMin.[second line address]), '', tMin.[second line address])) And 
   (tMax.town = tMin.town) And  
   (tMax.postcode = tMin.postcode)

UNION ALL 

Select Max(initial) As initial1, Max(surname) As surname1, [first line address], [second line address], town, postcode, Count() As GroupCount 
From members 
Group By [first line address], [second line address], town, postcode 
Having Count() = 1 Or Count(*) > 2

ORDER BY surname, initial, town, postcode;

修改后的SQL查询 #

你需要在每个基于members表的子查询中关联Season表并添加过滤条件，确保只返回指定赛季的记录，修改后的代码如下：

Select  (tMin.Initial1 + ' and ' + tMax.Initial1) As initial, tMax.surname1 As surname, tMax.[first line address], tMax.[second line address], tMax.town, tMax.postcode, tMax.GroupCount 
From (
    Select Distinct Max(initial) As initial1, Max(surname) As surname1,[first line address], [second line address], town, postcode, Count() As  GroupCount 
    From members 
    INNER JOIN seasons ON seasons.idseasons = members.[members id no]
    WHERE seasons.Season = [enter season]
    Group By [first line address], [second line address], town, postcode 
    Having Count() = 2
) As tMax 
Inner Join (
    Select Distinct Min(initial) As initial1, Min(surname) As surname1, [first line address],[second line address], town, postcode, Count(*) As GroupCount
    From members
    INNER JOIN seasons ON seasons.idseasons = members.[members id no]
    WHERE seasons.Season = [enter season]
    Group By [first line address], [second line address], town, postcode
    Having Count(*) = 2
) As tMin
On (tMax.[first line address] = tMin.[first line address]) And 
   (IIf(IsNull(tMax.[second line address]), '', tMax.[second line address]) = IIf(IsNull(tMin.[second line address]), '', tMin.[second line address])) And 
   (tMax.town = tMin.town) And  
   (tMax.postcode = tMin.postcode)

UNION ALL 

Select Max(initial) As initial1, Max(surname) As surname1, [first line address], [second line address], town, postcode, Count() As GroupCount 
From members 
INNER JOIN seasons ON seasons.idseasons = members.[members id no]
WHERE seasons.Season = [enter season]
Group By [first line address], [second line address], town, postcode 
Having Count() = 1 Or Count(*) > 2

ORDER BY surname, initial, town, postcode;

修改说明 #

• 三个基于members表的子查询（tMax、tMin、UNION ALL后的查询）都添加了INNER JOIN seasons ON seasons.idseasons = members.[members id no]来关联赛季表
• 每个子查询都新增了WHERE seasons.Season = [enter season]过滤条件，确保只获取指定赛季的会员记录
• 保持了原查询的聚合、关联逻辑和排序规则不变

内容的提问来源于stack exchange，提问作者Eliz A Purves