LINQ，Join，GroupBy和计数

我举了一个似乎正常工作的例子。

Public Class T1
    Public Property CAMPAIGNID As Integer
    Public Property TERMCD As String
End Class

Public Class T2
    Public Property CAMPAIGNID As Integer
    Public Property CALLS As Integer
End Class

Dim tbl1 As New List(Of T1) From {
    New T1 With {.CAMPAIGNID = 1, .TERMCD = "Refused"},
    New T1 With {.CAMPAIGNID = 1, .TERMCD = "Accepted"},
    New T1 With {.CAMPAIGNID = 2, .TERMCD = "Refused"},
    New T1 With {.CAMPAIGNID = 2, .TERMCD = "Accepted"},
    New T1 With {.CAMPAIGNID = 3, .TERMCD = "Refused"},
    New T1 With {.CAMPAIGNID = 3, .TERMCD = "Accepted"}
}
Dim tbl2 As New List(Of T2) From {
    New T2 With {.CAMPAIGNID = 1, .CALLS = 2},
    New T2 With {.CAMPAIGNID = 1, .CALLS = 4},
    New T2 With {.CAMPAIGNID = 2, .CALLS = 8},
    New T2 With {.CAMPAIGNID = 2, .CALLS = 16},
    New T2 With {.CAMPAIGNID = 3, .CALLS = 32},
    New T2 With {.CAMPAIGNID = 3, .CALLS = 64}
}

Dim query =
    From t1 In tbl1
    Join t2 In tbl2 On t1.CAMPAIGNID Equals t2.CAMPAIGNID
    Group By t1.CAMPAIGNID Into Group
    Select New With {
        .id = CAMPAIGNID,
        .CALLS = Group.Sum(Function(a) a.t2.CALLS),
        .count = Group.Count(Function(a) a.t1.TERMCD = "Refused")
    }

For Each q In query
    Console.WriteLine($"ID:{q.id}, CALLS:{q.CALLS}, COUNT:{q.count}")
Next

ID：1，CALLS：12，COUNT：2
ID：2，CALLS：48，COUNT：2
ID：3，CALLS：192，COUNT：2

在ID 3的情况下，CALLS的总和为192是正确的，因为该总和没有对的约束TERMCD = "Refused"，因此将全部计数。并且2的计数是准确的，因为其中存在约束TERMCD = "Refused"。

仅查看组，不进行汇总，让我们看看返回的结果

Dim query =
    From t1 In tbl1
    Join t2 In tbl2 On t1.CAMPAIGNID Equals t2.CAMPAIGNID
    Group By t1.CAMPAIGNID Into Group

For Each q In query
    Console.WriteLine($"ID:{q.CAMPAIGNID}")
    For Each g In q.Group
        Console.WriteLine($"t1.CAMPAIGNID:{g.t1.CAMPAIGNID}, t1.TERMCD:{g.t1.TERMCD}, t2.CAMPAIGNID:{g.t2.CAMPAIGNID}, t2.CALLS:{g.t2.CALLS}")
    Next
Next

ID：1
t1.CAMPAIGNID：1，t1.TERMCD：Refused，t2.CAMPAIGNID：1，t2.CALLS：2
t1.CAMPAIGNID：1，t1.TERMCD：Refused，t2.CAMPAIGNID：1，t2.CALLS：4
t1 .CAMPAIGNID：1，t1.TERMCD：Accepted，t2.CAMPAIGNID：1，t2.CALLS：2
t1.CAMPAIGNID：1，t1.TERMCD：Accepted，t2.CAMPAIGNID：1，t2.CALLS：4
ID：2 ID：2
t1。 CAMPAIGNID：2，t1.TERMCD：Refused，t2.CAMPAIGNID：2，t2.CALLS：8
t1.CAMPAIGNID：2，t1.TERMCD：Refused，t2.CAMPAIGNID：2，t2.CALLS：16
t1.CAMPAIGNID：2， t1.TERMCD：已接受，t2.CAMPAIGNID：2，t2.CALLS：8
t1.CAMPAIGNID：2，t1.TERMCD：已接受，t2.CAMPAIGNID：2，t2.CALLS：16
ID：3
t1.CAMPAIGNID：3，t1 .TERMCD：拒绝，t2.CAMPAIGNID：3，t2.CALLS：32
t1.CAMPAIGNID：3，t1.TERMCD：拒绝，t2.CAMPAIGNID：3，t2.CALLS：64
t1.CAMPAIGNID：3，t1.TERMCD：Accepted，t2.CAMPAIGNID：3，t2.CALLS：32
t1.CAMPAIGNID：3，t1.TERMCD：Accepted，t2.CAMPAIGNID：3，t2.CALLS：64

现在应该很清楚为什么您看到自己看到的东西。

无限制的总和：

t1.CAMPAIGNID：3，t1.TERMCD：Refused，t2.CAMPAIGNID：3，t2.CALLS：32
t1.CAMPAIGNID：3，t1.TERMCD：Refused，t2.CAMPAIGNID：3，t2.CALLS：64
t1.CAMPAIGNID： 3，t1.TERMCD：接受，t2.CAMPAIGNID：3，t2.CALLS：32
t1.CAMPAIGNID：3，t1.TERMCD：接受，t2.CAMPAIGNID：3，t2.CALLS：64

算在哪里 a.t1.TERMCD = "Refused"

t1.CAMPAIGNID：3，t1.TERMCD：Refused，t2.CAMPAIGNID：3，t2.CALLS：32
t1.CAMPAIGNID：3，t1.TERMCD：Refused，t2.CAMPAIGNID：3，t2.CALLS：64

我不知道你有什么想看到的，但至少应该告诉你，你的输出是准确的。

现在我们已经解决了这个问题，您的SQL查询如下

SELECT DISTINCT A.CAMPAIGNID
              , RFJOIN.RF
              , CCJOIN.CC
              , CALLSJOIN.CALLS
              , CALLSJOIN.CALLS / RFJOIN.RF as Calculated 
FROM tbl1 A 
inner join (SELECT CAMPAIGNID
                 , count() as RF 
            FROM tbl1 
            WHERE TERMCD='Refused' 
            GROUP by CAMPAIGNID) RFJOIN on RFJOIN.CAMPAIGNID = a.CAMPAIGNID 
inner join (SELECT CAMPAIGNID
                 , count() as CC 
            FROM tbl1 
            WHERE TERMCD LIKE '%Con%' 
            GROUP by CAMPAIGNID) CCJOIN on CCJOIN.CAMPAIGNID = a.CAMPAIGNID 
inner join (SELECT CAMPAIGNID
                 , SUM(CALLS) as CALLS 
            FROM tbl2 
            GROUP by CAMPAIGNID) CALLSJOIN on CALLSJOIN.CAMPAIGNID = a.CAMPAIGNID

我将其分解为子查询，然后将它们合并在一起。您几乎可以通过这种方式复制SQL语法

Dim RFJ = From t1 In tbl1
          Where t1.TERMCD = "Refused"
          Group By t1.CAMPAIGNID Into Group
          Select New With {CAMPAIGNID, .RF = Group.Count()}
Dim CCJ = From t1 In tbl1
          Where t1.TERMCD.Contains("Con")
          Group By t1.CAMPAIGNID Into Group
          Select New With {CAMPAIGNID, .CC = Group.Count()}
Dim CAJ = From t2 In tbl2
          Group By t2.CAMPAIGNID Into Group
          Select New With {CAMPAIGNID, .CALLS = Group.Count()}
Dim query = From A In tbl1
            Join RFJOIN In RFJ On RFJOIN.CAMPAIGNID Equals A.CAMPAIGNID
            Join CCJOIN In CCJ On CCJOIN.CAMPAIGNID Equals A.CAMPAIGNID
            Join CALLSJOIN In CAJ On CALLSJOIN.CAMPAIGNID Equals A.CAMPAIGNID
            Select New With {A.CAMPAIGNID, RFJOIN.RF, CCJOIN.CC, CALLSJOIN.CALLS, .Calculated = CALLSJOIN.CALLS / RFJOIN.RF}
Dim queryDistinct = query.Distinct()

' or in one big ugly query :)

Dim fullQuery = (From A In tbl1
                 Join RFJOIN In From t1 In tbl1
                                Where t1.TERMCD = "Refused"
                                Group By t1.CAMPAIGNID Into Group
                                Select New With {CAMPAIGNID, .RF = Group.Count()} On RFJOIN.CAMPAIGNID Equals A.CAMPAIGNID
                 Join CCJOIN In From t1 In tbl1
                                Where t1.TERMCD.Contains("Con")
                                Group By t1.CAMPAIGNID Into Group
                                Select New With {CAMPAIGNID, .CC = Group.Count()} On CCJOIN.CAMPAIGNID Equals A.CAMPAIGNID
                 Join CALLSJOIN In From t2 In tbl2
                                   Group By t2.CAMPAIGNID Into Group
                                   Select New With {CAMPAIGNID, .CALLS = Group.Count()} On CALLSJOIN.CAMPAIGNID Equals A.CAMPAIGNID
                 Select New With {A.CAMPAIGNID, RFJOIN.RF, CCJOIN.CC, CALLSJOIN.CALLS, .Calculated = CALLSJOIN.CALLS / RFJOIN.RF}).Distinct()