代码之家 › 专栏 › 技术社区 › Watki02

特定VB Linq查询帮助

linq-to-sql linq vb.net

Watki02 · 技术社区 · 16 年前

我需要: 连接3个表(共2个数据库)。让我们给他们打电话表1 表2 表3

表1在“组织”上连接到表2

过滤器(其中):

表3.1成功=正确

表3.appcode=“neo”

开始日期(我可以作为变量传入的日期)

结束日期(我可以作为变量传入的日期)

分组:(我认为分组是你应该怎么做?)

表格合并表必须仅为每个“emplid”分组选择最早(最远的过去)“stamp”日期字段(“stamp”最初出现在表3上,“emplid”最初出现在表3和表1上)

选择:

这是我最后要做的一件事,它可能会让你知道我在做什么:

query = (From j In db.table1s _
Join a In db.table3s _
On j.EMPLID Equals a.emplid _
Join d In db.table2s _
On d.ORG Equals j.Org _
Where a.appcode = "neo" _
Where a.success = True _
Select a.appcode, j.effdt, a.stamp, j.EMPLID, _
d.ORGANIZATION_DESCRIPTION, d.DEPARTMENT_DESCRIPTION, d.VP_DESCRIPTION, _
a.success _
Distinct).AsQueryable().AsEnumerable()

如果你能让我走到这一步,我会相信你的。但是如果你也能做下一件事,在我给你信用之前,我会给你你

然后根据该结果,我需要返回每个“组织”的唯一“emplid”计数。所以最终的结果会是:

   org    |     count    |   (other columns)
------------------------------------------------
 12345    |     15       |       etc...
 54321    |     7        |       etc...

2 回复 | 直到 16 年前

dahlbyk 16 年前

目前还不清楚您的各种EmplId分组要求与员工计数的关系,但这应该让您开始:

query = From j In db.table1s _
        Join a In db.table3s On j.EMPLID Equals a.emplid _
        Join d In db.table2s On d.ORG Equals j.Org _
        Where a.appcode = "neo" _
        Where a.success = True _
        Group By j.Org, d.ORGANIZATION_DESCRIPTION Into Count()

VB Group By on MSDN

Watki02 16 年前

我最终使用了这个查询,它可能不是最有效的,但它是有效的。我最大的问题是我不知道如何正确地进行子查询。

query = From final In _
        (From subfinal In _
            (From countdata In _
                (From a In _
                    (From aa In db.table3s _
                    Where aa.appcode = "neo" _
                    Where aa.success = True _
                    Group By aa.emplid Into Group _
                    Select emplid = Group.Min(Function(f) f.emplid), _
                           stampMin = Group.Min(Function(o) o.stamp) _
                    ) _
                Join j In _
                    (From jj In db.table1s _
                    Where jj.empl_rcd = 0 _
                    Group By jj.EMPLID Into Group _
                    Select EMPLID = Group.Max(Function(m) m.EMPLID), _
                        EffDate = Group.Max(Function(z) z.effdt) _
                    ) _
                On j.EMPLID Equals a.emplid _
                Join jorg In _
                    (From jj2 In db.table1s _
                    Where jj2.empl_rcd = 0 _
                    Select jj2.EMPLID, jj2.Org, jj2.effdt _
                    ) _
                On j.EMPLID Equals jorg.EMPLID _
                And j.EffDate Equals jorg.effdt _
                Select j.EMPLID, a.stampMin, j.EffDate, jorg.Org _
                Distinct) _
            Where countdata.stampMin < EndDate _
            And countdata.stampMin > StartDate _
            Group By countdata.Org Into Group _
            Select count = Group.Count, Org = Org _
            ) _
        Select subfinal.Org, subfinal.count _
        ) _
        Join d In _
            (From dd In db.table2s _
            Select dd.ORG, dd.ORGANIZATION_DESCRIPTION, dd.DEPARTMENT_DESCRIPTION, _
                    dd.VP_COLLEGE_DESCRIPTION _
            ) _
        On final.Org Equals d.ORG _
    Select final.count, final.Org, Org_desc = d.ORGANIZATION_DESCRIPTION, _
    Dept_descr = d.DEPARTMENT_DESCRIPTION, Coll_descr = d.VP_COLLEGE_DESCRIPTION