需求是这样的,我们有个排名列表,需要查询出当前条件下的指定人员的信息是在排序后的第几条。
两种sql解决方案
SELECT a.* FROM
(SELECT
*,
(@i :=@i + 1) AS No
FROM
jira_issue_statistics
, (SELECT @i:=0) AS it
WHERE
year = 2018 and mounth = 10
ORDER BY
count DESC) as a
WHERE a.reporter = 'jianglei_tmp'
方案2:
SELECT year,reporter,count,rowno FROM (
select * from
(
select * from(
SELECT year, mounth, reporter, count,(@rowno:=@rowno+1) as rowno FROM jira_issue_statistics csss where year = 2018 and mounth = 10 ORDER BY count desc
) bcc,(select (@rowno:=0)) b ORDER BY count DESC
) tempst where tempst.year = 2018 and tempst.mounth = 10 ORDER BY count desc
) c
WHERE c.reporter='jianglei_tmp';