我有两个表:
user_favourites -> id, user_id, product_id
product -> id, title, bought
我需要显示9个结果->用户收藏夹加上其他产品，如果用户有少于9个收藏夹。
所以在我的页面上应该有9个产品显示。如果用户选择了9个最喜欢的产品，那么我将显示这9个最喜欢的产品，如果他选择的少于9个(至少说是5个)，那么我必须显示他的5个最喜欢的产品加上系统中评价最高的4个产品。
获取用户收藏，我有这样的查询:
select product_id from user_favourites where user_id = $userId
要获得最高评级的产品，我有这个查询:
select id, title, count(bought) from product group by id limit 9
所以，既然我想显示第一个最喜欢的产品+最受欢迎的，如果用户没有选择9，我可以以某种方式合并这两个查询成一个得到想要的结果吗?请不要出现问题，我需要删除重复的内容。如果用户选择了id为999的产品，但他也是最受欢迎的产品之一，我只需要显示一次。我还需要得到最多9个结果。
稍微扩展一下dirluca的工作
create table product
(
id int not null auto_increment primary key, -- as per op question and assumption
title varchar(255) not null,
bought int not null -- bought count assumption, denormalized but who cares for now
);
create table user_favourites
(
id int not null auto_increment primary key, -- as per op question and assumption
user_id int not null,
product_id int not null,
unique index (user_id,product_id)
-- FK RI left for developer
);
insert into product (title,bought) values ('He Bought 666',10),('hgdh',9),('dfhghd',800),('66dfhdf6',2),('He Bought this popular thing',900),('dfgh666',11);
insert into product (title,bought) values ('Rolling Stones',20),('hgdh',29),('4dfhghd',100),('366dfhdf6',2),('3dfghdgh666',0),('The Smiths',16);
insert into product (title,bought) values ('pork',123),('and',11),('beans',16),('tea',2),('fish',-9999),('kittens',13);
insert into user_favourites (user_id,product_id) values (1,1),(1,5);
select P.id, P.title, P.bought,
( CASE
WHEN uf.user_id IS NULL THEN 0 ELSE -1 END
) AS ordering
from product as P
left join user_favourites as UF on(P.id=UF.product_id)
where UF.user_id=1 OR UF.user_id IS NULL
order by ordering,bought desc
limit 9;
——在输入gui
时自然忽略排序列id title bought ordering
5 He Bought this popular thing 900 -1
1 He Bought 666 10 -1
3 dfhghd 800 0
13 pork 123 0
9 4dfhghd 100 0
8 hgdh 29 0
7 Rolling Stones 20 0
12 The Smiths 16 0
15 beans 16 0
我会选择join:
select P.id, P.title, P.bought
from product as P
left join user_favourites as UF on(P.id=UF.product_id)
where UF.user_id=$user_id OR UF.user_id IS NULL
order by user_id DESC
limit 9;;;
假设在product表中，每个产品有1行，购买的是一个整数，而不是像group by所暗示的那样，每个买家有1行
Here's fiddle