MySQL选择共享此字段值的所有值 [英] MySQL select all values which share this field's value

问题描述

我是 MySQL 的新手，所以用语言描述问题很困难，寻找解决方案也极具挑战性.

这个问题最好用视觉来解释:

我想选择(作为数组)共享相同 pair_id 的 exchange_pair_id.

所以在上面的数据中，我的 MySQL 查询将返回一个对象:

{ pair_id: 1, exchange_pair_id: [183, 1] }

我知道这是一个残酷的问题，但我不知道要搜索什么来解决这个问题.

为了清晰/简洁而更新:

+------------------+-------------+---------+|exchange_pair_id |exchange_id |pair_id |+---------+-------------+---------+|1 |3 |1 |+---------+-------------+---------+|183 |1 |1 |+---------+-------------+---------+|69 |2 |2 |+---------+-------------+---------+|12 |4 |2 |+---------+-------------+---------+|2 |3 |2 |+---------+-------------+---------+|3 |3 |3 |+---------+-------------+---------+

来自 Javascript MySQL 选择查询的期望输出:

<预><代码>[{ pair_id: 1, exchange_pair_id: [1, 183] },{ pair_id: 2, exchange_pair_id: [69, 12, 2] },{ pair_id: 3, exchange_pair_id: [3] }]

解决方案

我正在考虑这样的查询，但我在评论中等待您的回答.基本上，您使用 GROUP BY 在两个不同的列中获取每个 pair_id 的值:

SELECT pair_id, MIN(exhange_pair_id) AS id1, MAX(exchange_pair_id) AS id2从你的桌子GROUP BY pair_id;

更新版本:你能在你的数据上试试这个吗?在这种情况下，MYSQL 允许您使用分隔符 (,) 连接字段

SELECT pair_id, GROUP_CONCAT(exhange_pair_id) AS exhange_pair_id从你的桌子GROUP BY pair_id

I am new to MySQL so describing problems in words is difficult and searching for solutions is extremely challenging.

This problem is best explained visually:

I want to select (as an array) exchange_pair_id's that share the same pair_id.

So in the above data, my MySQL query would return an object:

{ pair_id: 1, exchange_pair_id: [183, 1] }

I am aware this is a butchered question, but I do not know the words to search to solve this problem.

Updated for clarity/brevity:

+------------------+-------------+---------+
| exchange_pair_id | exchange_id | pair_id |
+------------------+-------------+---------+
| 1                | 3           | 1       |
+------------------+-------------+---------+
| 183              | 1           | 1       |
+------------------+-------------+---------+
| 69               | 2           | 2       |
+------------------+-------------+---------+
| 12               | 4           | 2       |
+------------------+-------------+---------+
| 2                | 3           | 2       |
+------------------+-------------+---------+
| 3                | 3           | 3       |
+------------------+-------------+---------+

Desired output from a Javascript MySQL select query:

[
 { pair_id: 1, exchange_pair_id: [1, 183] },
 { pair_id: 2, exchange_pair_id: [69, 12, 2] },
 { pair_id: 3, exchange_pair_id: [3] }
]

解决方案

I am thinking a query like this , but I'm waiting your answer at comments. Basically, you use GROUP BY to obtain in two different columns the values for each pair_id:

SELECT pair_id, MIN(exhange_pair_id) AS id1, MAX(exchange_pair_id) AS id2
FROM yourtable
GROUP BY pair_id;

Update version: Can you try this please on your data? In this case MYSQL let you concat field using a separator (,)

SELECT pair_id, GROUP_CONCAT(exhange_pair_id) AS exhange_pair_id 
FROM yourtable
GROUP BY pair_id

这篇关于MySQL选择共享此字段值的所有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！