SELECT t1.gname, t1.score, t1.pics FROM game t1 INNER JOIN ( SELECT tid, MAX(score) AS max_score FROM game WHERE score != '评分过少' GROUP BY tid ) t2 ON t1.tid = t2.tid AND t1.score = t2.max_score INNER JOIN gameType t3 ON t1.tid = t3.tid WHERE t3.tname IN ( SELECT tname FROM `user` u INNER JOIN stock s ON u.uid = s.uid INNER JOIN game g ON s.gid = g.gid INNER JOIN gameType t ON g.tid = t.tid WHERE u.uid = (SELECT uid FROM `user` WHERE uname = 'admin') GROUP BY tname ) ORDER BY t1.gid; 再次将以上sql优化使得第一个sql中查出的每一个tname只能在第二个sql中查出gid最小的那个数据

以下是优化后的 SQL 查询语句：

SELECT t1.gname, t1.score, t1.pics
FROM game t1
INNER JOIN (
    SELECT tid, MIN(gid) AS min_gid
    FROM game
    WHERE score != '评分过少'
    GROUP BY tid
) t2 ON t1.tid = t2.tid AND t1.gid = t2.min_gid
INNER JOIN gameType t3 ON t1.tid = t3.tid
WHERE t3.tname IN (
    SELECT tname
    FROM `user` u
    INNER JOIN stock s ON u.uid = s.uid
    INNER JOIN game g ON s.gid = g.gid
    INNER JOIN gameType t ON g.tid = t.tid
    WHERE u.uid = (SELECT uid FROM `user` WHERE uname = 'admin')
    GROUP BY tname
)
ORDER BY t1.gid;

在优化后的查询语句中，我使用了 MIN(gid) 函数来筛选出每个 tid 对应的最小 gid。这样可以确保在第一个查询中，每个 tname 只能返回 gid 最小的那个数据。

相关问题

SELECT t1.gname,t1.tid,t1.gid, t1.score, t1.pics FROM game t1 INNER JOIN ( SELECT tid, MAX(score) AS max_score FROM game WHERE score != '评分过少' GROUP BY tid ) t2 ON t1.tid = t2.tid AND t1.score = t2.max_score INNER JOIN gameType t3 ON t1.tid = t3.tid WHERE t3.tname IN ( SELECT tname FROM user u INNER JOIN stock s ON u.uid = s.uid INNER JOIN game g ON s.gid = g.gid INNER JOIN gameType t ON g.tid = t.tid WHERE u.uid = (SELECT uid FROM user WHERE uname = 'admin') GROUP BY tname ) ORDER BY t1.gid;优化以上sql使其相同的tid只取一条

以下是优化后的 SQL 查询语句：

SELECT t1.gname, t1.tid, t1.gid, t1.score, t1.pics
FROM (
    SELECT gname, tid, gid, score, pics,
        ROW_NUMBER() OVER (PARTITION BY tid ORDER BY gid) AS rn
    FROM game
    WHERE score != '评分过少'
) t1
INNER JOIN (
    SELECT tid, MAX(score) AS max_score
    FROM game
    WHERE score != '评分过少'
    GROUP BY tid
) t2 ON t1.tid = t2.tid AND t1.score = t2.max_score
INNER JOIN gameType t3 ON t1.tid = t3.tid
WHERE t3.tname IN (
    SELECT tname
    FROM `user` u
    INNER JOIN stock s ON u.uid = s.uid
    INNER JOIN game g ON s.gid = g.gid
    INNER JOIN gameType t ON g.tid = t.tid
    WHERE u.uid = (SELECT uid FROM `user` WHERE uname = 'admin')
    GROUP BY tname
)
AND t1.rn = 1
ORDER BY t1.gid;

在优化后的查询语句中，我使用了 ROW_NUMBER() 函数来为每个 tid 分配一个行号，然后在主查询中添加条件 t1.rn = 1 来确保只选择每个 tid 的第一条数据。这样可以实现相同的 tid 只取一条的要求。

SELECT t1.gname, t1.score, t1.pics FROM game t1 INNER JOIN ( SELECT tid, MAX(score) AS max_score FROM game WHERE score != '评分过少' GROUP BY tid ) t2 ON t1.tid = t2.tid AND t1.score = t2.max_score INNER JOIN gameType t3 ON t1.tid = t3.tid WHERE t3.tname IN ( SELECT tname FROM `user` u INNER JOIN stock s ON u.uid = s.uid INNER JOIN game g ON s.gid = g.gid INNER JOIN gameType t ON g.tid = t.tid WHERE u.uid = (SELECT uid FROM `user` WHERE uname = 'admin') GROUP BY tname ) ORDER BY t1.gid;将以上sql优化，使得查出来的数据，相同的tname只留最小的gid的那一个数据

以下是优化后的 SQL 查询语句：

SELECT t1.gname, t1.score, t1.pics
FROM game t1
INNER JOIN (
    SELECT tid, MAX(score) AS max_score, MIN(gid) AS min_gid
    FROM game
    WHERE score != '评分过少'
    GROUP BY tid
) t2 ON t1.tid = t2.tid AND t1.score = t2.max_score AND t1.gid = t2.min_gid
INNER JOIN gameType t3 ON t1.tid = t3.tid
WHERE t3.tname IN (
    SELECT tname
    FROM `user` u
    INNER JOIN stock s ON u.uid = s.uid
    INNER JOIN game g ON s.gid = g.gid
    INNER JOIN gameType t ON g.tid = t.tid
    WHERE u.uid = (SELECT uid FROM `user` WHERE uname = 'admin')
    GROUP BY tname
)
ORDER BY t1.gid;

在优化后的查询语句中，我添加了一个子查询来获取每个 tid 对应的最小 gid，然后在主查询中加入条件 t1.gid = t2.min_gid 来确保只保留相同 tname 下的最小 gid 的数据。这样可以实现相同的 tname 只留下最小 gid 的要求。