Question

我有问题要调查以下数据:

id. | name | type

 1.    bob | a 
 2.    sam | ab
 3.    jim | abc
 4.  sarah | ad
 5.   john | a
 6.    eve | bca
 7.  nikki | ca

我不想在以下投入的基础上撰写我的SQL问题:

a,
ac,
b*,
ac*

我想要归还以下名字:

bob
john
sam
jim
eve
nikki

<>Reakdown:

a  = bob, john
ac = nikki  
b* = sam, jim, eve
ac*= jim, eve, nikki

<>可解释:

a  = find results that are JUST a
ac = find results that contain ONLY an a AND c
b* = find results which contains a b
ac*  find results that contain at least an a and a c

可以看出,类型一栏可包含不同订单中的类型(并非总会增加)。

我确信,可以通过将类别栏分为<代码>类型_a、 类型_b来做到这一点。等等,然后在每一条中有<条码>bool。但是,这可以达到<条码>z,我不想在桌旁再 2626条col!

如果希望得到任何帮助的话,那是有可能在单一询问中这样做的。

关于标题,我不知道什么叫它。

Answer 1

http://dev.mysql.com/doc/refman/5.1/en/regexp.html”

SELECT name FROM your_table WHERE
     type RLIKE  ^a+$                             -- only  a 
  OR type RLIKE  ^((a[ac]*c)|(c[ac]*a))[ac]*$     -- only & both  a  AND  c 
  OR type RLIKE  b                                -- at least one  b 
  OR type RLIKE  (a.*c)|(c.*a)                    -- at least  a  and  c 
;

Answer 2

-- ac*, ac, a, b*

SELECT  *
FROM    mytable
WHERE   (
        type RLIKE  a 
        AND type RLIKE  c 
        )
        OR
        (
        type RLIKE  a 
        AND type RLIKE  c 
        AND NOT type RLIKE  [^ac] 
        )
        OR
        (
        type RLIKE  a 
        AND NOT type RLIKE  [^a] 
        )
        OR
        (
        type RLIKE  b 
        );

This won t use any indexes though.

如果您的表格为<代码>MyISAM,你可以储存这样的类型:

id   name     type
7    nikki    a c

(note the spaces) and use FULLTEXT functionality:

CREATE FULLTEXT INDEX fx_mytable_type ON mytable (type);

-- ac*
SELECT  *
FROM    mytable
WHERE   MATCH(type) AGAINST  +"a" + "c"  IN BOOLEAN MODE);


-- ac
SELECT  *
FROM    mytable
WHERE   MATCH(type) AGAINST  +"a" + "c"  IN BOOLEAN MODE);
        AND NOT TYPE RLIKE  [^ ac]  -- note the space

Set @ft_min_word_len = 1 for this to work.

友情链接