呈现多个SQL表

Presenting multiple sql tables
2021-09-14
  •  译文(汉语)
  •  原文(英语)

抱歉,如果您之前曾问过这个问题,但我找不到任何地方想要的东西.

设置:我有一个带有3个表的MS SQL数据库

表格-FoodInfo -FoodId(PK),DanName

表格-CompName -CmpId(PK),CmpName

表格-营养素(连结表) -FoodId(FK),CmpId(fk),BestLoc

对于每个DanName行,大约关联8个CmpName,对于每个CmpName,关联一个1 BestLoc

当我在Winform中显示数据时,这导致DanName重复8次.

问题是,我想将DanName与关联的CmpName及其值一起显示在一行中.

诸如此类:DanName-CmpName1-BestLoc值-CmpName2-BestLoc值-CmpName3-BestLoc值..依此类推.

对于解决方案,我非常灵活,但是我不确定该走哪条路.我应该尝试在数据库中创建一个看起来像我想要的表并将其放在DataGridView中,还是应该尝试使用Linq-To-SQL解决它并将其放入列表视图中?

如前所述,我对解决方案非常灵活,我只是不想在文本框中输入数据.我还希望可以使用我检索到的数据并在程序的其他地方使用它.

如果有人有解决方案,我希望看到它的示例.

编辑: 要更详细一点,并带有一些代码示例.

我在LINQ上的DAL课程

void bw_DoWork(object sender, DoWorkEventArgs e)
{
    Table<FoodInfo> FoodInfo = db.GetTable<FoodInfo>();
    Table<CompName> CompName = db.GetTable<CompName>();
    Table<Nutrient> Nutrients = db.GetTable<Nutrient>();

    var foods =
        from compname in CompName
        join nutrients in Nutrients on compname.CompId equals nutrients.CompId
        join foodinfo in FoodInfo on nutrients.FoodId equals foodinfo.FoodId
        where foodinfo.DanName.StartsWith(searchWord) && (compname.CompId >= 0
                                                      && compname.CompId < 8)
        select new { foodinfo.DanName, compname.CmpNamDK, nutrients.BestLoc };

    foreach (var food in foods)
    {
        DanName = food.DanName;
        Compname = food.CmpNamDK;
        BestLoc = food.BestLoc;

        OnSearchResultArgs OSR = new OnSearchResultArgs(DanName, Compname, BestLoc);
        onResult(this, OSR);
    }
}

wich触发我的表单中的事件处理程序,并发送结果并执行以下代码:

listView1.FullRowSelect = true;
listView1.Columns.Add("DanName", 100);
listView1.Columns.Add("CompName", 150);
listView1.Columns.Add("BestLoc", 50);

private void UpdateControls(object sender, OnSearchResultArgs e)
{
    var item = new ListViewItem();
    item.Text = e.DanName;
    item.SubItems.Add(e.CompName);
    item.SubItems.Add(e.BestLoc);
    listView1.Items.Add(item);
}

UpdateControl正在被调用,但是我认为没有必要在此处放置它.

通过此操作,我得到以下信息:

我想将这8行变成1行.喜欢:

速聊1:
我只是不想在文本框中输入数据,是否意味着所有条目(DanName-CmpName1-BestLoc-CmpName2-BestLoc ...)都应位于网格中的单独单元格中?还是在一个单元格中使用DanName,在一个相邻的单元格中串联其他数据?
速聊2:
这听起来像一个演示问题,而不是数据问题.如果没有有关表示层的更多信息,那么很难在其中提供任何建议,因为它取决于您要绑定的内容或显示方式.如果您确实想要连接数据,则必须回答上述有关澄清目标格式的Gert问题.
速聊3:
看起来您想透视数据.在linq和t-sql中都是可能的.如果您发布了一些代码,到目前为止您所做的一切,那么这里的人可以为您提供帮助.
速聊4:
抱歉,您不愿透露更多详细信息,希望det更新将对您有所帮助.该数据透视表看起来可以解决问题.你能用我的代码举一个例子吗?
速聊5:
您为什么不只输入示例输出,这些粗细的行并没有真正使它更清晰.这表明CompNames中有一个级别.
解决过程1

我有两个解决方案,都有其缺点.

第一种解决方案是手动旋转数据,但这不是很动态,因为如果您开始拥有超过8个CmpName,则必须更改查询,第二种解决方案是将所有CmpName和BestLoc值放在一个列中.

下面的T-SQL应该演示这两种解决方案.

--** Set up test data
DECLARE @FoodInfo Table (FoodId INT, DanName varchar(50));
DECLARE @CompName TABLE (CmpId INT, CmpName VARCHAR(50));
DECLARE @Nutrients TABLE (FoodId INT , CmpId INT, BestLoc VARCHAR(50));

INSERT INTO @FoodInfo (FoodId, DanName)
VALUES  (1, 'Abrikos, torret');

INSERT INTO @CompName (CmpId, CmpName)
VALUES  (1, 'Energi'),(2, 'Protein, total'),(3, 'total-N'),(4, 'Fedt, total'),(5, 'Maettede fedtsyrer'),(6, 'monoumaett, fedtsyrer'),(7, 'polyumaett, fedtsyrer'),(8, 'kulhydrat, tilgaengelig');

INSERT INTO @Nutrients(FoodId, CmpId, BestLoc)
VALUES  (1,1,1159),(1,2,2.9),(1,3,0.5),(1,4,1.7),(1,5,0.1),(1,6,0.6),(1,7,0.6),(1,8,57.2);

--** Standard query
SELECT fi.DanName
     , cn.CmpName
     , n.BestLoc
  FROM @FoodInfo AS fi
  JOIN @Nutrients AS n
    ON fi.FoodId = n.FoodId
  JOIN @CompName AS cn
    ON n.CmpId = cn.CmpId;

--** Manual Pivot
WITH sortCTE AS (
        SELECT fi.DanName
             , cn.CmpName
             , n.BestLoc
             , ROW_NUMBER() OVER (PARTITION BY fi.FoodId ORDER BY cn.CmpId) AS 'col'
          FROM @FoodInfo AS fi
          JOIN @Nutrients AS n
            ON fi.FoodId = n.FoodId
          JOIN @CompName AS cn
            ON n.CmpId = cn.CmpId)
SELECT s.DanName
     , MAX(CASE WHEN s.col = 1 THEN s.CmpName END) AS Cmp1
     , MAX(CASE WHEN s.col = 1 THEN s.BestLoc END) AS BestLoc1
     , MAX(CASE WHEN s.col = 2 THEN s.CmpName END) AS Cmp2
     , MAX(CASE WHEN s.col = 2 THEN s.BestLoc END) AS BestLoc2
     , MAX(CASE WHEN s.col = 3 THEN s.CmpName END) AS Cmp3
     , MAX(CASE WHEN s.col = 3 THEN s.BestLoc END) AS BestLoc3
     , MAX(CASE WHEN s.col = 4 THEN s.CmpName END) AS Cmp4
     , MAX(CASE WHEN s.col = 4 THEN s.BestLoc END) AS BestLoc4
     , MAX(CASE WHEN s.col = 5 THEN s.CmpName END) AS Cmp5
     , MAX(CASE WHEN s.col = 5 THEN s.BestLoc END) AS BestLoc5
     , MAX(CASE WHEN s.col = 6 THEN s.CmpName END) AS Cmp6
     , MAX(CASE WHEN s.col = 6 THEN s.BestLoc END) AS BestLoc6
     , MAX(CASE WHEN s.col = 7 THEN s.CmpName END) AS Cmp7
     , MAX(CASE WHEN s.col = 7 THEN s.BestLoc END) AS BestLoc7
     , MAX(CASE WHEN s.col = 8 THEN s.CmpName END) AS Cmp8
     , MAX(CASE WHEN s.col = 8 THEN s.BestLoc END) AS BestLoc8
  FROM sortCTE AS s
 GROUP BY s.DanName;

--** All in one column
SELECT fi.DanName
     , ISNULL(SUBSTRING((SELECT ', ' + cn.CmpName + ' - ' + CONVERT(VARCHAR(8), n.BestLoc)
                           FROM @Nutrients AS n
                           JOIN @CompName AS cn
                             ON n.CmpId = cn.CmpId
                          WHERE fi.FoodId = n.FoodId
                       ORDER BY cn.CmpId ASC FOR XML PATH('') ), 3, 5000), '') AS 'Cmp'
  FROM @FoodInfo AS fi;

抱歉,我不知道LINQ中的等效查询是什么样子,但这可以使您了解目标是什么.

我希望这有帮助.

速聊1:
..对不起,我迟到了,我离开了莱特利.这看起来就像是我正在寻找的解决方案,现在我已经在C#程序中创建了一个透视图类,但是该程序的运行速度确实很慢,所以我认为在数据库中创建透视图是正确的解决方案,我会请在本周晚些时候尝试一下,然后返回结果..现在,我将接受您的回答.谢谢 (: !
解决过程2

如果有人遇到与我相同的问题,则只是更新.

首先,我使用T-SQL尝试了@JonPayne的解决方案.它解决了我的问题,但是对我来说工作非常缓慢.玛比,我做错了.我不知道.

我最终从在这里找到的指南中创建了一种透视方法:http : //www.codeproject.com/Articles/22008/C-Pivot-Table

非常简单,我从SQL Server查询我的无序数据,将其放在数据表中,并使用数据透视方法格式化该数据表,该数据表返回有序数据表.

Sorry if this question have been asked before but I cant find what I'm looking for anywhere.

Setup: I have a MS SQL database with 3 tables

Table - FoodInfo Column - FoodId(PK), DanName

Table - CompName Column - CmpId(PK), CmpName

Table - Nutrients(junction table) Column - FoodId(FK), CmpId(fk), BestLoc

For every DanName row there is associated about 8 CmpName and for every CmpName there is associated 1 BestLoc

That leads to DanName getting repeatet 8 times when i show the data in my winform.

The problem is, i want to present DanName in one line with the associated CmpName's and their values.

Something like: DanName - CmpName1 - BestLoc value - CmpName2 - BestLoc value - CmpName3 - BestLoc value.. and so on.

I am very flexible about the solution, but im not sure what path to follow. should I try to create a table in the DB which look as I want it to and put it in a DataGridView, or should I try to solve it with Linq-To-SQL and put it in a listview?

As said, I'm pretty flexible about the solution, I just dont want the data in at textbox. I would also like it to be possible to use the data i retrieve and use it elsewhere in my program.

If anyone have a solution i would love to see an example of it.

Edit: To be a little more detailed, and with some code samples.

My DAL class with linq

void bw_DoWork(object sender, DoWorkEventArgs e)
{
    Table<FoodInfo> FoodInfo = db.GetTable<FoodInfo>();
    Table<CompName> CompName = db.GetTable<CompName>();
    Table<Nutrient> Nutrients = db.GetTable<Nutrient>();

    var foods =
        from compname in CompName
        join nutrients in Nutrients on compname.CompId equals nutrients.CompId
        join foodinfo in FoodInfo on nutrients.FoodId equals foodinfo.FoodId
        where foodinfo.DanName.StartsWith(searchWord) && (compname.CompId >= 0
                                                      && compname.CompId < 8)
        select new { foodinfo.DanName, compname.CmpNamDK, nutrients.BestLoc };

    foreach (var food in foods)
    {
        DanName = food.DanName;
        Compname = food.CmpNamDK;
        BestLoc = food.BestLoc;

        OnSearchResultArgs OSR = new OnSearchResultArgs(DanName, Compname, BestLoc);
        onResult(this, OSR);
    }
}

wich triggers an eventhandler in my form and send over the results and execute this code:

listView1.FullRowSelect = true;
listView1.Columns.Add("DanName", 100);
listView1.Columns.Add("CompName", 150);
listView1.Columns.Add("BestLoc", 50);

private void UpdateControls(object sender, OnSearchResultArgs e)
{
    var item = new ListViewItem();
    item.Text = e.DanName;
    item.SubItems.Add(e.CompName);
    item.SubItems.Add(e.BestLoc);
    listView1.Items.Add(item);
}

The UpdateControl is getting invoked, but I don't think that is necessary to put in here.

and by this I get the following:

I want to turn those 8 rows into 1 row. like:

Talk1:
I just dont want the data in a textbox Do you mean that all entries (DanName - CmpName1 - BestLoc - CmpName2 - BestLoc ...) should be in separate cells in the grid? Or could it be DanName in one cell and the other data concatenated in one adjacent cell?
Talk2:
This sounds like a presentation problem, not a data problem. Without more information about your presentation tier, it's hard to offer any advice on how to do it there since it's dependent on what you are binding to or how you are presenting it. If you really want to concatenate the data, you'll have to answer Gert's question above about clarifying your target format.
Talk3:
It looks like you want to Pivot the data.It is Possible both in linq and t-sql. If you post some code what you have done so far then people here can help you.
Talk4:
Sorry for not beeing more detailed, hope det update will help. This Pivot looks like it could do the trick. can you give an example with my code ?
Talk5:
Why don't you just type a sample output, these scrawly lines don't really make it clearer. It suggests that there is a level in CompNames.
Solutions1

I have two solutions, both have their drawbacks.

The first solution is to manually pivot the data, but this isn't very dynamic as you'll have to change the query should you start having more than 8 CmpName and the second solution is to put all CmpName and BestLoc values in one column.

The T-SQL below should demonstrate both solutions.

--** Set up test data
DECLARE @FoodInfo Table (FoodId INT, DanName varchar(50));
DECLARE @CompName TABLE (CmpId INT, CmpName VARCHAR(50));
DECLARE @Nutrients TABLE (FoodId INT , CmpId INT, BestLoc VARCHAR(50));

INSERT INTO @FoodInfo (FoodId, DanName)
VALUES  (1, 'Abrikos, torret');

INSERT INTO @CompName (CmpId, CmpName)
VALUES  (1, 'Energi'),(2, 'Protein, total'),(3, 'total-N'),(4, 'Fedt, total'),(5, 'Maettede fedtsyrer'),(6, 'monoumaett, fedtsyrer'),(7, 'polyumaett, fedtsyrer'),(8, 'kulhydrat, tilgaengelig');

INSERT INTO @Nutrients(FoodId, CmpId, BestLoc)
VALUES  (1,1,1159),(1,2,2.9),(1,3,0.5),(1,4,1.7),(1,5,0.1),(1,6,0.6),(1,7,0.6),(1,8,57.2);

--** Standard query
SELECT fi.DanName
     , cn.CmpName
     , n.BestLoc
  FROM @FoodInfo AS fi
  JOIN @Nutrients AS n
    ON fi.FoodId = n.FoodId
  JOIN @CompName AS cn
    ON n.CmpId = cn.CmpId;

--** Manual Pivot
WITH sortCTE AS (
        SELECT fi.DanName
             , cn.CmpName
             , n.BestLoc
             , ROW_NUMBER() OVER (PARTITION BY fi.FoodId ORDER BY cn.CmpId) AS 'col'
          FROM @FoodInfo AS fi
          JOIN @Nutrients AS n
            ON fi.FoodId = n.FoodId
          JOIN @CompName AS cn
            ON n.CmpId = cn.CmpId)
SELECT s.DanName
     , MAX(CASE WHEN s.col = 1 THEN s.CmpName END) AS Cmp1
     , MAX(CASE WHEN s.col = 1 THEN s.BestLoc END) AS BestLoc1
     , MAX(CASE WHEN s.col = 2 THEN s.CmpName END) AS Cmp2
     , MAX(CASE WHEN s.col = 2 THEN s.BestLoc END) AS BestLoc2
     , MAX(CASE WHEN s.col = 3 THEN s.CmpName END) AS Cmp3
     , MAX(CASE WHEN s.col = 3 THEN s.BestLoc END) AS BestLoc3
     , MAX(CASE WHEN s.col = 4 THEN s.CmpName END) AS Cmp4
     , MAX(CASE WHEN s.col = 4 THEN s.BestLoc END) AS BestLoc4
     , MAX(CASE WHEN s.col = 5 THEN s.CmpName END) AS Cmp5
     , MAX(CASE WHEN s.col = 5 THEN s.BestLoc END) AS BestLoc5
     , MAX(CASE WHEN s.col = 6 THEN s.CmpName END) AS Cmp6
     , MAX(CASE WHEN s.col = 6 THEN s.BestLoc END) AS BestLoc6
     , MAX(CASE WHEN s.col = 7 THEN s.CmpName END) AS Cmp7
     , MAX(CASE WHEN s.col = 7 THEN s.BestLoc END) AS BestLoc7
     , MAX(CASE WHEN s.col = 8 THEN s.CmpName END) AS Cmp8
     , MAX(CASE WHEN s.col = 8 THEN s.BestLoc END) AS BestLoc8
  FROM sortCTE AS s
 GROUP BY s.DanName;

--** All in one column
SELECT fi.DanName
     , ISNULL(SUBSTRING((SELECT ', ' + cn.CmpName + ' - ' + CONVERT(VARCHAR(8), n.BestLoc)
                           FROM @Nutrients AS n
                           JOIN @CompName AS cn
                             ON n.CmpId = cn.CmpId
                          WHERE fi.FoodId = n.FoodId
                       ORDER BY cn.CmpId ASC FOR XML PATH('') ), 3, 5000), '') AS 'Cmp'
  FROM @FoodInfo AS fi;

I'm sorry but I don't know what the equivalent queries would look like in LINQ but this could give you some idea what you are aiming for.

I hope this helps.

Talk1:
.. Sorry for the late answer, iv been out off town lateley. This looks like just the solution im looking for, right now i have created a pivot class in my C# program, but the program is really slow in the process, so i think that making the pivot in the database is det correct solution, i will try it out later this week and return with an result.. for now i will accept your answer. Thanks (: !
Solutions2

Just an update if anyone runs into the same question as i had.

First i tried the solution from @JonPayne, with the T-SQL.. it solved my problem, but worked very slow for me. Maby i was doing i wrong. i dont know.

I ended up with making a pivot method from a guide i found here: http://www.codeproject.com/Articles/22008/C-Pivot-Table

Very simple, I query my unordered data from the SQL-Server, put it in a datatable and format the datatable with the pivot method, that returns a ordered datatable.

转载于:https://stackoverflow.com/questions/15593618/presenting-multiple-sql-tables

本人是.net程序员,因为英语不行,使用工具翻译,希望对有需要的人有所帮助
如果本文质量不好,还请谅解,毕竟这些操作还是比较费时的,英语较好的可以看原文

留言回复
我们只提供高质量资源,素材,源码,坚持 下了就能用 原则,让客户花了钱觉得值
上班时间 : 周一至周五9:00-17:30 期待您的加入