我试图限制每组输出的行。这是怎么做的。解释以下情况:

表1:Customer_Details

Customer IDVandeur pid_01Vandeur pid_02Vandeur pid_03Vandeur pid_04Vandeur pid_01Vandeur pid_02Vandeur pid_03Vandeur pid_04Vandeur pid_05

表2:Month_Details

Month ID Jan pid_01 Jan pid_02 Jan pid_03 Jan pid_04 Feb pid_01 Feb pid_02 Feb pid_03 Feb pid_04 Feb pid_05

我的输出要求是-我每个月只需要customer Vandeur的前3个ID

样品O / P:

Customer Month IDVandeur Jan pid_01Vandeur Jan pid_02Vandeur Jan pid_03Vandeur Feb pid_01Vandeur Feb pid_02Vandeur Feb pid_03

有人可以建议我实现此目标的方法。

最佳答案

Mysql不像其他RDBMS一样支持窗口函数,要获得每组n个结果,可以使用用户定义的变量

 SELECT DISTINCT c.Customer,m1.`Month`,c.ID
 FROM Customer_Details c
 JOIN (
  SELECT m.*,
   @r:= CASE WHEN @g = `Month` THEN  @r +1 ELSE 1 END  rank,
   @g:=`Month`
    FROM `Month_Details` m ,
    (SELECT @g:=NULL,@r:=NULL) r
    ORDER BY m.`Month` DESC ,REPLACE(m.ID,'pid_','') * 1
     ) m1 ON(c.ID = m1.ID)
  WHERE m1.rank <=3
 AND c.Customer= 'Vandeur'
 ORDER BY m1.`Month` DESC ,REPLACE(m1.ID,'pid_','') * 1


DEMO

关于mysql - 使用联接限制每个组在SQL中的行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/29546747/

10-12 14:15