mysql查询的问题

发布于 2024-11-26 05:45:38 字数 1225 浏览 3 评论 0原文

我有一个名为 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`)
)

，最后我有一张叫做朋友的桌子，这就是表的样子，

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`)
)

所以基本上如果 userA 关注 userB ，那么一行将添加到 Friends 表中，属性 user_id1 是 userA ， user_id2 是 userB 。

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

因此，如果我们有 3 个用户

jack
jason
john，

如果用户 Chris（关注 jason）在搜索框中输入字符串“ja”，则查询将以以下顺序抓取用户列表：jason,jack。因为杰森后面跟着克里斯。

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

你们知道吗？

多谢

原文

i have a table called users
this what the table look like

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`)
)

and finally i have a table called friends,
this what the table look like

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`)
)

so basically if userA is following userB , then a row will be added to the friends table, with the attribute user_id1 is userA and user_id2 is userB.

im trying to write a mysql query for a searchbox. the user will enter a string and the query will crawl a list of users that include that string but the people that the user is following need to be displayed first.

so if we have 3 users

jack
jason
john

if the user Chris (who's following jason) enters in the searchbox the string 'ja', the query will crawl the list of users with the following order jason,jack. since jason is followed by chris.

from my understanding , i think it might a group by problem, i tried different queries but i couldnt get the needed results

do you guys have any idea ?

thanks a lot

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

星光不落少年眉 2024-12-03 05:45:38

你必须做一些排序的技巧，所以在临时字段中友谊得到 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 -条款

You have to do a trick for sorting, so friendships get a 0 and non-friendships get a 1 in a temporary field and then we sort ascending for this field and as second we sort by username

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

@thanks to scwagner for pointing me to extend JOIN-clause

回复收藏 0 原文

~没有更多了~