计算mysql中两个表之间的公共行数

so here's my question...

这是我的问题…

Hi have two tables in mysql, called go_H and go_J, both looking like this:

在mysql中有两个表，叫做go_H和go_J，它们都是这样的:

go_H

+---------------+------------+
| gene          | GoCode     |
+---------------+------------+
| DNAJC25-GNG10 | GO:0004871 |
| DNAJC25-GNG10 | GO:0005834 |
| DNAJC25-GNG10 | GO:0007186 |
| LOC100509620  | GO:0005215 |
| LOC100509620  | GO:0006810 |
| LOC100509620  | GO:0016021 |
| PPIAL4E       | GO:0000413 |
| PPIAL4E       | GO:0003755 |
| PPIAL4E       | GO:0005737 |
| PPIAL4E       | GO:0006457 |
| LOC105371242  | GO:0000413 |
+----------------------------+

go_J

+------------+
| GoCode     |
+------------+
| GO:0007254 |
| GO:0007256 |
| GO:0007257 |
| GO:0042655 |
| GO:0043506 |
| GO:0043507 |
| GO:0043508 |
| GO:0046328 |
| GO:0046329 |
| GO:0046330 |
+------------+

Basically what I want to achieve is to see what GoCode values from go_J appear in GoCode from Go_H, and count them, so as I get a total number o GO ids that are present in both tables.

基本上，我想要实现的是查看go_J的GoCode值在Go_H的GoCode中出现，并对它们进行计数，这样当我得到两个表中出现的o GO id总数时。

I have come to select go_H.GoCode and go_J.GoCode, but I don't know how to compare them to find common rows and then count them...

我来选择go_H。GoCode go_J。GoCode，但我不知道如何比较它们来查找公共行然后数它们…

Any help?

任何帮助吗?

4 个解决方案

#1

Hope this helps.

希望这个有帮助。

select count(*) from go_J j join  go_H h on h.GoCode=j.GoCode;

#2

SELECT COUNT(*) FROM go_H
INNER JOIN go_J USING GoCode

INNER JOIN => Rows that are in both tables based on the join column (GoCode)

内连接=>行，基于连接列(GoCode)

Alternative:

选择:

SELECT COUNT(*) FROM go_H h
INNER JOIN go_J ON j.GoCode = h.GoCode

Check this answer out to learn about joins:

查看以下答案了解连接:

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

内连接、左连接、右连接和全连接的区别是什么?

#3

To find how many rows are similar between 2 table

找出两个表之间有多少行是相似的

SELECT  COUNT(*) totalCount
FROM    go_H a 
        INNER JOIN go_J b
            ON a.GoCode = b.GoCode

To find how many rows from go_H are not in go_J

查找go_H中有多少行不在go_J中

SELECT  COUNT(*) totalCount
FROM    go_H a 
        LEFT JOIN go_J b
            ON a.GoCode = b.GoCode
WHERE   b.GoCode IS NULL

To find how many rows from go_J are not in go_H

查找go_J中有多少行不在go_H中

SELECT  COUNT(*) totalCount
FROM    go_J a 
        LEFT JOIN go_H b
            ON a.GoCode = b.GoCode 
WHERE   b.GoCode IS NULL

#4

You can achieve this just in SQL by running a query similar to this:

您可以通过运行类似于以下的查询来实现这一点:

SELECT
 *,
count (GoCode)
 FROM (    
SELECT GoCode FROM go_H
    UNION
    SELECT GoCode FROM go_H )a
group by a.gocode

This will provide you a table with each code in a column and then the amount of times it is present across both tables

这将为您提供一个包含列中的每一个代码的表，以及它在两个表中出现的次数

An alternative with PHP would be get both tables into an array by using PDO and use in_array to check

使用PHP的另一种方法是使用PDO和in_array来检查两个表

<?php
foreach ($go_H as $GoCode) {
    if (in_array($GoCode, $go_J)) {
        // handle codes in both tables
    }
}

This is not the most efficient method but it will yeild results.

这不是最有效的方法，但它将产生结果。

#1