mariadb 组合两个选择SQL查询

insrf1ej 于 2022-11-08 发布在其他

关注(0)|答案(1)|浏览(124)

我想知道是否有可能将这两个SQL查询合并为一个唯一的查询。

SELECT DISTINCT Pseudo FROM Games 
WHERE Time_Format IS NOT NULL 
AND Score IS NOT NULL 
AND Result = 'Win' ORDER BY Date DESC

    SELECT ID_Game,Time_Format,Time,Score,Date 
FROM Games 
WHERE Time_Format IS NOT NULL 
AND Score IS NOT NULL 
AND Result = 'Win' 
AND Pseudo = to the previous result of the other request
ORDER BY Time ASC,Score DESC LIMIT 1

以下是Games表中的数据示例

INSERT INTO Games(ID_Game, Pseudo, Time_Format,Time, Score,Statut, Result , Date, Date_Fin) VALUES (1,'bob','00:22',22,125,'completed','Win','2022-06-22 13:47:43','2022-06-22 13:47:52'),
(1,'bob2','00:52',52,369,'completed','Lose','2022-06-22 13:40:22','2022-06-22 13:40:22'),
(1,'bob3','00:36',36,650,'completed','Win','2022-06-22 13:22:22','2022-06-22 13:22:22'),
(1,'bob4','00:40',40,378,'completed','Win','2022-06-22 13:12:22','2022-06-22 13:12:22');

我想知道的是，在赢得的比赛中取得最好的成绩，但我不想在结果中出现两个相同的昵称
提前感谢您的帮助：）

mariadb

来源：https://stackoverflow.com/questions/72715596/combine-two-select-sql-queries

1条答案

按热度按时间

bvk5enib1#

您应该使用CTE或subquery。我更喜欢CTE：

WITH cte AS (
    SELECT DISTINCT Pseudo
    FROM Games 
    WHERE Time_Format IS NOT NULL 
        AND Score IS NOT NULL 
        AND Result = 'Win'
    --ORDER BY Date DESC
)

SELECT ID_Game,Time_Format,Time,Score,Date 
FROM Games 
WHERE Time_Format IS NOT NULL 
    AND Score IS NOT NULL 
    AND Resultat = 'Win' 
    AND Pseudo = (SELECT pseudo FROM cte)
ORDER BY Time ASC, Score DESC
LIMIT 1;

，但如果CTE不是选项，则存在包含子查询的版本：

SELECT ID_Game,Time_Format,Time,Score,Date 
FROM Games 
WHERE Time_Format IS NOT NULL 
    AND Score IS NOT NULL 
    AND Resultat = 'Win' 
    AND Pseudo = (
        SELECT DISTINCT Pseudo
        FROM Games 
        WHERE Time_Format IS NOT NULL 
            AND Score IS NOT NULL 
            AND Result = 'Win'
        --ORDER BY Date DESC
    )
ORDER BY Time ASC, Score DESC
LIMIT 1;

如果CTE返回多个值，只需将AND Pseudo = (SELECT pseudo FROM cte)替换为AND Pseudo IN (SELECT pseudo FROM cte)

赞(0）回复(0）举报 2022-11-08

我来回答

mariadb 组合两个选择SQL查询

1条答案

相关问题

热门标签

最新问答