我试图限制每组输出的行。这是怎么做的。解释以下情况:
表1:Customer_DetailsCustomer IDVandeur pid_01Vandeur pid_02Vandeur pid_03Vandeur pid_04Vandeur pid_01Vandeur pid_02Vandeur pid_03Vandeur pid_04Vandeur pid_05
表2:Month_DetailsMonth 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/