英文:
SQL Query to sum ids whenever the rows are reverse
问题
以下是翻译好的内容：
my_table==>
| id | col1 | col2 |
|---|---|---|
| 1 | a | b |
| 2 | b | a |
| 3 | b | a |
| 4 | c | d |
| 5 | d | c |
| 6 | x | y |
Expected Output==>
| id | col1 | col2 |
|---|---|---|
| 6 | a | b |
| 9 | c | d |
| 6 | x | y |
> 所以逻辑是每当col1和col2与前一个行的col2和col1相反时，添加id的总和！
SELECT t1.id + t2.id AS id_sum, t1.col1, t1.col2
FROM my_table t1
JOIN my_table t2 ON t1.col1 = REVERSE(t2.col2) AND t1.col2 = REVERSE(t2.col1)
WHERE t1.id < t2.id
ORDER BY id_sum;
英文:
my_table==>
| id | col1 | col2 |
|---|---|---|
| 1 | a | b |
| 2 | b | a |
| 3 | b | a |
| 4 | c | d |
| 5 | d | c |
| 6 | x | y |
Expected Output==>
| id | col1 | col2 |
|---|---|---|
| 6 | a | b |
| 9 | c | d |
| 6 | x | y |
> So the logic is whenever there is col1 and col2 is reversed for previous add the ids!
SELECT t1.id + t2.id AS id_sum, t1.col1, t1.col2
FROM my_table t1
JOIN my_table t2 ON t1.col1 = REVERSE(t2.col2) AND t1.col2 = REVERSE(t2.col1)
WHERE t1.id < t2.id
ORDER BY id_sum;
答案1
得分: 0
这是你想要的吗？
选择 sum(id) 作为 id，least(col1, col2) 作为 col1，greatest(col1, col2) 作为 col2
从 mytable t
按 least(col1, col2) 和 greatest(col1, col2) 分组
这个想法是取两列中的最小/最大值，并使用该信息对数据集进行分组。
这将为你的示例数据生成期望的结果。
英文:
Is this what you want?
select sum(id) as id, least(col1, col2) col1, greatest(col1, col2) col2
from mytable t
group by least(col1, col2), greatest(col1, col2)
The idea is to take the least/greatest value amongst the two columns, and use that information to group the dataset.
This would produce your expected results for your sample data.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论