树视图格式的分组依据

Question

我有一个数据库，我在那里保存贷款的人的数据。贷款可以只由一个人或由一群人进行。我将向您展示我的数据库模式的一部分。

​

​

‘'Acreditados’保持了‘Personas’(人)和‘Creditos’(贷款)之间的关系，因此一个人可以有多个贷款，一个贷款可以由多个人发放。‘'Agrupaciones’保持'acreditados‘(已经有贷款的人)和'grupos’(按组贷款有一个组名)之间的关系。最后，“movimientos”为每个人保留了费用和付款。

这里的问题是，我如何查询我的数据库，以按贷款(creditos)对所有付款(movimientos)进行分组，如果它是个人贷款节目名称，但如果它是组的群组贷款节目名称？

我已经有了以下查询：

SELECT CR.id_credito, SUM(M.monto) AS Monto, SUM(M.interes) AS Interes, SUM(M.iva) AS IVA, SUM(M.capital) AS Capital, M.fecha_mov
FROM movimientos AS M
JOIN cargos AS C ON C.id_movimiento = M.id_movimiento
JOIN acreditados AS A ON A.id_acreditado = M.id_acreditado
JOIN creditos AS CR ON CR.id_credito = A.id_credito
WHERE C.status = 0 
GROUP BY CR.id_credito, M.fecha_mov
ORDER BY M.fecha_mov

使用此查询，我按“creditos”(贷款)和日期对付款进行分组，因为每次付款都有不同的日期。但是，我如何使用'personas'( person )表加入此表，以便获得贷款者的姓名，如果是按组贷款，则显示组的名称？事实上，我不知道是否可以使用传统的查询，这样做的目的是在c#中创建一个三视图，其中组的名称是父节点，每个人都是子节点。个人贷款将是没有孩子的父节点。感谢您的帮助，谢谢！

Answer 1

对于连接可能会重复行的其他表，从而丢弃所有聚合函数，我会非常谨慎。我的意思是：

假设您有两个人附加到贷款，因此您从贷款付款开始，重新加入贷款，然后加入属于该贷款的人员。现在，您需要处理的每笔贷款付款都有2条记录(每个人一条)。

我推荐的做法是使用外部应用程序将字段连接回您需要的位置。如果您从未使用过outer/cross apply，只需将其视为select语句中的一个子查询：

SELECT (SELECT TOP 1 i.someColumn FROM innerTable i WHERE i.ID = t.ID) FROM someTable AS t

唯一不同的是它在连接本身之内。这意味着您可以在FROM子句中使用IN SCOPE子查询来连接回来。事实上，这可能是您最好的选择，以使所有付款仅限于贷款，并在此另一个查询中保留所有个人/组。因此，您的查询将如下所示：

SELECT CR.id_credito, SUM(M.monto) AS Monto, SUM(M.interes) AS Interes, SUM(M.iva) AS IVA, SUM(M.capital) AS Capital, M.fecha_mov, ISNULL(tg.nombre, tp.nombre) Nombre
FROM movimientos AS M
JOIN cargos AS C ON C.id_movimiento = M.id_movimiento
JOIN acreditados AS A ON A.id_acreditado = M.id_acreditado
JOIN creditos AS CR ON CR.id_credito = A.id_credito
OUTER APPLY (SELECT TOP 1 G.nombre FROM grupos G JOIN agrupaciones AG on AG.id_grupo = G.id_grupo WHERE AG.id_acreditado = A.id_acreditado) tg
OUTER APPLY (SELECT TOP 1 P.nombre FROM personas P WHERE P.id_persona = A.id_persona) tp
WHERE C.status = 0 
GROUP BY CR.id_credito, M.fecha_mov, ISNULL(tg.nombre, tp.nombre)
ORDER BY M.fecha_mov

其中需要注意的一点是，OUTER APPLY不需要连接回from子句中的其他表，因为它已经在基于来自FROM子句其余源(表)的值进行查询的假设下工作。此外，不要忘记为应用查询添加别名(在本例中，别名为tg和tp)。

编辑:我刚刚注意到了树视图格式的要求。我现在就来解决这个问题。

似乎不管怎样，您都希望返回每个人的所有记录。在这种情况下，您希望为组中的所有人返回相同的记录。我看到的最好的方法是作为常规连接(而不是apply)连接回persons表，然后再按tg.nombre和personas.nombre字段分组。这样，您仍然可以获得与贷款关联的每个人，但也可以获得返回的组名，以便如果存在组，则可以将其用作树中的根节点。

至于将其创建为treeview，您需要做一些额外的工作才能真正将其设置为treeview。我建议以一种可用的格式获取查询，并使用LINQ分组将其编译在一起。

然后需要这样更改查询:将上面的行OUTER APPLY (SELECT TOP 1 P.nombre FROM personas P WHERE P.id_persona = A.id_persona) tp更改为INNER JOIN personas P on P.id_persona = A.id_persona，将select/grouping从ISNULL(tg.nombre, tp.nombre) Nombre更改为tg.nombre Grupo, P.nombre Nombre。在您的代码中，在获取查询结果之后，使用以下LINQ语句来获取您的查询(假设至少是.NET 3.5)：

// Assume the query results are in dtLoanPayments strongly typed datatable.
// Also assume we have a TreeView called tvLoans
dtLoanPayments.AsEnumerable().GroupBy(tr => tr.Grupo).ToList().ForEach(tr =>
{
    (tr.Key == null ? tvLoans.Nodes : tvLoans.Nodes.Add(tr.Key)).AddRange
        (tr.Select(ti => { TreeNode tempNode = new TreeNode(ti.Nombre); tempNode.Tag = ti; return tempNode; }).ToArray());
});

同样，这真的取决于您希望它们如何显示。如果您希望按人员进一步分组，则需要在内部tr.Select之前执行另一个GroupBy，因此它将是tr.GroupBy(ti => ti.Personas).Select，并且您必须为每个贷款支付记录创建另一个TreeNode。在这些类型的情况下，只需记住，保持显示和数据逻辑分离通常要容易得多。在查询方面，只需确保返回的数据是正确的，然后处理应用程序代码中的格式。

编辑:回答你的评论问题，添加更多的代码。只需将项目列表转换为适当嵌套的TreeNode对象即可。我想我上面的解释已经解决了这个问题，但是我可以给你代码，解释到底是怎么回事。假设您的列表名为amortList，而您的TreeView名为tvLoans，那么尝试如下所示：

// First, group by group name. If it's null (meaning no group), then it will just be 
// the person's name. Doing ToList() after grouping so we can do a ForEach.
amortList.GroupBy(tr => string.IsNullOrEmpty(tr.Grupo) ? tvLoans : tvLoans.Add(tr.Grupo)).ToList()
    .ForEach(tr => 
    // This gives us the node we'll be adding each record to as the key. Now all
    // we need to do is add all loan records to the node, grouping by person
    {
        tr.Key.Nodes.AddRange(
            // Group by person's name first
            tr.GroupBy(ti => new TreeNode(ti.Nombre))
                // Then transform into each node (loan payment, etc) and add to the person node.
                .Select(ti => 
                { 
                    ti.Key.Nodes.AddRange(
                        ti.Select(tn => new TreeNode(/*Use whatever field here to display.*/).ToArray());
                    return ti.Key; // Return TreeNode.
                }).ToArray());
    }

如果这仍然令人困惑，你可以做一个foreach来完成同样的事情(并且更容易理解)。

foreach(_Amortizacion amNode in amortList)
{
    // Using Null Coalescing to create the node if it doesn't exist.
    // First check if group is null. If it isn't, try to pull the node and create it if it doesn't exist.
    TreeNodeCollection workingNode = !string.IsNullOrEmpty(amNode.Grupo) ?
        (tvLoans.Nodes[amNode.Grupo] ?? tvLoans.Nodes.Add(amNode.Grupo)).Nodes :
        tvLoans.Nodes;
    // At this point, workingNode is the NodeCollection we'll be adding the person to. Creating if doesn't exist.
    workingNode = (workingNode[amNode.Personas] ?? workingNode.Add(amNode.Personas)).Nodes;
    // Now we're adding the actual loan record to the person Node.
    workingNode.Add(new TreeNode(/* This is what will show up in TreeView. */) { Tag = amNode, Name = /* Specify a key here, in case you want to search. */ });
}

Answer 2

要回答您的问题，因为它与您当前的模式相关，您可以使用：

agrupaciones

• JOIN acreditados to personas和
• to grupos
• Use CASE Statement确定是否从personas或grupos

中提取nombre

至于在treeview中构建它，您可能希望创建一个递归查询来提取数据。

希望这能有所帮助。