GROUP BY问题，当从2表的SELECT来的dataGridView

本文介绍了GROUP BY问题，当从2表的SELECT来的dataGridView的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 随着你一点帮助我做了这两个 QUERY 。我贴的照片，所以你可以看到，在绿色方块是我想看到的空值和红色正方形这是我不会想显示值。变量 vyberradek 插入值'子'是 INT 在DB，但我将它传递到 。字符串 With little help of you I made this two QUERY. I posted pictures so you can see that in green squares are empty values which I would like to see and in red squares are values which I wouldnt like to display. Variable vyberradek inserts values for 'subkey' it is INT in DB but I pass it through string.所以我将简化：现在我可以显示所有或一点点，你会帮助我如何显示正确的记录？ So I will simplify: Now I can display all or a little, would you help me how to display the correct records? 这首查询做到这一点： string sQuery = string.Format("SELECT zajsluz.akce,zajsluz.text,klisluz.pocet,klisluz.subkey,zajsluz.ID FROM zajsluz LEFT JOIN klisluz ON zajsluz.ID=klisluz.IDzajsluz WHERE zajsluz.akce= '{0}' GROUP BY klisluz.subkey,zajsluz.akce,zajsluz.text,klisluz.pocet,zajsluz.ID", sZakce);下面是什么呢第二个查询： Here is what does the second query: string sQuery = string.Format("SELECT zajsluz.akce,zajsluz.text,klisluz.pocet,klisluz.subkey,zajsluz.ID FROM zajsluz LEFT JOIN klisluz ON zajsluz.ID=klisluz.IDzajsluz WHERE zajsluz.akce= '{0}' and klisluz.subkey ='" + vyberradek + "' GROUP BY klisluz.subkey,zajsluz.akce,zajsluz.text,klisluz.pocet,zajsluz.ID", sZakce); 因此​​，大家可以看到我也想显示空的。我想你也可能需要将检查该复选框这里是代码：So as you can see I would also like to display the empty ones. I think you might need also the code which checks the checkboxes here it is: for (int i = 0; i < dtg_ksluzby.Rows.Count; i++) { var row = dtg_ksluzby.Rows[i]; int id = (int)row.Cells["ID"].Value; using (var novyprikaz3 = new SqlCommand("SELECT * from klisluz WHERE subkey='" + vyberradek + "'AND IDzajsluz=" + id, spojeni)) { spojeni.Open(); SqlDataReader precti3 = novyprikaz3.ExecuteReader(); if (precti3.HasRows) { row.Cells[5].Value = true; } spojeni.Close(); } } 请问你们，请建议我应该如何查询模样选择甚至不检查值（在的arent表klisluz）和检查的呢？ Would you guys please suggest me how should the query look like to select even the non checked value (which arent in table klisluz) and the checked ones? 当我看到没有人回答这么好，让我给你们举一个简单的例子：当我创建客户端，我从10复选框选中例如3。所以它选择3行（以及它添加到表klisluz）。我有两个查询，第一次查询显示来自其他客户端选中的复选框，也非检查的人此客户端。它简单地示出了一切的是在数据库中，所以它显示了与其他客户的检查事行（但只有那些对本相同，并且一些其他客户端/ s时，它不CREATE重复）。第二只显示检查此客户端，但没有那些他还没有choosen。所以，当我做这个客户的编辑我想显示什么他做了检查，其余7（7可能是他还没有签，所以我可以检查他们）。现在，我可以只显示我是怎么检查（不包括非选中）或一切，包括其他客户的检查（这是该客户端相同）。我希望这是可以理解的，对不起我的英语薄弱。 As I see nobody is replying so well let me give you one simple example: When I create the client I check for example 3 from 10 checkboxes. So it selects 3 rows (and those it adds into table klisluz). And I have these two queries, First query shows the checked checkbox from other clients, but also the non-checked ones for this client. It simply shows everything what is in database, so it shows rows with checked things of other clients (but only those which are the same for this and some other client/s, it DOESNT CREATE duplicates). The second shows only checked for this client but without those which he havent choosen. So when I do edit of this client I would like to display what did he checked and the rest 7(the 7 possibilities what he haven't checked so I could check them). Now I can display only what did I checked (without the non-checked) or everything including other clients checks (which are the same for this client). I hope it is understandable, sorry for my weak english. P.S。随着检查我的意思是检查复选框（选择行）基本上它是一个程序，增加了对客户的服务。P.S. With check I mean checking checkbox (selecting row)Basically its a program that adds service to clients.感谢你这么多的关注，也为如果你看过所有的阅读。林坚持这个问题2天：/ Thank you so much for your interest and also for reading if you read that all. Im stuck with this issue for 2 days :/照片为kwwallpe：你可以在这里看到，NIC从来没有被任何负荷消费选择，因此被显示。 Picture for kwwallpe:You can see here that "Nic" has never been selected by any costumer so it is displayed. 为了使我的问题更清楚 - 我把其中的一些列从另一个表（你可以在SQL查询中看到）To make my question more clear - I take some of these columns from another table (as you can see in sql query).这是什么呢kwwallpe代码：This is what does kwwallpe's code: string sQuery = string.Format("SELECT zajsluz.akce,zajsluz.text,klisluz.pocet,klisluz.subkey,zajsluz.ID FROM zajsluz LEFT JOIN klisluz ON zajsluz.ID=klisluz.IDzajsluz WHERE zajsluz.akce= '{0}' and ISNULL(klisluz.subkey, '" + vyberradek + "') = '" + vyberradek + "' GROUP BY klisluz.subkey,zajsluz.akce,zajsluz.text,klisluz.pocet,zajsluz.ID", sZakce);这代码更接近的方式我觉得那些2.因为它选择哪些从未被选择的值任何负荷消费B4。你可以看到帽NIC被显示，因为它不是在表中klisluz This code is much way close I think that those 2. Because it SELECT values which have never been selected by any costumer b4. You can see hat "Nic" is displayed because it is not in the table klisluz. 推荐答案如果我认识你以后，这是的让你从得到的klisluz没有条目行中的一部分：If I'm understanding what you're after, this is the part that's keeping you from getting the rows with no entries in klisluz:WHERE zajsluz.akce= '{0}' and klisluz.subkey ='" + vyberradek + "'有关的行不存在中klisluz，子项将是NULL，不会与WHERE子句匹配任何内容。要获得这些行也，可以替换当前的WHERE子句中使用：For the rows that don't exist in klisluz, subkey will be NULL, which won't match anything with that WHERE clause. To get these rows as well, you can replace your current WHERE clause with:WHERE zajsluz.akce= '{0}' and ISNULL(klisluz.subkey, '" + vyberradek + "') ='" + vyberradek + "'下面的代码全系列：string sQuery = string.Format("SELECT zajsluz.akce,zajsluz.text,klisluz.pocet,klisluz.subkey,zajsluz.ID FROM zajsluz LEFT JOIN klisluz ON zajsluz.ID=klisluz.IDzajsluz WHERE zajsluz.akce= '{0}' and ISNULL(klisluz.subkey, '" + vyberradek + "') = '" + vyberradek + "' GROUP BY klisluz.subkey,zajsluz.akce,zajsluz.text,klisluz.pocet,zajsluz.ID", sZakce);要确定复选框是否应检查：To determine whether the checkbox should be checked:if (precti3.HasRows){ precti3.Read(); if (precti3.Item("subkey") != Null) { row.Cells[5].Value = true; } else { row.Cells[5].Value = false; }} 我的认为的这是怎么它会去用C＃。我在VB.NET中工作，用于此的在线转换器。I think that's how it'd go in C#. I work in VB.NET, used an online converter for this. 这篇关于GROUP BY问题，当从2表的SELECT来的dataGridView的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！