java - 基于第一次排序，再次排序 Sqlite vs Java (Android)

Question

组合多个Sqlite查询

数据库结构

CREATE TABLE `userdetails` ( `_id` INTEGER NOT NULL, `username` TEXT NOT NULL, `code` TEXT NOT NULL, `usertype` TEXT NOT NULL, `userID` TEXT NOT NULL, PRIMARY KEY(`_id`) )

_id   username code  usertype  userID  "1"   "user1"  "ERE" "type1"   "1"  "2"   "user2"  "ERE" "type2"   "2"  "3"   "user1"  "ERE" "type3"   "3"  "4"   "user1"  "ERE" "type4"   "4"  "5"   "user2"  "ERE" "type1"   "5"  "6"   "user2"  "ERE" "type2"   "6"  "7"   "user2"  "ERE" "type4"   "7"  "8"   "user2"  "ERE" "type3"   "8"  "9"   "user2"  "ERE" "type5"   "9"  "10"  "user2"  "ERE" "type1"   "10"  "11"  "user2"  "ERE" "type2"   "11"  

I'm writing this in SQLite Query in Android Application

output should beHighest userID value will have some user type, all that usertype should be there, followed by next highest value.... see example

"11" "user2" "ERE" "type2" "11""6"  "user2" "ERE" "type2" "6"      "2"  "user2" "ERE" "type2" "2"  "10" "user2" "ERE" "type1" "10"  "5"  "user2" "ERE" "type1" "5"  "1"  "user1" "ERE" "type1" "1"  "9"  "user2" "ERE" "type5" "9"  "8"  "user2" "ERE" "type3" "8"  "3"  "user1" "ERE" "type3" "3"  "7"  "user2" "ERE" "type4" "7"  "4"  "user1" "ERE" "type4" "4"  

I tried this but didn't work as expected

SELECT *  FROM userdetails  ORDER BY usertype <> (SELECT usertype FROM userdetails ORDER BY _id DESC LIMIT 1), _id DESC;

是否值得编写此查询，或者最好在 andoird java 文件中执行此逻辑？

Answer 1

要计算每种类型的最高 userID 值，请使用如下查询:

SELECT usertype,
       MAX(userID) AS highestID
FROM userdetails
GROUP BY usertype;

然后您可以将其与原始表连接起来，以便该值可用于每一行:

SELECT userdetails.*
FROM userdetails
JOIN (SELECT usertype,
             MAX(userID) AS highestIDperType
      FROM userdetails
      GROUP BY usertype
     ) USING (usertype)
ORDER BY highestIDperType DESC,
         userID DESC;