要在表中搜索字符串的SQL查询？

Question

嗨，我想在存储在mysql数据库中的表中搜索特定的字符串。表的结构如下：

+--------------------------------------------------------------------------+
|    day    | 9-10    | 10-11  | 11-12    | 12-1  | 1-2    |  2-3   |  3-4 |
|---------------------------------------------------------------------------
| Monday    | DA6210  | DA6220  | DA6010  |       | DA6020 |        |      |
| Tuesday   | DA6210  | DA6010  | DA6220  |       | DA6020 |        |      |
| Wednesday | IA6010  | DA6220  |         |       | DA6020 |        |      |
| Thursday  | IA6010  |         | DA6210  |       | GC6210 |        |      |
| Friday    | IA6010  |         | DA6010  |       | GC6210 |        |      |
| Saturday  | DA6210  |         |         |       |        |        |      |
+--------------------------------------------------------------------------+

我想找字符串的日子和所有的时间。为了前夫。如果是星期四，代码是DA6220，那么它应该返回空集，因为星期四的所有时隙中都没有字符串。

我已经创建了一个sql查询，但它没有按需要工作。请提供一种方法。

SQl查询：-

SELECT * from `table` where day='Thursday' AND `9-10`='DA6220' OR `10-11`='DA6220' OR `11-12`='DA6220' 
            OR `12-1`='DA6220' OR `1-2`='DA6220' OR `2-3`='DA6220' OR `3-4`='DA6220'

Answer 1

SELECT * from `table` where day='Thursday' AND (`9-10`='DA6220' OR `10-11`='DA6220' OR `11-12`='DA6220' 
            OR `12-1`='DA6220' OR `1-2`='DA6220' OR `2-3`='DA6220' OR `3-4`='DA6220')

看起来你的和是先评估的。请试一下上面的内容

Answer 2

您的问题的答案已经给出了，但是当我查看您的数据时，我看到了一些可以使您的生活更加轻松并增加您的可能性的东西:您最好创建一个视图。

首先，我稍微修改了您的列名，因为您保留了单词。

单击此处获取

介绍你的新朋友 myview

cREATE VIEW myview AS 
SELECT wkday, '9-10'  AS rng, `9-10`  AS cd from `mytable` 
UNION  
SELECT wkday, '10-11' AS rng, `10-11` AS cd from `mytable` 
UNION 
SELECT wkday, '11-12' AS rng, `11-12` AS cd from `mytable` 
UNION 
SELECT wkday, '12-1'  AS rng, `12-1`  AS cd from `mytable` 
UNION 
SELECT wkday, '1-2'   AS rng, `1-2`   AS cd from `mytable` 
UNION 
SELECT wkday, '2-3'   AS rng, `2-3`   AS cd from `mytable` 
UNION 
SELECT wkday, '3-4'   AS rng, `3-4`   AS cd from `mytable`  
;

这是一个针对您的DB的一次性查询。

您的视图数据是：

SELECT * FROM myview

wkday           rng     cd
----------------------
Monday      9-10    DA6210
Tuesday     9-10    DA6210
Wednesday   9-10    IA6010
Thursday    9-10    IA6010
Friday      9-10    IA6010
Saturday    9-10    DA6210
Monday      10-11   DA6220
Tuesday     10-11   DA6010
Wednesday   10-11   DA6220
...

创建视图后，原始查询编写起来要简单得多：

它变成了:

SELEct rng FROM myview WHERE wkday='Thursday' AND cd='DA6220';

不再需要for，也不必无休止地重复您正在寻找的代码。

...and如果您想要搜索多个代码，那么它也是微不足道的：

SELEct rng FROM myview WHERE wkday='Thursday' AND cd IN ('DA6220','IA6010');

但是，你的新观点的酷之处在于，你可以简单地问：

我有代码'DA6220‘的日期或范围是多少？

days

  SELEct wkday FROM myview WHERE cd='DA6220';

结果：

wkday
----------
Tuesday
Wednesday
Monday

Ranges

  SELEct rng FROM myview WHERE cd='DA6220';

结果：

rng
-----
11-12
10-11
10-11

这是视图的典型应用程序。这会让你的生活更轻松。

Answer 3

对我的评论作如下阐述：

SELECT *
  FROM `table`
 WHERE 0 < LOCATE("DA6220", CONCAT(
          `9-10`, " ", `10-11`, " ", `11-12`, " ",
          `12-1`, " ", `1-2`, " ", `2-3`, " ", `3-4`
       ))

这种方法的好处是不必重复搜索字符串。它可能比OR版本稍慢一些。