SELECT bid,tid,pid,need FROM( select bid,tid,pid,need,ROW_NUMBER()OVER (ORDER BY need desc) RN from B where bid=4 and pid=1 and exists (select 1 from A where bid=B.bid AND tid=B.tid AND able=1) )WHERE RN<=100
报错啊= = 不能执行#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( ORDER BY need desc ) RN from B where bid = 4 and pid = 1 and exist' at line 1
你是mysql的服务器?这是oracle区
试试这个,mysql应该是用limit就可以,不需要分析函数 SELECT bid,tid,pid,need FROM( select bid,tid,pid,need where bid=4 and pid=1 and exists (select 1 from A where bid=B.bid AND tid=B.tid AND able=1) ORDER BY need desc )limit 0,100
竟然把From B写丢了 SELECT bid,tid,pid,need FROM( select bid,tid,pid,need from B where bid=4 and pid=1 and exists (select 1 from A where bid=B.bid AND tid=B.tid AND able=1) ORDER BY need desc )limit 0,100
select bid,tid,pid,need,ROW_NUMBER()OVER (ORDER BY need desc) RN from B
where bid=4 and pid=1
and exists (select 1 from A where bid=B.bid AND tid=B.tid AND able=1)
)WHERE RN<=100
报错啊= =
不能执行#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( ORDER BY need desc ) RN from B where bid = 4 and pid = 1 and exist' at line 1
SELECT bid,tid,pid,need FROM(
select bid,tid,pid,need
where bid=4 and pid=1
and exists (select 1 from A where bid=B.bid AND tid=B.tid AND able=1)
ORDER BY need desc
)limit 0,100
这个真的可以执行么?我跑了好久都没跑出来= =数据库规模: B表16W条,其中bid=4约有10W条; A表4W条左右。。我直接在mysql的命令行中跑,几分钟还在跑、、- -
SELECT bid,tid,pid,need FROM(
select bid,tid,pid,need
from B
where bid=4 and pid=1
and exists (select 1 from A where bid=B.bid AND tid=B.tid AND able=1)
ORDER BY need desc
)limit 0,100