mysql 一个字段多个值查询

代码如下:

SELECT

*

FROM

news_extinfo

where

(ExtID

=

1

and

OptionValue

=

0)

or

(ExtID

=

2

and

OptionValue

=

0

)

or

(ExtID

=

3

and

OptionValue

=

0

)

group

by

NewsID

having

count(*)=3

此处

count(*)

=

3

表示的意思是

在查询的结果中，只查出按group

分组之后，每个组的有3条数据的结果集

select id,name,

coalesce((select id from table1 where name=left(tt2.name,1)),-1) nameID1,

coalesce((select id from table1 where name=mid(tt2.name,3,1)),-1) nameID2,

coalesce((select id from table1 where name=mid(tt2.name,5,1)),-1) nameID3,

coalesce((select id from table1 where name=mid(tt2.name,7,1)),-1) nameID4

from table2

如果保证table2.name中无空格且table1.name长度都为1，那以下也可以用

select table2.id,table2.name,

max(case when instr(table2.name,table1.name)=1 then table1.id else -1 end) naemID1,

max(case when instr(table2.name,table1.name)=3 then table1.id else -1 end) naemID2,

max(case when instr(table2.name,table1.name)=5 then table1.id else -1 end) naemID3,

max(case when instr(table2.name,table1.name)=7 then table1.id else -1 end) naemID4

from table2 left join tt1 on instr(table2.name, table1.name)>0

group by table2.id, table2.name

如果table2.name有空格或table1.name长度不都为1，也可以使用

select table2.id,table2.name,

max(case when length(left(table2.name,instr(table2.name, table1.name)))-length(replace(left(table2.name,instr(table2.name,table1.name)), ',', ''))=0 

then table1.id else -1 end) naemID1,

max(case when length(left(table2.name,instr(table2.name, table1.name)))-length(replace(left(table2.name,instr(table2.name,table1.name)), ',', ''))=1 

then table1.id else -1 end) naemID2,

max(case when length(left(table2.name,instr(table2.name, table1.name)))-length(replace(left(table2.name,instr(table2.name,table1.name)), ',', ''))=2 

then table1.id else -1 end) naemID3,

max(case when length(left(table2.name,instr(table2.name, table1.name)))-length(replace(left(table2.name,instr(table2.name,table1.name)), ',', ''))=3 

then table1.id else -1 end) naemID4

from table2 left join table1 on instr(table2.name, table1.name)>0

group by table2.id, table2.name

length(left(table2.name,instr(table2.name, table1.name)))-length(replace(left(table2.name,instr(table2.name,table1.name)), ',', ''))这个表达式是找到相应的table1.name前面的逗号个数即对应table1.name的位置

select * from 表 where n_id in

(select s_name from 表

group by s_name,n_money,s_zhipiao,s_date,s_lixi

having count(s_name)>1)

---