主页 / coding / 问题 / 79422705
Accepted
Eliz A Purves
Asked: 2025-02-08 13:57:03 +0800 CST

如何向联合查询添加参数

  • 772

我在这里找到了以下查询，发现它很有用。使查询适用于我的数据库后，我现在想向其中添加一个参数以过滤掉不需要的记录 - 该参数所需的表名为 Season，与成员表的联接为 seasons.idseasons = members.members id no，参数为 WHERE (((seasons.Season)=[enter 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 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;

不确定参数应该放在代码中的什么位置。你能帮忙吗？

ms-access

1 个回答

  1. Best Answer

    尝试一下：

    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 members.memberid = Seasons.idseasons
    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 members.memberid = Seasons.idseasons
    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 members.memberid = Seasons.idseasons
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;
    • 0

相关问题