我有兩個表如下：
create table gift_certificate
(
id int auto_increment
primary key,
name varchar(64) not null,
description mediumtext not null,
price decimal default 0 not null,
duration int default 1 not null,
create_date datetime not null,
last_update_date datetime not null
)
和
create table tag
(
id int auto_increment
primary key,
name varchar(64) not null,
constraint tag_name_uindex
unique (name)
)
帶有鏈接表：
create table gift_certificate__tag
(
certificate_id int not null,
tag_id int not null,
primary key (certificate_id, tag_id),
constraint gift_certificate__tag_gift_certificate_id_fk
foreign key (certificate_id) references gift_certificate (id),
constraint gift_certificate__tag_tag_id_fk
foreign key (tag_id) references tag (id)
)
我需要通過多個標簽（“and”條件）搜索禮券。我只想出了一個標簽的解決方案
select distinct gc.*, tag.* from gift_certificate gc
left outer join gift_certificate__tag joint on gc.id=joint.certificate_id
left outer join tag on joint.tag_id=tag.id
where tag.name='puppy'
order by gc.id desc;
將不勝感激一些支持
uj5u.com熱心網友回復：
您可以按證書聚合聯合表，并使用HAVING僅保留具有所有標簽的證書。然后使用IN子句選擇所有匹配的證書。例如：
select *
from gift_certificate
where id in
(
select joint.certificate_id
from gift_certificate__tag joint
join tag on joint.tag_id=tag.id
group by joint.certificate_id
having max(case when tag.name = 'puppy' then 1 else 0 end) = 1
and max(case when tag.name = 'something' then 1 else 0 end) = 1
);
由于 MySQL 中的 true = 1 和 false = 0，您可以將運算式縮短為
having max(tag.name = 'puppy')
如果你覺得這個可讀。或者
having sum(tag.name = 'puppy') > 0
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/492924.html