blocks|key|462034|text|另一种方法是将union+all与聚合一起使用：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|462035|select+in_i10descr,+in_gems_pcsi9,+count(*)+as+numcodes
from+(select+code,+max(in_i10descr)+as+in_i10descr,+max(in_gems_pcsi9)+as+in_gems_pcsi9
++++++from+((select+i10_descr.i10_code+as+code,+1+as+in_i10descr,+0+as+in_gems_pcsi9
+++++++++++++from+i10_descr
++++++++++++)+union+all
++++++++++++(select+gems_pcsi9.i10_code,+0,+1
+++++++++++++gems_pcsi9.i10_code
++++++++++++)
+++++++++++)+t
++++++group+by+code
+++++)+c
group+by+in_i10descr,+in_gems_pcsi9;|code-block|syntax|javascript|462036|这将分别计算每个表和两个表中的计数。|462037|entityMap^0|7|9|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

An alternative method is to use <code>union all</code> with aggregation:

<pre><code>select in_i10descr, in_gems_pcsi9, count(*) as numcodes
from (select code, max(in_i10descr) as in_i10descr, max(in_gems_pcsi9) as in_gems_pcsi9
 from ((select i10_descr.i10_code as code, 1 as in_i10descr, 0 as in_gems_pcsi9
 from i10_descr
 ) union all
 (select gems_pcsi9.i10_code, 0, 1
 gems_pcsi9.i10_code
 )
 ) t
 group by code
 ) c
group by in_i10descr, in_gems_pcsi9;
</code></pre>

This will calculate counts of things in each table separately and in both tables.

blocks|key|462103|text|您可以使用反联接模式来获取存在于一个表中但不存在于另一个表中的i10_code的列表。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|462104|SELECT+i.i10_code
++FROM+i10_descr+i
++LEFT
++JOIN+gems_pcsi9+g
++++ON+g.i10_code+=+i.i10_code
+WHERE+g.i10_code+IS+NULL
+ORDER+BY+i.i10_code|code-block|syntax|javascript|462105|如果只想要计数，可以在选择列表中使用COUNT(i.i10_code)和/或COUNT(DISINCT+i.i10_code)，并删除ORDER+BY子句。|offset|length|style|CODE|462106|要在gems表中获取不在i10表中的i10_code，可以执行相同的操作，但将查询颠倒一下，这样gems就是“驱动”表。例如：|462107|SELECT+COUNT(DISTINCT+g.i10_code)+AS+cnt_diff
++FROM+gems_pcsi9+g
++LEFT
++JOIN+i10_descr+i
++++ON+i.i10_code+=+g.i10_code
+WHERE+i.i10_code+IS+NULL|462108|如果想要组合差异的数量，可以通过使两个查询成为内联视图来组合它们：|462109|SELECT+d.cnt_diff+%2B+e.cnt_diff+AS+total_diff+
++FROM+(
+++++++++SELECT+COUNT(DISTINCT+g.i10_code)+AS+cnt_diff
+++++++++++FROM+gems_pcsi9+g
++++++++++++LEFT
++++++++++++JOIN+i10_descr+i
++++++++++++++ON+i.i10_code+=+g.i10_code
+++++++++++WHERE+i.i10_code+IS+NULL
+++++++)+d
+CROSS+
++JOIN+(+
+++++++++SELECT+COUNT(DISTINCT+i.i10_code)+AS+cnt_diff
+++++++++++FROM+i10_descr+i
+++++++++++LEFT
+++++++++++JOIN+gems_pcsi9+g
+++++++++++++ON+g.i10_code+=+i.i10_code
++++++++++WHERE+g.i10_code+IS+NULL
+++++++)+e|462110|注意:+COUNT+aggregate将忽略空值。如果您还想“计算”i10_code为NULL值的行，则需要调整查询。如果您只想要一些不同的值，则可以使用COUNT(DISTINCT+)。一个COUNT()会给出很多行。如果有多个行具有相同的i10_code值，则这两个结果将不同。|462111|462112|为了获得代码的“比率”，假设在这一点上，“差异”无关紧要，您可以从每个表中获得代码的计数。这样做的查询可以在内联视图中使用：|462113|SELECT+d.cnt+/+e.cnt+AS+ratio_cnt_g_over_cnt_i
+++++,+d.cnt+++++++++AS+cnt_g
+++++,+e.cnt+++++++++AS+cnt_i
++FROM+(
+++++++++SELECT+COUNT(DISTINCT+g.i10_code)+AS+cnt
+++++++++++FROM+gems_pcsi9+g
+++++++)+d
+CROSS+
++JOIN+(+
+++++++++SELECT+COUNT(DISTINCT+i.i10_code)+AS+cnt
+++++++++++FROM+i10_descr+i
+++++++)+e|462114|entityMap^0|0|0|I|H|12|P|0|0|0|0|0|25|G|2O|7|3D|8|0|0|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|14|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|15|8|@$I|16|J|17|K|L]|$I|18|J|19|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|1A|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|1C|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|1D|8|@]|9|@]|A|$E|F]]|$1|U|3|V|5|6|7|1E|8|@$I|1F|J|1G|K|L]|$I|1H|J|1I|K|L]|$I|1J|J|1K|K|L]]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1L|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1M|8|@]|9|@]|A|$]]|$1|Z|3|10|5|D|7|1N|8|@]|9|@]|A|$E|F]]|$1|11|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]]|12|$]]

You could use an anti-join pattern to get a list of i10_code that exist in one table, but not the other. For example:

<pre><code>SELECT i.i10_code
 FROM i10_descr i
 LEFT
 JOIN gems_pcsi9 g
 ON g.i10_code = i.i10_code
 WHERE g.i10_code IS NULL
 ORDER BY i.i10_code
</code></pre>

If you just want a count, you could use <code>COUNT(i.i10_code)</code> and/or <code>COUNT(DISINCT i.i10_code)</code> in the SELECT list and remove the ORDER BY clause.

To get the i10_code in the gems table that aren't in the i10 table, you'd do the same thing but invert the query so that gems is the "driving" table. e.g.

<pre><code>SELECT COUNT(DISTINCT g.i10_code) AS cnt_diff
 FROM gems_pcsi9 g
 LEFT
 JOIN i10_descr i
 ON i.i10_code = g.i10_code
 WHERE i.i10_code IS NULL
</code></pre>

If you want to combine the number of differences, you can combine the two queries by making them inline views:

<pre><code>SELECT d.cnt_diff + e.cnt_diff AS total_diff 
 FROM (
 SELECT COUNT(DISTINCT g.i10_code) AS cnt_diff
 FROM gems_pcsi9 g
 LEFT
 JOIN i10_descr i
 ON i.i10_code = g.i10_code
 WHERE i.i10_code IS NULL
 ) d
 CROSS 
 JOIN ( 
 SELECT COUNT(DISTINCT i.i10_code) AS cnt_diff
 FROM i10_descr i
 LEFT
 JOIN gems_pcsi9 g
 ON g.i10_code = i.i10_code
 WHERE g.i10_code IS NULL
 ) e
</code></pre>

NOTE: the COUNT aggregate will omit NULL values. The query would need to be tweaked if you also wanted to "count" rows that had NULL values for i10_code. You'd use <code>COUNT(DISTINCT )</code> if you want just a number of distinct values that are different. A <code>COUNT()</code> would give a number of rows. These two results would be different if you had multiple rows with the same <code>i10_code</code> value.

<hr>

To get a "ratio" of codes, assuming that at this point, the "differences" don't matter, you get a count of codes from each table. The queries to do that could be used inline views:

<pre><code>SELECT d.cnt / e.cnt AS ratio_cnt_g_over_cnt_i
 , d.cnt AS cnt_g
 , e.cnt AS cnt_i
 FROM (
 SELECT COUNT(DISTINCT g.i10_code) AS cnt
 FROM gems_pcsi9 g
 ) d
 CROSS 
 JOIN ( 
 SELECT COUNT(DISTINCT i.i10_code) AS cnt
 FROM i10_descr i
 ) e
</code></pre>

I am trying to link two tables with similar column. I need to find out how many values differ from table1.column1 and table 2.column1: 

My current query:

<pre><code>SELECT i10_descr.i10_code, gems_pcsi9.i10_code
FROM i10_descr INNER JOIN gems_pcsi9 ON i10_descr.i10_code = gems_pcsi9.i10_code
ORDER BY i10_descr.i10_code;
</code></pre>

I know this query shows the matching codes of each table: I cannot figure out how to COUNT the missing/different codes in the tables. 

Also, I have to compute the ratio of codes.

Any help, tips, or direction is much appreciated.

Thanks

Combining an INNER JOIN query with Count of different values

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在尝试链接两个具有相似列的表。我需要找出与table1.column 1和table 2.column 1不同的值有多少：我现在的问题是：SELECT i10_descr.i10_code, gems_pcsi9.i10_codeFROM i10_descr INNER JOIN gems_pcsi9 ON i10...

问将内连接查询与不同值的计数组合在一起
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将内连接查询与不同值的计数组合在一起EN