英文:
Mysql how to split columns according to the condition?
问题
我有一个db fiddle，在这个fiddle中有一张表。在这张表中，有代码和它们的描述，它们都在同一个字段中。例如，如果代码是101，那么它的描述MDI Reset也在同一列中。这两者都由id 271标识，代码的id，和285是描述的id。
实际表格
+------------+--------+----------+---------------+
| id | did | source | code |
+------------+--------+----------+---------------+
| 101 | 271 | 101 | 200300B8 |
+------------+--------+----------+---------------+
| 102 | 271 | 102 | 200300B9 |
+------------+--------+----------+---------------+
| 151 | 285 | MDI Reset | 200300B8 |
+------------+--------+----------+---------------+
| 152 | 285 | Parameterization | 200300B9 |
+------------+--------+----------+---------------+
我想要的输出
------+----------+-------------------------------+
| source 1 | source 2 | code |
+------------+--------+----------+---------------+
| 101 | MDI Reset | 200300B8 |
+------------+--------+----------+---------------+
| 102 | Parameterization | 200300B9 |
+------------+--------+----------+---------------+
我想要将描述和它们的来源放在一起，就像上面的示例一样。
英文:
I have a db fiddle in which I have a table. In this table, there are codes and their descriptions which are on the same field. i.e. for example if the code is 101 then its description MDI Reset is also in the same column. These both are identified by an id 271, the id of the codes, and 285 is the id of the description.
Actual Table
+------------+--------+----------+---------------+
| id | did | source | code |
+------------+--------+----------+---------------+
| 101 | 271 | 101 | 200300B8 |
+------------+--------+----------+---------------+
| 102 | 271 | 102 | 200300B9 |
+------------+--------+----------+---------------+
| 151 | 285 | MDI Reset | 200300B8 |
+------------+--------+----------+---------------+
| 152 | 285 | Parameterization | 200300B9 |
+------------+--------+----------+---------------+
Output I want
------+----------+-------------------------------+
| source 1 | source 2 | code |
+------------+--------+----------+---------------+
| 101 | MDI Reset | 200300B8 |
+------------+--------+----------+---------------+
| 102 | Parameterization | 200300B9 |
+------------+--------+----------+---------------+
I want to put The description and its source side by side just like above.
答案1
得分: 2
你的表格设计有些笨拙，但我们可以通过自连接来处理你的需求：
SELECT e1.source AS source1, e2.source AS source2, e1.code
FROM events e1
INNER JOIN events e2
ON e2.code = e1.code
WHERE
e1.source REGEXP '^[0-9]+$' AND
e2.source NOT REGEXP '^[0-9]+$';
这里的连接逻辑匹配具有相同代码的记录对。在这对中，第一条记录的源具有纯数字，而第二条记录的源不具有纯数字。
这是你更新后的 SQL Fiddle 链接。
英文:
Your table design is awkward, but we can handle your requirement via a self join:
<!-- language: sql -->
SELECT e1.source AS source1, e2.source AS source2, e1.code
FROM events e1
INNER JOIN events e2
ON e2.code = e1.code
WHERE
e1.source REGEXP '^[0-9]+$' AND
e2.source NOT REGEXP '^[0-9]+$';
The joining logic here matches pairs of records sharing the same code. The first record in the pair has a pure number as its source, while the second record does not have a pure number as its source.
Here is a link to your updated SQL Fiddle.
答案2
得分: 0
select dmbmbs as 'source 1', bm as 'source 2', mbbm as 'code' from sp_bmzd
英文:
You can simply call the columns you specifically want to see and rename the titles of the columns within the statement.
select dmbmbs as 'source 1', bm as 'source 2', mbbm as 'code' from sp_bmzd
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论