LongStaying
2019-04-08 14:30
采纳率: 83.3%
浏览 396

关于Mysql的Limit转换为TOP语句过程中出现语法错误问题

mysql:

    select ordersitem.flowerid,flowername,sum(ordersitem.flowernum) as totalnum,orderdate,flowerunit 
    from flower,ordersitem,orders 
    where flower.flowerid=ordersitem.flowerid 
        and orders.id=ordersitem.ordersid 
    group by flowerid 
    order by totalnum desc 
    limit " + (pageNow - 1) * pageSize + "," + pageSize

然后转为我的想法是先将:

select ordersitem.flowerid,flowername,sum(ordersitem.flowernum) 
as totalnum,orderdate,flowerunit 
from flower,ordersitem,orders 
where flower.flowerid=ordersitem.flowerid 
and orders.id=ordersitem.ordersid 
group by flowerid order by totalnum desc

作为一个结果表,然后对这个结果表进行top操作:

SELECT top 2 * FROM (
select ordersitem.flowerid,flowername,sum(ordersitem.flowernum) 
as totalnum,orderdate,flowerunit 
from flower,ordersitem,orders 
where flower.flowerid=ordersitem.flowerid 
and orders.id=ordersitem.ordersid 
group by ordersitem.flowerid,flowername,orderdate,flowerunit 
)
WHERE ordersitem.flowerid NOT in (

SELECT top 0 flowerid FROM(
select ordersitem.flowerid,flowername,sum(ordersitem.flowernum) 
as totalnum,orderdate,flowerunit 
from flower,ordersitem,orders 
where flower.flowerid=ordersitem.flowerid 
and orders.id=ordersitem.ordersid 
group by ordersitem.flowerid,flowername,orderdate,flowerunit 
)
)
order by totalnum desc

然后就是一直语法错误,于是我就把下面分页的部分去掉了:

SELECT top 2 * FROM (
select ordersitem.flowerid,flowername,sum(ordersitem.flowernum) 
as totalnum,orderdate,flowerunit 
from flower,ordersitem,orders 
where flower.flowerid=ordersitem.flowerid 
and orders.id=ordersitem.ordersid 
group by ordersitem.flowerid,flowername,orderdate,flowerunit 
)

测试了一下这样的写法,还是说语法有错误,不知道错误在哪里....很迷茫,我还要不要继续从事计算机? 手动滑稽, 请大佬帮忙看看....

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • 聂晓宁_Cherry 2019-04-08 14:47
    已采纳

    可以尝试一下下面的SQL:
    SELECT top 2 t.* FROM (
    select ordersitem.flowerid,flowername,orderdate,flowerunit,sum(ordersitem.flowernum) as totalnum
    from flower,ordersitem,orders
    where flower.flowerid=ordersitem.flowerid
    and orders.id=ordersitem.ordersid
    group by ordersitem.flowerid,flowername,orderdate,flowerunit ) t
    order by t.totalnum desc
    ;

    点赞 评论
  • 白silence 2019-04-08 14:42

    top语句在mysql 中不会生效

    点赞 评论
  • qq_34814050 2019-04-08 15:27

    select top pageSize t.* from( select rumnum() as rowindex,ordersitem.flowerid,flowername,sum(ordersitem.flowernum) as totalnum,orderdate,flowerunit
    from flower,ordersitem,orders
    where flower.flowerid=ordersitem.flowerid
    and orders.id=ordersitem.ordersid
    group by flowerid
    order by totalnum desc ) as t
    where t.rowindex>(pageNow - 1) * pageSize

    点赞 评论
  • csdn_780 2019-04-08 15:45

    msql中 top 是不能用的, 如果说想取前两条的话用 limit 0,2

    点赞 评论

相关推荐 更多相似问题