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 只取一条的要求。