Mysql - 帮助我更改此查询以在搜索中应用 AND 逻辑而不是 OR ? [英] Mysql - Help me alter this query to apply AND logic instead of OR in searching?

查看：40 发布时间：2021/6/5 19:33:08 mysql

本文介绍了Mysql - 帮助我更改此查询以在搜索中应用 AND 逻辑而不是 OR ?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

首先执行这些表和数据转储:-

如果不存在`标签`，则创建表(`id_tag` int(10) unsigned NOT NULL auto_increment，`tag` varchar(255) 默认为 NULL，主键(`id_tag`)，唯一键`标签`(`标签`)，KEY`id_tag`(`id_tag`)，关键`tag_2`(`标签`)，密钥`tag_3`(`标签`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;插入`标签`(`id_tag`，`tag`)值(1, 'key1'),(2, 'key2');如果不存在则创建表`Tutors_Tag_Relations`(`id_tag` int(10) 无符号 NOT NULL 默认值 '0'，`id_tutor` int(10) 默认为 NULL，KEY`Tutors_Tag_Relations`(`id_tag`)，KEY`id_tutor`(`id_tutor`)，密钥`id_tag`(`id_tag`)) 引擎=InnoDB 默认字符集=latin1;插入`Tutors_Tag_Relations`(`id_tag`，`id_tutor`)值(1, 1),(2, 1);

以下查询从 Tutors_Tag_Relations 表中查找所有引用至少一个术语key1"或key2"的导师.

SELECT td .*FROM Tutors_Tag_Relations AS tdINNER JOIN 标签 AS t ON t.id_tag = td.id_tagWHERE t.tag LIKE "%key1%"或 t.tag LIKE "%key2%"按 td.id_tutor 分组限制 10

请帮我修改这个查询，以便它返回 Tutors_Tag_Relations 表中引用了术语key1"和key2"(AND 逻辑而不是 OR 逻辑)的所有导师.请建议考虑到大量数据记录的优化查询(查询不应单独获取匹配每个关键字的两组导师，然后找到交集).

更新

将问题提升到一个新的水平.请运行以下新查询:-

======================================================================================

如果不存在则创建表 learning_packs_tag_relations (id_tag int(10) unsigned NOT NULL DEFAULT '0',id_tutor int(10) DEFAULT NULL,id_lp int(10) 无符号默认 NULL，KEY Learning_Packs_Tag_Relations_FKIndex1 (id_tag)，KEY id_lp (id_lp),KEY id_tag (id_tag)) 引擎=InnoDB 默认字符集=latin1;

如果不存在则创建表 learning_packs (id_lp int(10) unsigned NOT NULL AUTO_INCREMENT,id_status int(10) unsigned NOT NULL DEFAULT '2',id_author int(10) unsigned NOT NULL DEFAULT '0',name varchar(255) NOT NULL DEFAULT '',主键 (id_lp)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;

如果不存在则创建表 tutors_tag_relations (id_tag int(10) unsigned NOT NULL DEFAULT '0',id_tutor int(10) DEFAULT NULL,

KEY Tutors_Tag_Relations (id_tag),KEY id_tutor (id_tutor),KEY id_tag (id_tag)) 引擎=InnoDB 默认字符集=latin1;

如果不存在则创建表 users (id_user int(10) unsigned NOT NULL AUTO_INCREMENT,name varchar(100) NOT NULL DEFAULT '',surname varchar(155) NOT NULL DEFAULT '',

主键(id_user)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=52 ;

如果不存在则创建表 tutor_details (id_tutor int(10) NOT NULL AUTO_INCREMENT,id_user int(10) 非空，主键 (id_tutor)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=60 ;

如果不存在则创建表 tags (id_tag int(10) unsigned NOT NULL AUTO_INCREMENT,tag varchar(255) DEFAULT NULL,主键 (id_tag)，唯一键 tag (tag)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

ALTER TABLE learning_packs_tag_relations添加约束Learning_Packs_Tag_Relations_ibfk_1 FOREIGN KEY (id_tag) REFERENCES tags (id_tag) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE learning_packs

ADD CONSTRAINT Learning_Packs_ibfk_2 FOREIGN KEY (id_author) REFERENCES users (id_user) ON DELETE NO ACTION ON UPDATE无动作；

ALTER TABLE tutors_tag_relations添加约束Tutors_Tag_Relations_ibfk_1 FOREIGN KEY (id_tag) REFERENCES tags (id_tag) ON DELETE NO ACTION ON UPDATE NO ACTION;

INSERT INTO test.users (id_user ,<代码>名称 ,姓氏)值 (NULL , '薇薇安', '理查兹'), (NULL , 'Sachin', 'Tendulkar');

INSERT INTO test.users (id_user ,<代码>名称 ,姓氏)值 (NULL , '唐', '布拉德曼');

INSERT INTO test.tutor_details (id_tutor ,id_user)值 (NULL , '52'), (NULL , '53');

INSERT INTO test.tutor_details (id_tutor ,id_user)值 (NULL , '54');

INSERT INTO test.tags (id_tag ,标签)值 (1、《薇薇安》), (2、理查兹");

INSERT INTO test.tags (id_tag, tag) VALUES (3, 'Sachin'),(4, 'Tendulkar');INSERT INTO test.tags (id_tag, tag) VALUES (5, 'Don'), (6,'布拉德曼');

INSERT INTO test.learning_packs (id_lp, id_status, id_author,name) VALUES ('1', '1', '52', 'Cricket 1'), ('2', '2', '52', 'Cricket 2');

INSERT INTO test.tags (id_tag, tag) VALUES ('7', 'Cricket'), ('8', '1');INSERT INTO test.tags (id_tag, tag) VALUES ('9', '2');

INSERT INTO test.learning_packs_tag_relations (id_tag, id_tutor, id_lp)值 ('7', '52', '1'), ('8', '52', '1');INSERT INTO test.learning_packs_tag_relations (id_tag, id_tutor, id_lp) VALUES ('7', '52', '2'), ('9', '52', '2');

======================================================================================

关于新系统——- 系统现在还有 4 个表 - 导师、用户(链接到tutor_details)、learning_packs、learning_packs_tag_relations- 导师创建包 - 导师的标签关系存储在tutors_tag_relations 中，包的标签关系存储在 learning_packs_tag_relations 中.

现在我想用相同的 AND 逻辑搜索 learning_packs.帮我修改下面的查询，以便搜索包名称或导师的名字，姓氏结果所有活动包(直接那些包或那些导师创建的包).

====================================================================================

选择 lp.*

来自 Learning_Packs AS lp

LEFT JOIN Learning_Packs_Tag_Relations AS lptagrels ON lp.id_lp = lptagrels.id_lp

LEFT JOIN Tutors_Tag_Relations 作为 ttagrels ON lp.id_author = ttagrels.id_tutorLEFT JOIN Tutor_Details AS td ON ttagrels.id_tutor = td.id_tutor在 td.id_user = u.id_user 上以 u 的身份加入用户

JOIN 标签为 t on (t.id_tag = lptagrels.id_tag) 或 (t.id_tag = ttagrels.id_tag)

其中 lp.id_status = 1 AND ( t.tag LIKE "%Vivian%" OR t.tag LIKE "%Richards%" )

group by lp.id_lp HAVING count(lp.id_lp) > 1 limit 0,20

如您所见，搜索Cricket 1"会返回该包，但搜索 Vivian Richards 不会返回相同的包.

请帮忙

解决方案

如果使用 Group 和 Being 就很简单了.这应该能满足您的需求.

选择id_tutorFROM Tutors_Tag_Relations AS tdINNER JOIN 标签 AS t ON t.id_tag = td.id_tagWHERE t.tag LIKE "%key1%"或 t.tag LIKE "%key2%"按 id_tutor 分组有 count(id_tutor)>1

First execute these tables and data dumps :-

CREATE TABLE IF NOT EXISTS `Tags` (
`id_tag` int(10) unsigned NOT NULL auto_increment,
`tag` varchar(255) default NULL,
PRIMARY KEY (`id_tag`),
UNIQUE KEY `tag` (`tag`),
KEY `id_tag` (`id_tag`),
KEY `tag_2` (`tag`),
KEY `tag_3` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;


INSERT INTO `Tags` (`id_tag`, `tag`) VALUES
(1, 'key1'),
(2, 'key2');

CREATE TABLE IF NOT EXISTS `Tutors_Tag_Relations` (
`id_tag` int(10) unsigned NOT NULL default '0',
`id_tutor` int(10) default NULL,
KEY `Tutors_Tag_Relations` (`id_tag`),
KEY `id_tutor` (`id_tutor`),
KEY `id_tag` (`id_tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Tutors_Tag_Relations` (`id_tag`, `id_tutor`) VALUES
(1, 1),
(2, 1);

The following query finds all the tutors from Tutors_Tag_Relations table which have reference to at least one of the terms "key1" or "key2".

SELECT td . *
FROM Tutors_Tag_Relations AS td
INNER JOIN Tags AS t ON t.id_tag = td.id_tag
WHERE t.tag LIKE "%key1%"
OR t.tag LIKE "%key2%"

Group by td.id_tutor
LIMIT 10

Please help me modify this query so that it returns all the tutors from Tutors_Tag_Relations table which have reference to both the terms "key1" and "key2" (AND logic instead of OR logic). Please suggest an optimized query considering huge number of data records (the query should NOT individually fetch two sets of tutors matching each keyword and then find the intersection).

Update

Taking the question to the next level. Please run the following fresh queries :-

===================================================================================

CREATE TABLE IF NOT EXISTS learning_packs_tag_relations ( id_tag int(10) unsigned NOT NULL DEFAULT '0', id_tutor int(10) DEFAULT NULL, id_lp int(10) unsigned DEFAULT NULL, KEY Learning_Packs_Tag_Relations_FKIndex1 (id_tag), KEY id_lp (id_lp), KEY id_tag (id_tag) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS learning_packs ( id_lp int(10) unsigned NOT NULL AUTO_INCREMENT, id_status int(10) unsigned NOT NULL DEFAULT '2', id_author int(10) unsigned NOT NULL DEFAULT '0', name varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (id_lp) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;

CREATE TABLE IF NOT EXISTS tutors_tag_relations ( id_tag int(10) unsigned NOT NULL DEFAULT '0', id_tutor int(10) DEFAULT NULL,

KEY Tutors_Tag_Relations (id_tag), KEY id_tutor (id_tutor), KEY id_tag (id_tag) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS users ( id_user int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(100) NOT NULL DEFAULT '', surname varchar(155) NOT NULL DEFAULT '',

PRIMARY KEY (id_user)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=52 ;

CREATE TABLE IF NOT EXISTS tutor_details ( id_tutor int(10) NOT NULL AUTO_INCREMENT, id_user int(10) NOT NULL, PRIMARY KEY (id_tutor) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=60 ;

CREATE TABLE IF NOT EXISTS tags ( id_tag int(10) unsigned NOT NULL AUTO_INCREMENT, tag varchar(255) DEFAULT NULL, PRIMARY KEY (id_tag), UNIQUE KEY tag (tag) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

ALTER TABLE learning_packs_tag_relations ADD CONSTRAINT Learning_Packs_Tag_Relations_ibfk_1 FOREIGN KEY (id_tag) REFERENCES tags (id_tag) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE learning_packs

ADD CONSTRAINT Learning_Packs_ibfk_2 FOREIGN KEY (id_author) REFERENCES users (id_user) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE tutors_tag_relations ADD CONSTRAINT Tutors_Tag_Relations_ibfk_1 FOREIGN KEY (id_tag) REFERENCES tags (id_tag) ON DELETE NO ACTION ON UPDATE NO ACTION;

INSERT INTO test.users ( id_user , name , surname ) VALUES ( NULL , 'Vivian', 'Richards' ), ( NULL , 'Sachin', 'Tendulkar' );

INSERT INTO test.users ( id_user , name , surname ) VALUES ( NULL , 'Don', 'Bradman' );

INSERT INTO test.tutor_details ( id_tutor , id_user ) VALUES ( NULL , '52' ), ( NULL , '53' );

INSERT INTO test.tutor_details ( id_tutor , id_user ) VALUES ( NULL , '54' );

INSERT INTO test.tags ( id_tag , tag ) VALUES ( 1 , 'Vivian' ), ( 2 , 'Richards' );

INSERT INTO test.tags (id_tag, tag) VALUES (3, 'Sachin'), (4, 'Tendulkar'); INSERT INTO test.tags (id_tag, tag) VALUES (5, 'Don'), (6, 'Bradman');

INSERT INTO test.learning_packs (id_lp, id_status, id_author, name) VALUES ('1', '1', '52', 'Cricket 1'), ('2', '2', '52', 'Cricket 2');

INSERT INTO test.tags (id_tag, tag) VALUES ('7', 'Cricket'), ('8', '1'); INSERT INTO test.tags (id_tag, tag) VALUES ('9', '2');

INSERT INTO test.learning_packs_tag_relations (id_tag, id_tutor, id_lp) VALUES ('7', '52', '1'), ('8', '52', '1'); INSERT INTO test.learning_packs_tag_relations (id_tag, id_tutor, id_lp) VALUES ('7', '52', '2'), ('9', '52', '2');

===================================================================================

About the new system - - The system now has 4 more tables - tutors, Users (linked to tutor_details), learning_packs, learning_packs_tag_relations - Tutors create packs - tag relations for tutors stored in tutors_tag_relations and those for packs stored in learning_packs_tag_relations.

Now I want to search learning_packs, with the same AND logic. Help me modify the following query so that searching pack name or tutor's name, surname results all active packs (either directly those packs or packs created by those tutors).

==================================================================================

select lp.*

from Learning_Packs AS lp

LEFT JOIN Learning_Packs_Tag_Relations AS lptagrels ON lp.id_lp = lptagrels.id_lp

LEFT JOIN Tutors_Tag_Relations as ttagrels ON lp.id_author = ttagrels.id_tutor LEFT JOIN Tutor_Details AS td ON ttagrels.id_tutor = td.id_tutor LEFT JOIN Users as u on td.id_user = u.id_user

JOIN Tags as t on (t.id_tag = lptagrels.id_tag) or (t.id_tag = ttagrels.id_tag)

where lp.id_status = 1 AND ( t.tag LIKE "%Vivian%" OR t.tag LIKE "%Richards%" )

group by lp.id_lp HAVING count(lp.id_lp) > 1 limit 0,20

As you can see, searching "Cricket 1" returns that pack but searching Vivian Richards does not return the same pack.

Please help

解决方案

Pretty simple if using Group and Having. This should get what you are looking for.

SELECT id_tutor FROM Tutors_Tag_Relations AS td INNER JOIN Tags AS t ON t.id_tag = td.id_tag WHERE t.tag LIKE "%key1%" or t.tag LIKE "%key2%" group by id_tutor having count(id_tutor)>1

这篇关于Mysql - 帮助我更改此查询以在搜索中应用 AND 逻辑而不是 OR ?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

Mysql - 帮助我更改此查询以在搜索中应用 AND 逻辑而不是 OR ? [英] Mysql - Help me alter this query to apply AND logic instead of OR in searching?

问题描述

group by lp.id_lp HAVING count(lp.id_lp) > 1 limit 0,20

group by lp.id_lp HAVING count(lp.id_lp) > 1 limit 0,20

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

Mysql - 帮助我更改此查询以在搜索中应用 AND 逻辑而不是 OR ? [英] Mysql - Help me alter this query to apply AND logic instead of OR in searching?

问题描述

group by lp.id_lp HAVING count(lp.id_lp) > 1 limit 0,20

group by lp.id_lp HAVING count(lp.id_lp) > 1 limit 0,20

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭