I have two Tables
我有两个表
Name | ID |
--------+---------+
A | 3 |
A | 5 |
B | 1 |
ID | Count |
--------+---------+
1 | 10 |
2 | 50 |
3 | 15 |
6 | 20 |
I want to produce something which joins the ID of these two tables as well as aggregating the results of the non-matching entries
我想要生成一些与这两个表的ID相连接的东西,以及聚合不匹配项的结果。
Name | ID | Count
--------+---------+----------
A | 3 | 15
A | 5 | 0 *//(doesn't exists)*
A | others | 80 *//(10 (ID 1) + 50 (ID 2) + 20 (ID 6))*
B | 1 | 10
B | others | 85 *//(50 (ID 2) + 15 (ID 3) + 20 (ID 6))*
Till now I can get the matched items but not able to get the non matching aggregates.
到目前为止,我可以得到匹配的项,但不能得到非匹配的聚合。
I feel that doing an except
on each list of IDs and aggregating the non matching ones is not an elegant solution.
我认为在每个id列表上执行一个except并聚合不匹配的id不是一种优雅的解决方案。
Would like to find a more elegant solution to the problem.
我想找到一个更优雅的方法来解决这个问题。
EDIT: I forgot to mention that I am using SQL server
. The oracle solution would not be of any use in my scenario but will help in a similar scenario for Oracle users.
编辑:我忘了说我正在使用SQL server。oracle解决方案在我的场景中不会有任何用处,但是对于oracle用户来说将会有帮助。
3 个解决方案
#1
0
A LINQ solution is this:
LINQ解决方案是:
from n in Names
join c in Counts on n.ID equals c.ID into outer
select new { n.Name,
ID = outer.Count() > 1 ? "others" : n.ID.ToString(),
Sum = ((int?)outer.Sum(x => x.Count)) ?? 0 }
It does the equivalent of an outer join, but also has some trickery to translate a null
to 0
. In linq-to-sql this nicely translates into SQL. And because it is translated into SQL outer.Count()
does not throw an exception when outer
is null
.
它的作用相当于一个外部连接,但也有一些技巧可以将null转换为0。在linqto - SQL中,这很好地转化为SQL。而且因为它被转换为SQL outer. count(),所以在outer为null时不会抛出异常。
#2
1
How about this: Get the matched items and do a union with a second query. The second query would select name, 'others', sum(count) and would group on name where id is not in the ids from the first table (you can check that with a subquery).
这样如何:获取匹配的项并使用第二个查询执行联合。第二个查询将选择name、'others'、sum(count),并在id不在第一个表的id中分组(您可以使用子查询来检查它)。
I can type it out if you want to, you look like you know what you are doing and just need a general idea about how to do it.
我可以把它打出来,如果你想,你看起来知道你在做什么,只是需要一个关于如何做的总体想法。
#3
1
In case of oracle use nvl. sql server ifnull() and mysql isnull() with union of left and right outer instead of full outer
如果oracle使用nvl。sql server ifnull()和mysql isnull()是由左外和右外而不是全外组成的
select name, id, sum(cnt) from
(
select name, nvl(to_char(a.id), 'others') id, nvl(b.count,0) cnt
from table1 a
full outer join
table2 b on a.id = b.id)
group by name, id
#1
0
A LINQ solution is this:
LINQ解决方案是:
from n in Names
join c in Counts on n.ID equals c.ID into outer
select new { n.Name,
ID = outer.Count() > 1 ? "others" : n.ID.ToString(),
Sum = ((int?)outer.Sum(x => x.Count)) ?? 0 }
It does the equivalent of an outer join, but also has some trickery to translate a null
to 0
. In linq-to-sql this nicely translates into SQL. And because it is translated into SQL outer.Count()
does not throw an exception when outer
is null
.
它的作用相当于一个外部连接,但也有一些技巧可以将null转换为0。在linqto - SQL中,这很好地转化为SQL。而且因为它被转换为SQL outer. count(),所以在outer为null时不会抛出异常。
#2
1
How about this: Get the matched items and do a union with a second query. The second query would select name, 'others', sum(count) and would group on name where id is not in the ids from the first table (you can check that with a subquery).
这样如何:获取匹配的项并使用第二个查询执行联合。第二个查询将选择name、'others'、sum(count),并在id不在第一个表的id中分组(您可以使用子查询来检查它)。
I can type it out if you want to, you look like you know what you are doing and just need a general idea about how to do it.
我可以把它打出来,如果你想,你看起来知道你在做什么,只是需要一个关于如何做的总体想法。
#3
1
In case of oracle use nvl. sql server ifnull() and mysql isnull() with union of left and right outer instead of full outer
如果oracle使用nvl。sql server ifnull()和mysql isnull()是由左外和右外而不是全外组成的
select name, id, sum(cnt) from
(
select name, nvl(to_char(a.id), 'others') id, nvl(b.count,0) cnt
from table1 a
full outer join
table2 b on a.id = b.id)
group by name, id