Mysql查询 - 在表格中选择最高排名/有序主题（加入）

Scenario:

We have 5 users. (users table) Each user has up to 10 imgs. (image table) These 10 images can be ordered 1 – 10. (image table) Each img can be listed in multiple categories (say there are 5 categories – birds, bees, bunnies, brains, belugas (category table connected to img table via table that stores img_ids and category_ids)

我们有5位用户。 (用户表)每个用户最多有10个imgs。 (图像表)这10个图像可以订购1 - 10.(图像表)每个img可以列出多个类别(比如有5个类别 - 鸟类,蜜蜂,兔子,大脑,白鲸(通过img表连接的类别表)存储img_ids和category_ids的表)

In searching through the categories, say someone chooses bees. The search should find the images in that category that is listed CLOSEST to the #1 img for all users. So if each user has 3 images in the bees category, ordered as numbers 4, 7 & 9, the search should show the 4th as its closest to the number 1.

在搜索类别时,请说有人选择蜜蜂。搜索应该在所有用户的#1 img中找到列出CLOSEST的类别中的图像。因此,如果每个用户在蜜蜂类别中有3个图像,按照数字4,7和9排序,则搜索应显示第4个最接近数字1。

The results I keep getting are all over the place and almost seems like it is choosing the images via WHEN they were added to the DB.

我不断得到的结果到处都是,几乎看起来它是通过将它们添加到数据库时选择图像。

SELECT i.img_name, i.ordered, a.user_name, c.keyword, c.cat_id
FROM images AS i JOIN artists AS a USING (user_id) 
JOIN img_cat_table AS im USING ( img_id )
JOIN catkeys AS c USING (cat_id)
WHERE ( cat_id = 3) // THE BEES ID # 
    GROUP BY user_id ORDER BY user_name DESC

3 个解决方案

#1

I'm also not sure if you want to show all of the relevant images in the right order, or only the top one. Assuming that it is the latter situation, you will need to join to a subquery or view that returns the min rank for each user, category:

我也不确定你是想以正确的顺序显示所有相关的图像,还是只显示最顶层的图像。假设它是后一种情况,您将需要加入子查询或视图,返回每个用户的最低排名,类别:

SELECT i.img_name, i.ordered, a.user_name, c.keyword, c.cat_id
FROM images AS i JOIN artists AS a USING (user_id) 
JOIN img_cat_table AS im USING ( img_id )
JOIN catkeys AS c USING (cat_id)
JOIN (
SELECT user_id, min(img_rank) img_rank
FROM images AS i 
JOIN artists AS a on i.user_id = a.user_id
JOIN img_cat_table AS im on im.img_id = i.img_id
JOIN catkeys AS c on c.cat_id = i.cat_id
WHERE ( cat_id = 3) ) x on x.user_id = a.user_id and x.img_rank = img_rank
WHERE c.cat_id = 3

I'm not sure what the name of the column that holds the image ranking is. I called it img_rank. Hopefully this will give you the idea

我不确定保持图像排名的列的名称是什么。我叫它img_rank。希望这会给你这个想法

#2

though if you can post the table structure and data, that will be great but Here is what I haved tried

虽然如果你可以发布表格结构和数据,这将是伟大的,但这是我尝试过的

SELECT i.img_name, i.ordered, a.user_name, c.keyword, c.cat_id
from (
select img_name, ordered, img_id, user_id from 
images 
group by user_id
order by user_img ) as i
JOIN artists AS a USING (user_id) 
JOIN img_cat_table AS im USING ( img_id )
JOIN catkeys AS c USING (cat_id)
WHERE ( cat_id = 3) // THE BEES ID # 

#3

Try removing DESC from your ORDER BY clause.

尝试从ORDER BY子句中删除DESC。