mysql - 使用左联接，顺序和限制删除sql

DELETE tuser
  FROM tuser
 WHERE sts_seleksi IN (SELECT sts_seleksi
                         FROM tuser
                                LEFT JOIN ttes
                                       ON ttes.id_user=tuser.id_user
                        WHERE sts_seleksi='1'
                        ORDER BY tuser.sts_verifikasi DESC,
                                 ttes.nilai DESC,
                                 tuser.id_user ASC
                        LIMIT 20)

上面写着
错误#1235-此版本的MySQL尚不支持“LIMIT&IN/ALL/ANY/SOME subquery”
然后我试着

DELETE tuser
  FROM tuser
         INNER JOIN (SELECT sts_seleksi
                       FROM tuser
                              LEFT JOIN ttes
                                    ON ttes.id_user=tuser.id_user
                      WHERE sts_seleksi='1'
                      ORDER BY tuser.sts_verifikasi DESC,
                               ttes.nilai DESC,
                               tuser.id_user ASC
                      LIMIT 20)

它仍然说：
错误1248-每个派生表都必须有自己的别名
mydata查询=SELECT tuser.id_user,no_peserta,nisn,sts_verifikasi,sts_seleksi,ttes.nilai FROMtuserLEFT JOIN ttes ON ttes.id_user=tuser.id_user WHERE sts_seleksi='1' ORDER BY tuser.sts_verifikasi DESC, ttes.nilai DESC, tuser.id_user ASC

id_user no_peserta  nisn    sts_verifikasi  sts_seleksi nilai
6   U201601150006   2388881 1   1   NULL
7   U201601180007   129811  1   1   NULL
8   U201602090008   2788923 1   1   NULL
10  U201602090010   3434881 1   1   NULL
55  U201602100055   2221119 1   1   NULL
56  U201602100056   2111100 1   1   NULL
57  U201602100057   2999323 1   1   NULL
58  U201602100058   210091  1   1   NULL
59  U201602100059   230034  1   1   NULL
60  U201602100060   2009149 1   1   NULL
20  U201602090020   2111343 0   1   99.99
2   U201601100002   128899  0   1   30.00
3   U201601100003   238239  0   1   20.00
4   U201601110004   82371   0   1   20.00
12  U201602100012   433121  0   1   10.00
1   U201601100001   9012291 0   1   NULL
5   U201601150005   2332311 0   1   NULL
9   U201602090009   9231212 0   1   NULL
11  U201602090011   531211  0   1   NULL
13  U201602090013   3455532 0   1   NULL
14  U201602090014   234332  0   1   NULL
15  U201602090015   2339905 0   1   NULL
16  U201602090016   2211236 0   1   NULL
17  U201602090017   3234378 0   1   NULL
18  U201602090018   2211235 0   1   NULL
19  U201602090019   2145563 0   1   NULL
21  U201602090021   2754317 0   1   NULL

最佳答案

必须将INNER JOIN操作中使用的派生表别名为：

DELETE tuser
FROM tuser
INNER JOIN (
   SELECT id_user
   FROM tuser
   LEFT JOIN ttes ON ttes.id_user=tuser.id_user
   WHERE sts_seleksi='1'
   ORDER BY tuser.sts_verifikasi DESC, ttes.nilai DESC, tuser.id_user ASC
   LIMIT 20
) AS tuser2 ON tuser.id_user = tuser2.id_user

您还必须提供ON子句。您的示例数据表明应该在那里使用字段id_user。
Demo here