php - MySQL查询帮助-更改此MySQL查询以获得这些结果？

请先执行以下查询进行设置，以便您可以帮助我：-

CREATE TABLE IF NOT EXISTS `Tutor_Details` (
`id_tutor` int(10) NOT NULL auto_increment,
`firstname` varchar(100) NOT NULL default '',
`surname` varchar(155) NOT NULL default '',
PRIMARY KEY (`id_tutor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;

INSERT INTO `Tutor_Details` (`id_tutor`,`firstname`, `surname`) VALUES
(1, 'Sandeepan', 'Nath'),
(2, 'Bob', 'Cratchit');

CREATE TABLE IF NOT EXISTS `Classes` (
`id_class` int(10) unsigned NOT NULL auto_increment,
`id_tutor` int(10) unsigned NOT NULL default '0',
`class_name` varchar(255) default NULL,
PRIMARY KEY (`id_class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=229 ;

INSERT INTO `Classes` (`id_class`,`class_name`, `id_tutor`) VALUES
(1, 'My Class', 1),
(2, 'Sandeepan Class', 2);

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, 'Bob'),
(6, 'Class'),
(2, 'Cratchit'),
(4, 'Nath'),
(3, 'Sandeepan'),
(5, 'My');

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
(3, 1),
(4, 1),
(1, 2),
(2, 2);

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

INSERT INTO `Class_Tag_Relations` (`id_tag`, `id_class`, `id_tutor`) VALUES
(5, 1, 1),
(6, 1, 1),
(3, 2, 2),
(6, 2, 2);

在我给出的系统数据中，名为“sandepan Nath”的导师创建了名为“我的班级”的班级，名为“Bob Cratchit”的导师创建了名为“sandepan班级”的班级。
要求-
要执行对结果有限制的单个查询，以按照以下搜索关键字和逻辑显示搜索结果：-
如果搜索“sandepan Class”，则返回Tutor Details表中的Tutor sandepan Nath记录（因为“sandepan”是sandepan Nath的名字，并且类出现在sandepan的类的类名中）
如果搜索“类”，则会获取Tutor_details表中的两个导师，因为类以两个导师创建的类的名称存在。
以下是我迄今为止所取得的成就（PHP Mysql）：-

<?php
$searchTerm1 = "Sandeepan";
$searchTerm2 = "Class";

mysql_select_db("test");


$sql = "SELECT td.*
FROM Tutor_Details AS td
LEFT JOIN Tutors_Tag_Relations AS ttagrels ON td.id_tutor = ttagrels.id_tutor
LEFT JOIN Classes AS wc ON td.id_tutor = wc.id_tutor
LEFT JOIN Class_Tag_Relations AS wtagrels ON td.id_tutor = wtagrels.id_tutor

LEFT JOIN Tags as t1 on ((t1.id_tag = ttagrels.id_tag) OR (t1.id_tag = wtagrels.id_tag))
LEFT JOIN Tags as t2 on ((t2.id_tag = ttagrels.id_tag) OR (t2.id_tag = wtagrels.id_tag))


where t1.tag LIKE '%".$searchTerm1."%'
AND t2.tag LIKE '%".$searchTerm2."%'

GROUP BY td.id_tutor
LIMIT 10
";

$result = mysql_query($sql);
echo $sql;
if($result)
{

while($rec = mysql_fetch_object($result)) $recs[] = $rec;
//$rec = mysql_fetch_object($result);
echo "<br><br>";

if(is_array($recs))
{
foreach($recs as $each)
{
print_r($each);
echo "<br>";
}

}

}
?>

但结果是：-
如果搜索“sandepan Nath”，则不返回任何家教（而不是仅返回sandepan的行）
如果搜索“sandepan类”，则返回sandepan的行（而不是两个导师）
如果搜索“Bob类”，它将正确返回Bob的行
如果搜索“Bob Cratchit”，则不返回任何家教（而不是仅返回

最佳答案

问题是，您有两个搜索项，并且您没有生成任何可以从同一个关系表中搜索两个标记的行（如果您查看查询结果时不将其限制为td，*，这很容易看到）。如果您想在SQL中完成这项工作，解决方案是生成用于每个tutor/class关系的标记的所有2个搜索项排列（同样，当您查看完整的查询结果时，这种解释更有意义）。不管怎样，这是我用SQL来修复SQL的方式：

SELECT td.*
FROM Tutors_Tag_Relations AS ttagrels1
JOIN Tutors_Tag_Relations AS ttagrels2 ON
    ttagrels2.id_tutor = ttagrels1.id_tutor AND
    ttagrels2.id_tag != ttagrels1.id_tag
JOIN Class_Tag_Relations AS wtagrels1 ON
    wtagrels1.id_tutor = ttagrels1.id_tutor AND
    wtagrels1.id_tag != ttagrels1.id_tag AND
    wtagrels1.id_tag != ttagrels2.id_tag
JOIN Class_Tag_Relations AS wtagrels2 ON
    wtagrels2.id_tutor = ttagrels1.id_tutor AND
    wtagrels2.id_tag != wtagrels1.id_tag AND
    wtagrels2.id_tag != ttagrels1.id_tag AND
    wtagrels2.id_tag != ttagrels2.id_tag
JOIN Tags as t1 ON
    t1.id_tag = ttagrels1.id_tag OR
    t1.id_tag = ttagrels2.id_tag OR
    t1.id_tag = wtagrels1.id_tag OR
    t1.id_tag = wtagrels2.id_tag
JOIN Tags as t2 ON
    t2.id_tag != t1.id_tag AND
    (t2.id_tag = ttagrels1.id_tag OR
    t2.id_tag = ttagrels2.id_tag OR
    t2.id_tag = wtagrels1.id_tag OR
    t2.id_tag = wtagrels2.id_tag)
LEFT JOIN Tutor_Details as td ON ttagrels1.id_tutor = td.id_tutor
LEFT JOIN Classes AS wc ON td.id_tutor = wc.id_tutor
WHERE
    t1.tag LIKE '%Sandeepan%' AND
    t2.tag LIKE '%Nath%'
GROUP BY td.id_tutor

不过，我真的不会这么做。尝试通过连接进行这种搜索会变得非常、非常繁重，只有添加更多的搜索词，情况才会变得更糟。
对缺失排列的解释：
这些表是通过删除where子句、group子句、删除重复项并仅显示td1和td2列来生成的。
你的方式：

+--------+-----------+--------+-----------+
| id_tag | tag       | id_tag | tag       |
+--------+-----------+--------+-----------+
|      1 | Bob       |      3 | Sandeepan |
|      1 | Bob       |      6 | Class     |
|      2 | Cratchit  |      3 | Sandeepan |
|      2 | Cratchit  |      6 | Class     |
|      3 | Sandeepan |      1 | Bob       |
|      3 | Sandeepan |      2 | Cratchit  |
|      3 | Sandeepan |      5 | My        |
|      3 | Sandeepan |      6 | Class     |
|      4 | Nath      |      5 | My        |
|      4 | Nath      |      6 | Class     |
|      5 | My        |      3 | Sandeepan |
|      5 | My        |      4 | Nath      |
|      6 | Class     |      1 | Bob       |
|      6 | Class     |      2 | Cratchit  |
|      6 | Class     |      3 | Sandeepan |
|      6 | Class     |      4 | Nath      |
+--------+-----------+--------+-----------+

现在，如果我们看一下这个，我们会发现td1.id_标签是由存在的类或导师关系产生的。此外，td2.id_标签是根据当前的班级或导师关系生成的。但是，对于此结果的任何一行，td1.id_标记和td2.id_标记不能来自同一个关系表。它们总是Class/Tutors或Tutors/Class一个类/类或Tutors/Tutors一组标记（请记住，在类关系表中有一个sandepan标记）。这意味着您无法搜索“sandepan”“Nash”或“Bob”“Cratchit”，因为在这两种情况下，这些标记只出现在一个表中。
我的方式：

+--------+-----------+--------+-----------+
| id_tag | tag       | id_tag | tag       |
+--------+-----------+--------+-----------+
|      1 | Bob       |      2 | Cratchit  |
|      1 | Bob       |      3 | Sandeepan |
|      1 | Bob       |      6 | Class     |
|      2 | Cratchit  |      1 | Bob       |
|      2 | Cratchit  |      3 | Sandeepan |
|      2 | Cratchit  |      6 | Class     |
|      3 | Sandeepan |      1 | Bob       |
|      3 | Sandeepan |      2 | Cratchit  |
|      3 | Sandeepan |      4 | Nath      |
|      3 | Sandeepan |      5 | My        |
|      3 | Sandeepan |      6 | Class     |
|      4 | Nath      |      3 | Sandeepan |
|      4 | Nath      |      5 | My        |
|      4 | Nath      |      6 | Class     |
|      5 | My        |      3 | Sandeepan |
|      5 | My        |      4 | Nath      |
|      5 | My        |      6 | Class     |
|      6 | Class     |      1 | Bob       |
|      6 | Class     |      2 | Cratchit  |
|      6 | Class     |      3 | Sandeepan |
|      6 | Class     |      4 | Nath      |
|      6 | Class     |      5 | My        |
+--------+-----------+--------+-----------+

我的SQL所做的只是生成缺少的类/类Tutors/Tutors行，这就解决了问题。

关于php - MySQL查询帮助-更改此MySQL查询以获得这些结果？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2926358/