我在做查询。语句如下。
select count(*) as count ,cm_member.mem_hotel from cm_member where cm_member.mem_hotel like(select htl_sid from cm_hotel where cm_hotel.htl_country='20090312708827321319' AND cm_hotel.htl_city='20090312339003708918' AND cm_hotel.sys_deleted='N' AND htl_status='Published' AND htl_draft='') group by cm_member.mem_hotel order by cm_member.mem_hotel ASC其中
(select htl_sid from cm_hotel where cm_hotel.htl_country='20090312708827321319' AND cm_hotel.htl_city='20090312339003708918' AND cm_hotel.sys_deleted='N' AND htl_status='Published' AND htl_draft='')
语句查出来的是酒店的ID号,而在cm_member这张表中mem_hotel字段,存的就是酒店ID,不过,这个mem_hotel可以存好几个酒店ID,例如,酒店ID1,酒店ID2
他们中间用逗号分隔,
所以,我做查询,就想第一步得到酒店ID,然后在cm_member表中mem_hotel字段用like来比较酒店ID。但是我用LIKE报的错误是: Subquery returns more than 1 row 翻译过来是:子查询返回多个行1。大家来看看,是不是嵌套查询语句中不能用like,还是我写错了,大家帮忙纠正下,也让我学习学习。谢谢。
select count(*) as count ,cm_member.mem_hotel from cm_member where cm_member.mem_hotel like(select htl_sid from cm_hotel where cm_hotel.htl_country='20090312708827321319' AND cm_hotel.htl_city='20090312339003708918' AND cm_hotel.sys_deleted='N' AND htl_status='Published' AND htl_draft='') group by cm_member.mem_hotel order by cm_member.mem_hotel ASC其中
(select htl_sid from cm_hotel where cm_hotel.htl_country='20090312708827321319' AND cm_hotel.htl_city='20090312339003708918' AND cm_hotel.sys_deleted='N' AND htl_status='Published' AND htl_draft='')
语句查出来的是酒店的ID号,而在cm_member这张表中mem_hotel字段,存的就是酒店ID,不过,这个mem_hotel可以存好几个酒店ID,例如,酒店ID1,酒店ID2
他们中间用逗号分隔,
所以,我做查询,就想第一步得到酒店ID,然后在cm_member表中mem_hotel字段用like来比较酒店ID。但是我用LIKE报的错误是: Subquery returns more than 1 row 翻译过来是:子查询返回多个行1。大家来看看,是不是嵌套查询语句中不能用like,还是我写错了,大家帮忙纠正下,也让我学习学习。谢谢。
where charindex(',' + (select top 1 htl_sid from cm_hotel where cm_hotel.htl_country='20090312708827321319' AND cm_hotel.htl_city='20090312339003708918' AND cm_hotel.sys_deleted='N' AND
htl_status='Published' AND htl_draft='')+ ',' , ',' + cm_member.mem_hotel + ',') > 0 group by cm_member.mem_hotel order by cm_member.mem_hotel ASC
如果含有多个记录like铁定不行啊like改成in看看呢
参照楼上的,sql语句报错,我实在是搞不懂,怎么错了,用in 也不行。
cc,则上面的 sql就是 先在 mem_hotel 两边加上分隔符,也就是逗号,变成 ,aa,bb,cc, 和
,kk,bb,ee, 然后htl_sid 两边加上分隔符 变成 ,cc, ,最后看看 ,cc, 存在哪个
mem_hotel 中 ,显然结果是存在于 ,aa,bb,cc, 中
是这样分析的,但是我运行sql语句,就报的是select top 1这里的错误,如果我把top 1拿掉,
错误就是[Err] 1305 - FUNCTION cms_edm_new.CHARINDEX does not exist我不明白的是,加了个top 怎么就有问题了
inner join cm_hotel b
on charindex(b.htl_sid,a.mem_hotel)>0
group by a.mem_hotel order by a.mem_hotel ASC
mysql怎么转换 ?不懂啊,