我有一个mysql查询,我想在MS Access中使用它。
SELECT company_name, agent_id FROM
( SELECT company_name, agent_id, @rn := IF(@prev = agent_id, @rn + 1, 1)
AS rn, @prev := agent_id FROM users
JOIN (SELECT @prev := NULL, @rn := 0) AS vars
ORDER BY agent_id DESC, company_name)
AS T1
WHERE T1.agent_id is not null and rn <= 3;
我一直试图在Access中运行它,但它抛出了错误:“from子句中的语法错误”。
发布于 2018-06-04 05:23:21
在Microsoft Access中,我们可以尝试在WHERE子句中使用"TOP n“相关子查询。
我会试着这样做:
SELECT t.company_name
, t.agent_id
FROM [users] AS [t]
WHERE t.company_name IN
( SELECT TOP 3 u.company_name
FROM [users] AS [u]
WHERE u.agent_id = t.agent_id
ORDER
BY u.company_name
)
ORDER
BY t.agent_id DESC
, t.company_name
https://stackoverflow.com/questions/50670497
复制相似问题