如何通过加入两个表来显示所有类别，如果类别不存在则显示0？ [英] How to show all categories by joining the two table and display 0 if category not existed?

问题描述

我有tbl1和tbl2



tbl1：



id服务

==================

1 Microsoft

2 Oracle

3 Java





tbl2



服务RollNumber

==========================

Microsoft 13

Microsoft 12
Java 2

Java 16

Microsoft 15



如何使用以下方式获取linq：



服务费用

===================

Microsoft 3

Java 2

Oracle 0



我尝试过的：



var data = tbl1中的t1

tbl2中加入t2
t1上的
.Service.Trim（）等于t2.Service.Trim（）进入eGroup

来自t2in eGroup.DefaultIfEmp ty（）

I have tbl1 and tbl2

tbl1:

id Service
==================
1 Microsoft
2 Oracle
3 Java


tbl2

Service RollNumber
==========================
Microsoft 13
Microsoft 12
Java 2
Java 16
Microsoft 15

How can i get as below using linq:

Service Counts
===================
Microsoft 3
Java 2
Oracle 0

What I have tried:

var data = from t1 in tbl1
join t2 in tbl2
on t1.Service.Trim() equals t2.Service.Trim() into eGroup
from t2in eGroup.DefaultIfEmpty()

推荐答案

你做错了：tbl2应该包含ServiceID，而不是名字：

You are doing that wrong: tbl2 should contain the ServiceID, not the name:

ServiceID RollNumber
1          13
1          12
3           2
3          16
2          15

并使用JOIN访问服务名称。



这确实使您想要的最终查询稍微复杂一点，但不是很多：

And use a JOIN to access the Service name.

That does make the final query you want slightly more complex, but not a lot:

SELECT a.Service, ISNULL(b.CNT, 0) 
FROM tbl1 a
LEFT JOIN (SELECT ServiceID, COUNT(RollNumber) AS CNT 
           FROM tbl2
           GROUP BY ServiceID) b 
       ON a.ID = b.ServiceID

自己做两个好处：

1）使用合理的表名！它使您的查询更具可读性，因此更可靠...

2）向tbl2添加IDENTITY id列，以防止重复。您不需要在大多数查询中使用它，但SQL不允许您有重复的行。

And do yourself two favours:
1) Use sensible table names! It makes your queries so much more readable, and thus reliable...
2) Add an IDENTITY id column to tbl2, to prevent duplication. You don't need to use it in most of your queries, but SQL will not allow you to have duplicate rows.

试试这个：

Try this:

var data = from t1 in tbl1
    join t2 in tbl2 on t1.Service.Trim() equals t2.Service.Trim() into eGroup
    from together in eGroup.DefaultIfEmpty()
    select new 
    {
       Service = together.?Service,
       Count = together.Count()
    };

有关详细信息，请参阅：执行左外连接（C＃中的LINQ）| Microsoft Docs [ ^ ]



另一种方法是根据服务名称计算表＃2中的记录数：

For further details, please see: Perform left outer joins (LINQ in C#) | Microsoft Docs[^]

Another way is to calculate count of records in a table #2 based on service name:

var result = tbl1
	.Select(x=> new
	{
		Service = x.Service,
		Count = tbl2.Count(y=> y.Service.Trim()==x.Service.Trim()))
	});

祝你好运！

Good luck!

这篇关于如何通过加入两个表来显示所有类别，如果类别不存在则显示0？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！