查询同一表以创建搜索屏幕的订购

Question

我有一个表用户和recentsearch以及一个允许用户查找其他用户的屏幕。服务器返回用户数组，而无需考虑朋友和最近的搜索，我想包括IT客户端（客户端是指运行这些查询的iOS应用程序）。 我希望朋友和恢复搜索从服务器返回的东西中获得优先级。

我有点卡住。我不确定该怎么做。我在下面进行了查询，但这不起作用，因为它要求所有用户将设置为1的flag ShowonSearchScreen设置为1，但这并不总是适用于朋友和最近的搜索。

flag showonsearchscreen 为服务器返回的用户设置为true。这意味着，用户可以将标志设置为1，也可以成为朋友。

这些是我的表格：

create table User (
    userUuid text not null primary key,
    username text not null,
    showOnSearchScreen int not null,
    friends int not null
);

create table RecentSearch (
    userUuid text not null primary key,
    foreign key (userUuid) references User (userUuid) on delete cascade
);

这是我的查询，它不起作用（假设用户在某处搜索用“ g”的用户）：

select *,
       case
           when exists(select 1
                       from User CustomUser
                       where User.userUuid = CustomUser.userUuid
                         and CustomUser.friends = 1
                         and CustomUser.username like '%g%') then 2
           else 0 end as friendScore,
       case
           when exists(select 1
                       from RecentSearch
                                join User CustomUser on RecentSearch.userUuid = CustomUser.userUuid and
                                                        User.userUuid = RecentSearch.userUuid
                       where CustomUser.username like '%g%') then 1
           else 0 end as recentSearchScore
from User
-- This is wrong...
where showOnSearchScreen = 1
order by friendScore + recentSearchScore;

是否必须通过外部连接来解决这？我有点困惑。

因此，关键点：

• 服务器将user s发送给客户端，客户端将它们upsert在数据库中，并将flag showonSearchScreen设置为1，
• 查询应至少返回所有使用该标志的用户，将所有
• 用户设置为1个我希望是朋友，并且是朋友，并且具有与输入相似的用户名（我猜是withext％的效果都足够好）以优先于从服务器返回的用户，
• 适用于user s recentsearch ，如果用户从recentsearch加入具有相似的用户名，请优先于从服务器返回的用户（但是朋友应在顶部显示）

Answer 1

IIUC-考虑Union查询，该查询可能会使用分数来解决您的子弹点以确定优先级或订购：

SELECT userUuid, username, 3 AS score
FROM User
WHERE friends = 1 
  AND username LIKE '%g%'

UNION

SELECT r.userUuid, u.username, 2 AS score
FROM RecentSearch r
 JOIN User u ON r.userUuid = u.userUuid 
WHERE u.username LIKE '%g%'

UNION

SELECT userUuid, username, 1 AS score
FROM User 
WHERE showOnSearchScreen = 1 
  AND username LIKE '%g%'