MySQL-如何获取行数

发布于 2024-12-01 09:46:29 字数 526 浏览 1 评论 0 原文

我在数据库中有这些数据：

+----+------------+---------+
| id | col1       | col2    |
+----+------------+---------+
|  1 |          3 |       2 |
|  2 |          2 |       3 |
|  3 |          4 |       5 |
+----+------------+---------+

我正在尝试执行一个查询，这将给我一个行数，其中有相同的数字。我知道其中之一的值（数字）。

在本例中，相同的数字是 2 和 3 （在 col1 和 col2 列中） - 我是尝试从数据库编号2（两行）获取。我总是有两个数字之一 - 在本例中是数字 3。

可以做这个查询吗？感谢您的帮助。

原文

I have these data in database:

+----+------------+---------+
| id | col1       | col2    |
+----+------------+---------+
|  1 |          3 |       2 |
|  2 |          2 |       3 |
|  3 |          4 |       5 |
+----+------------+---------+

I am trying to do a query, that will give me a count of rows, in which are the same numbers.
I know the value one of them (the numbers).

In this case, the same numbers are 2 and 3 (in the columns col1 and col2) - I am trying to get from database number 2 (two rows). I have available always one of two numbers - in this case number 3.

Is possible to do this query?
Thanks for help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

紧拥背影 2024-12-08 09:46:29

对于您指定的非常狭窄的场景，我会尝试以下操作：

select count(*)
from myTable mt1 
     inner join myTable mt2 on (mt1.col1 = mt2.col2 AND mt2.col1 = mt1.col2)
and mt1.col1 = 3 --here goes the known value

这只适用于您发布的数据。请告知我们以下情况的计数：

+----+------------+---------+
| id | col1       | col2    |
+----+------------+---------+
|  1 |          3 |       2 |
|  2 |          2 |       3 |
|  3 |          3 |       2 |
|  4 |          3 |       5 |
|  5 |          4 |       5 |
+----+------------+---------+

For the very narrow scenario you specified, I would try this:

select count(*)
from myTable mt1 
     inner join myTable mt2 on (mt1.col1 = mt2.col2 AND mt2.col1 = mt1.col2)
and mt1.col1 = 3 --here goes the known value

This only works for the data you posted. Please let us know the count for the following scenario:

+----+------------+---------+
| id | col1       | col2    |
+----+------------+---------+
|  1 |          3 |       2 |
|  2 |          2 |       3 |
|  3 |          3 |       2 |
|  4 |          3 |       5 |
|  5 |          4 |       5 |
+----+------------+---------+

回复收藏 0 原文

故笙诉离歌 2024-12-08 09:46:29

你看起来像吗

SELECT (col1+col2) , COUNT(*) AS num_rows
FROM table1
WHERE col1 =3 OR col2 =3
GROUP BY (col1+col2)
HAVING num_rows >1

Are you looking something like

SELECT (col1+col2) , COUNT(*) AS num_rows
FROM table1
WHERE col1 =3 OR col2 =3
GROUP BY (col1+col2)
HAVING num_rows >1

回复收藏 0 原文

哭泣的笑容 2024-12-08 09:46:29

通过此查询：

select count(*) count_rows
from table1 
where col1=3 or col2=3
group by concat(IF(col1<col2, col1, col2), ',', IF(col1<col2, col2, col1))

您将得到结果 2。

如果有其他输入，请替换 where 子句中查询中的 3。

concat(IF(col1 的值为 "2,3" code> 对应记录 1-2，（"4,5" 对应第三条记录）。

With this query:

select count(*) count_rows
from table1 
where col1=3 or col2=3
group by concat(IF(col1<col2, col1, col2), ',', IF(col1<col2, col2, col1))

you get the result 2.

Replace the 3 from the query in the where clause if there is another input.

The value for concat(IF(col1<col2, col1, col2), ',', IF(col1<col2, col2, col1)) would be "2,3" for records 1-2, (and "4,5" for the third record).