SQL高级模糊查询语句 高手进来
发布网友
发布时间：2022-04-25 00:55
共2个回答
热心网友
时间：2022-04-07 18:47
张三
张三
张三1
张三2
下面的语句就能列出你要的结果（张三有重复）。
Select data_id,a.data_name,class_id,server_id,data_hits,data_elite,data_status,data_inputer,data_color from adnim_data a where exists (select * from(select data_name from adnim_data group by data_name having count(data_name) > 1) as b where a.data_name like data_name+'%')
但如果数据如下
张三
张三1
张三2
上面的语句就不能列出来(张三无重复)，不能用group by
你必须先去除1,2,3之类的后缀才可以列,或者你可以写个函数取掉所有名称中的数字后，再进行group by，即可解决
比如
Select data_id,data_name,class_id,server_id,data_hits,data_elite,data_status,data_inputer,data_color
from adnim_data
where data_name in
(select data_name from adnim_data
group by replace(replace(replace(data_name,'1',''),'2',''),'3','')
having count(*) > 1)
------------------------
后面的语句经过修改，应该为这样
Select data_id,data_name,class_id,server_id,data_hits,data_elite,data_status,data_inputer,data_color
from adnim_data
where data_name like
(select replace(replace(replace(data_name,'1',''),'2',''),'3','') from adnim_data
group by replace(replace(replace(data_name,'1',''),'2',''),'3','')
having count(*) > 1) + '%'
热心网友
时间：2022-04-07 20:05
