这个Oracle语句转成 mysql语句怎么写 ，我每次运行都有错误

select * from (select rownum rn,g.*,c.name cname from shop_goods g,shop_category c where g.categoryid=c.id

and g.categoryid=#{categoryid}



and g.name like #{name}

) r
where r.rn between (#{page}-1)*#{pageSize} and #{page}*#{pageSize}

将里面的rownum去掉，里面的查询使用limit，不需要外面的嵌套就行了

 select g.*,c.name cname from shop_goods g,shop_category c where g.categoryid=c.id
and g.categoryid=#{categoryid} and g.name like #{name} limit  index,pagesize

Oracle语句和mysql语句基本上是相同的,不同的是它们使用的关键字有些不同的,关键字不同用法就不同了。你只要比较一下就知道怎么改了。

参照以下方式改

select g.*,c.name cname from shop_goods g,shop_category c where g.categoryid=c.id and g.categoryid=#{categoryid} limit (#{page}-1)*#{pageSize}+1 ,#{pageSize}

--如果要显示伪列rn序号,MySQL要这样用
SET @rowindex=0;
SELECT SQL_CALC_FOUND_ROWS @rowindex:=@rowindex+1 AS rn,g.*,c.name cname from shop_goods g,shop_category c where g.categoryid=c.id and g.categoryid=#{categoryid} limit (#{page}-1)*#{pageSize}+1 ,#{pageSize}

eg:
select Sname
from Student
where Sno IN
(select Sno
from SC
where Cno='2'); ——括号里为内层查询

OR

select Student.Sno,Sname
from Student,SC,Course
where Student.Sno=SC.Sno AND
SC.Cno=Course.Cno AND
Course.Course='计算机';
两种方式都可使用