如何在mysql中基于agentID和排名获取三个记录 [英] How to get three records based on agentID and rank in mysql

问题描述

如何获取三个记录，即他的记录，之前的排名记录和之后的排名记录 我在小提琴下面有SP:

How to get the three records i.e his record,previous rank record and after rank record I had SP in below fiddle:

 DELIMITER $$

 CREATE DEFINER=`ntc`@`%` PROCEDURE `stckrank`()
BEGIN
SELECT AgentID, 
    current_day_amount, 
    month_amount, 
    year_amount,
    @Position:=@Position + 1 AS `Rank`
FROM
(
SELECT just_agent.AgentID, 
        total_current_date.Totoalamountperday AS current_day_amount, 
        total_current_month.Totoalamountpermonth AS month_amount, 
        total_year.Totoalamountperyear AS year_amount
     FROM
(
    SELECT DISTINCT AgentID
    FROM collection
) just_agent
LEFT OUTER JOIN
(
    select AgentID, SUM(AmountRecevied) as Totoalamountperday
    from collection
    where  day(Date_Time) = day(CURRENT_DATE())
    group by AgentID
) total_current_date
ON just_agent.AgentID = total_current_date.AgentID
LEFT OUTER JOIN
(
    select AgentID, sum(AmountRecevied) as Totoalamountpermonth
    from collection
    where  date_time between DATE_FORMAT(NOW(), '%Y-%m-01') and LAST_DAY(now() - interval 0 month) 
    group by AgentID
) total_current_month
ON just_agent.AgentID = total_current_month.AgentID
LEFT OUTER JOIN
(
    select AgentID, sum(AmountRecevied) as Totoalamountperyear
    from collection
    where  year(Date_Time) = YEAR(CURRENT_DATE()) 
    group by AgentID
) total_year
ON just_agent.AgentID = total_year.AgentID
ORDER BY total_year.Totoalamountperyear DESC
) Sub1
 CROSS JOIN (SELECT @Position:=0) Sub2;
END

输出:

agentiid dayamount monthamount yearamount rank
 2        3000       4000          7000   1
 1        2000      3000           5000   2
 3         200       300            500   3
 4         100       100            200    4

现在，如果我选择代理ID 2，我需要获得低于记录的记录，而没有高于记录的记录，那么应该显示null或0

now if i select agent id 2 i need to get below record and no record for above so null or 0 should show

选择代理ID -2 需要展示或获取 他的排名记录和之后的记录

select agent id -2 Need to show or get his rank record and after record

agentiid dayamount monthamount yearamount rank
    2        3000       4000          7000   1
    1        2000      3000           5000   2

推荐答案

您可以按照排名条件将表连接到自身:

You can do it joining the table to itself on a rank condition:

SELECT t2.*
FROM yourTable t1
JOIN yourTable t2
ON ABS(t1.rank - t2.rank) <= 1.5 --is this what you want ?
WHERE t1.agentiid = 2

此SQL小提琴提供了所需的结果.

使用此联接，原始表中的每一行(始终)与自身关联，并与具有上一个和下一个等级的行关联(如果存在).

With this join, each row in your original table is associated to itself (always) and to the line with previous and next rank (if they exist).

这篇关于如何在mysql中基于agentID和排名获取三个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！