select *
,b.COUNTRY||'-'||b.CITY as '地区'
,row_number () over (order by cost) as '排名'
from PRODUCT a
left join PRODUCER b
on a.producerid=b.producerid
帆软自带的数据库是 SQLite 没有 rownum 概念，可取巧实现以上排序的功能。
代码如下：
select *, pdr1.COUNTRY||'-'||pdr1.CITY as '地区', ((select count(*) from PRODUCT pdt2 where pdt2.COST<pdt1.COST) + 1) as '排名' from PRODUCT pdt1 left join PRODUCER pdr1 on pdt1.PRODUCERID=pdr1.PRODUCERID order by pdt1.COST asc
排序思路：计算某个产品的 COST 大于其他多少个产品的 COST。
这个*得指名A.*还是B.*
sqlite 貌似没有row_number ；
从你的sql来看，是不是需要一个“排名”字段来存储order by cost后的序号？
select
(select count(*) from (select *
on a.producerid=b.producerid order by a.cost) b where a.cost >= b.cost) as "排名"
,*
from (select *,b.COUNTRY||'-'||b.CITY as '地区'
from PRODUCT a left join PRODUCER b
on a.producerid=b.producerid order by a.cost) as a
order by cost;
不知道能不能满足?
sqlserver是支持row_number的，
你这是什么数据库。
https://help.finereport.com/doc-view-344.html
1min目标场景问卷
立即参与
