简化跨多列的查询

Question

我有一张教室名称表，我想与几种已知仪器进行比较。我的表共有11个列，但其中四个列包含部分首席乐器名称（instrument1 ... instrument4）。我想创建一个SQL语句，将我要查找的两个工具与每行每列中存在的四个名称进行比较。

ex。我需要找到小号和长号

我认为它会看起来像这样。

下面是声明，我有

SELECT * 
FROM section_info 
WHERE (instrument1 = trombone 
      OR instrument2 = trombone 
      OR instrument3 = trombone 
      OR instrument4 = trombone) 
     AND (instrument1 = trumpet 
       OR intsrument2 = trumpet 
       OR instrument3 = trumpet 
       OR instrument4 = trumpet) 

有没有办法来简化？

Answer 1

鉴于你的数据库结构，这段代码就好了。只要确保你format it nicely和拼写trombone正确。

但是，您始终可以选择调整数据库结构。

，你可以：

1. 创建Player表，其中有场PlayerID和PlayerName。
2. 创建一个Instruments表，其中包含字段InstrumentID和InstrumentName。
3. 最后，创建一个Player_Instruments表，其中包含字段PlayerID和InstrumentID。这将乐器映射到玩家。谁拥有小号和长号

   SELECT * 
   FROM players AS P 
        INNER JOIN player_instruments AS PI ON PI.PlayerID = P.PlayerID 
        INNER JOIN instruments AS I ON I.InstrumentID = PI.InstrumentID 
   WHERE I.InstrumentName = 'trumpet' 
        OR I.InstrumentName = 'trombone' 
   

   玩家选择：

谁拥有一个小号长号玩家选择

SELECT * 
FROM players AS P 
     INNER JOIN player_instruments AS TRUMP ON TRUMP.playerid = P.playerid 
     INNER JOIN instruments AS TRUMP_I ON TRUMP_I.instrumentid = TRUMP.instrumentid AND TRUMP_I.InstrumentName = 'trumpet' 
     INNER JOIN player_instruments AS TROM ON TROM.playerid = P.playerid 
     INNER JOIN instruments AS TROM_I ON TROM_I.instrumentid = TROM.instrumentid AND TROM_I.InstrumentName = 'trombone' 

的Inner Join此原因只有那些球员谁有一个有效的映射到小号和长号包括在结果集中。

Answer 2

SELECT * FROM SECTION_INFO 
WHERE INSTRUMENT1 in ('TROMBONE', 'TRUMPET') 
or INSTRUMENT2 in ('TROMBONE', 'TRUMPET') 
or INSTRUMENT3 in ('TROMBONE', 'TRUMPET') 
or INSTRUMENT4 in ('TROMBONE', 'TRUMPET') 

不是很简单，但由于您是比较多个列，我没有看到太多的选择。

编辑：现在我重读了这个问题，如果你必须同时拥有长号和小号，这可能不会奏效。所以我可能在这方面失败了。 :)对不起。

Answer 3

如下表模式：

CREATE TABLE `instruments` (
    `id` INT(10) NOT NULL AUTO_INCREMENT, 
    `instrument` VARCHAR(50) NULL DEFAULT NULL, 
    PRIMARY KEY (`id`) 
) 
COLLATE='utf8_general_ci' 
ENGINE=InnoDB 

CREATE TABLE `section_info` (
    `instrument1` INT(10) NULL DEFAULT NULL, 
    `instrument2` INT(10) NULL DEFAULT NULL, 
    `instrument3` INT(10) NULL DEFAULT NULL, 
    `instrument4` INT(10) NULL DEFAULT NULL 
) 
COLLATE='utf8_general_ci' 
ENGINE=InnoDB 

你可以做到以下几点：

SELECT si.*, inst_1.instrument, inst_2.instrument, inst_3.instrument, inst_4.instrument  
FROM section_info si                   
    LEFT JOIN instruments as inst_1 ON si.instrument1=inst_1.id        
    LEFT JOIN instruments as inst_2 ON si.instrument2=inst_2.id        
    LEFT JOIN instruments as inst_3 ON si.instrument3=inst_3.id        
    LEFT JOIN instruments as inst_4 ON si.instrument4=inst_4.id        

WHERE                       
    # instrument 1 is a trombone...               
    (si.instrument1 = 1 OR si.instrument2 = 1 OR si.instrument3 = 1 OR si.instrument4 = 1) 
    AND                      
    # instrument 2 is a trumpet...                
    (si.instrument1 = 2 OR si.instrument2 = 2 OR si.instrument3 = 2 OR si.instrument4 = 2)  

Answer 4

如果你的意思是要简化自己的查询脚本，那么你可以重写WHERE子句像这样：

WHERE 'trombone' IN (instrument1, instrument2, instrument3, instrument4) 
    AND 'trumpet' IN (instrument1, instrument2, instrument3, instrument4) 

但我必须说，这可能会阻止数据库引擎使用索引所述列，如果有的话。