MySQL-帮助我更改此搜索查询以获得所需结果 [英] Mysql - Help me alter this search query to get desired results

问题描述

以下是回答理解系统所需的表和数据的转储:-

系统由导师和课程组成. 表All_Tag_Relations中的数据存储已注册的每个导师和该导师创建的每个类的标签关系.标签关系用于搜索类别.

The system consists of tutors and classes. The data in the table All_Tag_Relations stores tag relations for each tutor registered and each class created by a tutor. The tag relations are used for searching classes.

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`),
  KEY `tag_4` (`tag`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

INSERT INTO `Tags` (`id_tag`, `tag`) VALUES
(1, 'Sandeepan'),
(2, 'Nath'),
(3, 'first'),
(4, 'class'),
(5, 'new'),
(6, 'Bob'),
(7, 'Cratchit');


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


INSERT INTO `All_Tag_Relations` (`id_tag`, `id_tutor`, `id_wc`) VALUES
(1, 1, NULL),
(2, 1, NULL),
(3, 1, 1),
(4, 1, 1),
(6, 2, NULL),
(7, 2, NULL),
(5, 2, 2),
(4, 2, 2),
(8, 1, 3),
(9, 1, 3);

以下是我的查询:-

此查询搜索第一类"(标签"表中"first = 3"和"class = 4"的标签)，并返回所有这些类，以使"first"和"class"这两个词都出现在类名称中.

This query searches for "first class" (tag for first = 3 and for class = 4, in Tags table) and returns all those classes such that both the terms first and class are present in the class name.

SELECT wtagrels.id_wc,SUM(DISTINCT( wtagrels.id_tag =3)) AS
       key_1_total_matches,
       SUM(DISTINCT( wtagrels.id_tag =4))                AS
       key_2_total_matches
FROM   all_tag_relations AS wtagrels
WHERE  ( wtagrels.id_tag =3
          OR wtagrels.id_tag =4 )
GROUP  BY wtagrels.id_wc
HAVING key_1_total_matches = 1
       AND key_2_total_matches = 1
LIMIT  0, 20  

它返回id_wc = 1的类.

And it returns the class with id_wc = 1.

但是，我希望搜索显示所有这些类，以使所有搜索词都出现在类名或其导师名中 因此，搜索"Sandeepan类"(wtagrels.id_tag = 1,4)或"Sandeepan Nath"也将返回id_wc = 1的类.和搜索.搜索鲍勃优先"应该不会返回任何类.

But, I want the search to show all those classes such that all the search terms are present in the class name or its tutor name So that searching "Sandeepan class" (wtagrels.id_tag = 1,4) or "Sandeepan Nath" also returns the class with id_wc=1. And Searching. Searching "Bob First" should not return any classes.

如果可能的话，请使用MyIsam-全文搜索修改上面的查询或提出新的查询，但是以某种方式可以帮助我获得结果.

Please modify the above query or suggest a new query, if possible using MyIsam - fulltext search, but somehow help me get the result.

推荐答案

我认为此查询将为您提供帮助:

I think this query would help you:

SET @tag1 = 1, @tag2 = 4; -- Setting some user variables to see where the ids go. (you can put the values in the query)

SELECT wtagrels.id_wc,
  SUM(DISTINCT( wtagrels.id_tag =@tag1 OR wtagrels.id_tutor =@tag1)) AS key_1_total_matches,
  SUM(DISTINCT( wtagrels.id_tag =@tag2 OR wtagrels.id_tutor =@tag2)) AS key_2_total_matches
FROM   all_tag_relations AS wtagrels
WHERE  ( wtagrels.id_tag =@tag1 OR wtagrels.id_tag =@tag2 )
GROUP  BY wtagrels.id_wc
HAVING key_1_total_matches = 1 AND key_2_total_matches = 1
LIMIT  0, 20

它返回id_wc = 1.
对于(6，3)，查询不返回任何内容.

It returns id_wc = 1.
For (6, 3) the query returns nothing.

这篇关于MySQL-帮助我更改此搜索查询以获得所需结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！