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
)
测试了一下这样的写法，还是说语法有错误，不知道错误在哪里....很迷茫，我还要不要继续从事计算机？ 手动滑稽， 请大佬帮忙看看....
可以尝试一下下面的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
;
top语句在mysql 中不会生效
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
msql中 top 是不能用的, 如果说想取前两条的话用 limit 0,2