MySQL查询问题

Question

我有一个名为“users”的表格，它的样子如下：

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL,
  `password` varchar(60) NOT NULL,
  `email` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) 

最后，我有一张名为“friends”的表格，它的样子如下：

CREATE TABLE `friends` (
  `friendship_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id1` bigint(20) unsigned NOT NULL,
  `user_id2` bigint(20) unsigned NOT NULL,
  `time_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`friendship_id`),
  KEY `user_id1` (`user_id1`),
  KEY `user_id2` (`user_id2`),
  CONSTRAINT `friends_ibfk_1` FOREIGN KEY (`user_id1`) REFERENCES `users` (`id`),
  CONSTRAINT `friends_ibfk_2` FOREIGN KEY (`user_id2`) REFERENCES `users` (`id`)
)

基本上，如果用户A正在关注用户B，则将向好友表添加一行，其中属性user_id1是用户A，而user_id2是用户B。

我正在尝试为搜索框编写MySQL查询。用户将输入一个字符串，查询将爬取包含该字符串的用户列表，但需要首先显示用户正在关注的人。

因此，如果我们有3个用户：Jack、Jason和John。如果正在关注Jason的Chris在搜索框中输入字符串“ja”，则查询将按以下顺序爬取用户列表：Jason、Jack。因为Chris正在关注Jason。

据我的理解，这可能是一个分组问题。我尝试了不同的查询，但无法获得所需的结果。

你们有什么想法吗？

非常感谢。

- Don Gorgon

---

我不太明白你的问题，你是想改变结果的顺序还是需要将已经存在的友谊作为第一行呈现？请展示你运行获取用户的查询语句。 - Tim

我只想从数据库中爬取用户，但我希望先显示朋友，然后再显示非朋友。 - Don Gorgon

Answer 1

您需要进行一个技巧性的排序，使得朋友关系赋值为0，非朋友关系赋值为1，并在临时字段中进行排序，首先按照该字段进行升序排序，其次按照用户名进行排序。

SELECT x.username
FROM users x LEFT JOIN friends y ON x.id=y.user_id2 AND y.user_id1=$LOGGED_IN_USER
WHERE LOWER(x.username) LIKE 'ja%'
ORDER BY CASE WHEN y.user_id2 IS NULL THEN 1 ELSE 0 END,x.username

感谢scwagner指导我扩展JOIN子句。

- rabudde

---

在 JOIN 中一定要包括 "and y_user_id1 = $(LoggedInUserId)"，否则如果有人有一个匹配的名字，那么他们将被排到顶部。 - scwagner

感谢@rabudde的回答。 我尝试了您的查询，但它没有起作用。 这是错误： ERROR 1064（42000）：您的SQL语法有误；请检查与您的MySQL服务器版本相对应的手册，以获取正确的语法使用方式，例如'WHERE LOWER(t.username) LIKE 'ha%' ORDER BY CASE WHEN y.user_id2 IS NULL THEN 1'在第1行。 - Don Gorgon

抱歉，在ORDER BY子句中有一个AS order_field，请将其删除（或查看编辑后的答案）。 - rabudde

@rabudde 谢谢伙计，现在它运行得很好。 感谢您的帮助 干杯 - Don Gorgon