如何使用group更新表 [英] How to update table using group by

问题描述

我正在使用Visual Studio 2010 C＃语言，请帮助我



在下表中：每个家庭都有familyid，即拥有相同familyid的成员是一个家庭。关系码'01'=父亲，'05'=儿子，'06'=女儿

I am using visual studio 2010 C# language, please help me

in the following table : every family has familyid i.e members having same familyid is a family. The relationcode '01'= father, '05'=son, '06'=daughter

Familyid  Memberid  SerialNumber   Name      Age   Relationcode   Snofather fathername                        
101           1         1          Jame       30      01            -           -
101           2         2          John       10      05            -           -
101           3         3          mary        5      06            -           -
102           1         1          Lawrence    45     01            -           -
102           2         2          Linda       15     06            -           -
102           3         3          milie       13     06            -           -
102           4         4          sam         10     05            -           -
103           1         1          Joe         34     01            -           -
103           2         2          kate        10     06            -           -

i想要更新列snofather（父亲的SerialNumber）和每个家庭的父姓，例如 - 在第一个家庭jame是john和mary的父亲，所以snofather是1和父亲的名字是约翰和玛丽和下一个家庭的jame。

输出应该是这样的;

i want to update the columns snofather (SerialNumber of father) and fathername of every family for e.g - in the first family jame is father of john and mary, so snofather is 1 and fathername is jame for john and mary and the next family so on.
The output should look like this ;

Familyid  Memberid  SerialNumber   Name      Age   Relationcode   Snofather fathername                        
101           1         1          Jame       30      01            -           -
101           2         2          John       10      05            1         Jame
101           3         3          mary        5      06            1         Jame
102           1         1          Lawrence    45     01            -           -
102           2         2          Linda       15     06            1        Lawrence 
102           3         3          milie       13     06            1        Lawrence    
102           4         4          sam         10     05            1        Lawrence 
103           1         1          Joe         34     01            -           -
103           2         2          kate        10     06            1           Joe

<br/>

推荐答案

假设：您的DataTable名为'Families

Assume: your DataTable is named 'Families

// required
using System.Data;

private void UpdateFamiles()
{
    string currentParentName = "";

    foreach (DataRow row in Families.Rows)
    {
        if (Convert.ToInt32(row.Field<int>("RelationCode")) == 1)
        {
            currentParentName = row.Field<string>("Name");
        }
        else
        {   
            row.SetField<int>("snoFather", 1);
            row.SetField<string>("fathername", currentParentName);
        }
    }
}

你可能想知道你是否可以在这里使用Linq，我想你可以，但我不会使用Linq，因为：据我所知它，Linq并不意味着用于修改字段的值。如果有人读这篇文章认为Linq可以/应该被使用，我会非常好奇你的意见作为评论。

You may wonder if you could use Linq here, and I imagine you could, but I would not use Linq, because: as I understand it, Linq is not meant to be used to modify the value of fields. If someone reading this thinks Linq could/should be used, I'd be very curious to have your opinion as a comment here.

我不认为你需要这个是重复的任务标签对我来说没有多大意义。



虽然，你可以在SQL中做这样的事情：



在此处查看公用表格表格 [< a href =http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx\"target =_ blanktitle =New Window> ^ ]。

I don't think you need this to be recurring task and the tags don't make much sense to me.

Although, you could do something in SQL like this:

Look here for Common Table Expressions[^].

WITH Family_Father (FamilyId, SerialNumber, Name)
AS
(
    SELECT FamilyId, SerialNumber, Name
    FROM #Temp
    WHERE RelationCode = 1
)

SELECT
    #Temp.FamilyId,
    #Temp.MemberId,
    #Temp.SerialNumber,
    #Temp.Name,
    #Temp.Age,
    #Temp.RelationCode,
    CASE WHEN (RelationCode <> 1) THEN Family_Father.SerialNumber ELSE 0 END As SNoFather,
    CASE WHEN (RelationCode <> 1) THEN Family_Father.Name ELSE '' END AS FatherName
FROM
    #Temp INNER JOIN Family_Father ON
        #Temp.FamilyId = Family_Father.FamilyId

这篇关于如何使用group更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！