英文:
Get result set from a single column table in sql
问题
我有一个只有一个列的国家表，其中包含以下值
印度
巴基斯坦
澳大利亚
斯里兰卡
我的结果应该是这样的
印度 vs 巴基斯坦
印度 vs 澳大利亚
印度 vs 斯里兰卡
巴基斯坦 vs 斯里兰卡
巴基斯坦 vs 澳大利亚
澳大利亚 vs 斯里兰卡
我们如何通过自连接来实现这个？
尝试使用自连接，但无法知道如何从列中检索第一个值和第二个值。
英文:
I have a table country with single column that has the below values
India
Pakistan
Australia
Srilanka
My result should be like this
India vs Pakistan
India vs Australia
India vs Srilanka
Pakistan vs Srilanka
Pakistan vs Australia
Australia vs Srilanka
How do we achieve this with self join?
Tried with self join but unable to know how to retrieve first value and second value from column
答案1
得分: 2
使用自连接并加入条件以确保一个国家不会与自己比赛：
CREATE TABLE counties (
name varchar(50)
);
INSERT INTO counties (name)
VALUES ('India'), ('Pakistan'), ('Australia'), ('Srilanka');
SELECT
CONCAT(c1.name, ' vs ', c2.name) AS matchup
FROM counties c1
JOIN counties c2 ON c1.name < c2.name
ORDER BY
matchup;
| matchup |
|---|
| Australia vs India |
| Australia vs Pakistan |
| Australia vs Srilanka |
| India vs Pakistan |
| India vs Srilanka |
| Pakistan vs Srilanka |
更多组合可以通过在条件中使用 < > 来实现：
SELECT
CONCAT(c1.name, ' vs ', c2.name) AS matchup
FROM counties c1
JOIN counties c2 ON c1.name <> c2.name
ORDER BY
matchup;
| matchup |
|---|
| Australia vs India |
| Australia vs Pakistan |
| Australia vs Srilanka |
| India vs Australia |
| India vs Pakistan |
| India vs Srilanka |
| Pakistan vs Australia |
| Pakistan vs India |
| Pakistan vs Srilanka |
| Srilanka vs Australia |
| Srilanka vs India |
| Srilanka vs Pakistan |
英文:
Use the self join with a condition so that a country cannot play against itself:
CREATE TABLE counties (
name varchar(50)
);
INSERT INTO counties (name)
VALUES ('India'), ('Pakistan'), ('Australia'), ('Srilanka');
SELECT
CONCAT(c1.name, ' vs ', c2.name) AS matchup
FROM counties c1
JOIN counties c2 ON c1.name < c2.name
ORDER BY
matchup;
| matchup |
|---|
| Australia vs India |
| Australia vs Pakistan |
| Australia vs Srilanka |
| India vs Pakistan |
| India vs Srilanka |
| Pakistan vs Srilanka |
More combinations can be achieved with <> in the condition:
SELECT
CONCAT(c1.name, ' vs ', c2.name) AS matchup
FROM counties c1
JOIN counties c2 ON c1.name <> c2.name
ORDER BY
matchup;
| matchup |
|---|
| Australia vs India |
| Australia vs Pakistan |
| Australia vs Srilanka |
| India vs Australia |
| India vs Pakistan |
| India vs Srilanka |
| Pakistan vs Australia |
| Pakistan vs India |
| Pakistan vs Srilanka |
| Srilanka vs Australia |
| Srilanka vs India |
| Srilanka vs Pakistan |
答案2
得分: 0
你可以使用CROSS JOIN和WHERE c1.name < c2.name：
SELECT CONCAT(c1.name, ' vs ', c2.name) AS Combination
FROM counties c1
CROSS JOIN counties c2
WHERE c1.name < c2.name
ORDER BY Combination
英文:
You can use CROSS JOIN, and WHERE c1.name < c2.name:
SELECT CONCAT(c1.name, ' vs ', c2.name) AS Combination
FROM counties c1
CROSS JOIN counties c2
WHERE c1.name < c2.name
ORDER BY Combination
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论