如何实现获取与含count函数的Query1中MemberID匹配的Query2数据？

原有查询说明 #

Query1：筛选交易次数大于1的会员ID及交易次数 #

SELECT
    memberid,
    count(*) count
From
    dbo.Transactions
group by
    dbo.Transactions.MemberID
having
    count(memberid) > 1

该查询从dbo.Transactions表筛选出交易次数大于1的MemberID及对应交易次数。

Query2：关联多表获取会员交易日志数据 #

SELECT
    transactionlog.id,
    transactionlog.transactionid,
    transactionlog.transactionamount,
    transactionlog.transactiondate,
    transactions.MemberID,
    GymMember.FirstName
from
    dbo.GymMember
    inner join Transactions on
        GymMember.MemberID = Transactions.MemberId
    inner join TransactionLog on
        Transactions.Id = TransactionLog.TransactionId

该查询通过多表关联获取会员的交易日志等相关数据。

实现需求的查询方案 #

要获取Query2中与Query1返回的MemberID一致的数据，即仅保留交易次数大于1的会员的交易日志，可通过以下两种方式实现：

方法1：使用IN子查询 #

在Query2的WHERE子句中嵌入Query1作为子查询，直接过滤符合条件的MemberID：

SELECT
    transactionlog.id,
    transactionlog.transactionid,
    transactionlog.transactionamount,
    transactionlog.transactiondate,
    transactions.MemberID,
    GymMember.FirstName
from
    dbo.GymMember
    inner join Transactions on
        GymMember.MemberID = Transactions.MemberId
    inner join TransactionLog on
        Transactions.Id = TransactionLog.TransactionId
WHERE
    transactions.MemberID IN (
        SELECT
            memberid
        From
            dbo.Transactions
        group by
            dbo.Transactions.MemberID
        having
            count(memberid) > 1
    )

方法2：使用JOIN关联子查询 #

将Query1的结果作为临时表，与Query2的多表关联结果进行JOIN筛选：

SELECT
    tl.id,
    tl.transactionid,
    tl.transactionamount,
    tl.transactiondate,
    t.MemberID,
    gm.FirstName
from
    dbo.GymMember gm
    inner join Transactions t on gm.MemberID = t.MemberId
    inner join TransactionLog tl on t.Id = tl.TransactionId
    inner join (
        SELECT
            memberid
        From
            dbo.Transactions
        group by
            dbo.Transactions.MemberID
        having
            count(memberid) > 1
    ) filtered_members on t.MemberID = filtered_members.memberid

注：这里对表名做了别名简化，让SQL语句更简洁易读。

内容的提问来源于stack exchange，提问作者Faisal Zahid