mysql - 如何获取特定 where 条件的前 "n"行数的某些状态？

Question

我有一个这样的查询

SELECT DISTINCT Id, AppStatusId,
IF ( (AppStatusId = 80),"1","2" ) as res 
#(here i need res as "1" if AppStatusId = 80 for first 100 rows  )
FROM App
WHERE
AppStatusId = 80
or 
AppTypeId = 100

查询返回 1000 行，我希望将前 100 行的 res 列设置为 1，条件 AppStatusId = 80。我期待以下结果

Id, AppStatusId,res
14343  ,80 ,    ,1
2234   ,80 ,    ,1
3232   ,80 ,    ,1
..................
..................
..................
..................
8975,  80,      ,1 # 100th row
3232,  80,      ,0
102,   80,      ,0
103,   80,      ,0
..................
..................
222,   55,      ,0 ( becuase of  or AppTypeId = 100 in where condition)

Answer 1

SELECT
    Id
    ,AppStatusId
    ,if(AppStatusId = 80 AND RowNumber <= 100, 1, 0) as res
FROM
    (
       SELECT
          *
          ,(@rn:= if(AppStatusId = 80, @rn + 1,@rn)) as RowNumber
       FROM
          App a
          CROSS JOIN (SELECT @rn:=0) var
       WHERE
          a.AppStatusId = 80
          OR a.AppTypeId = 100
       ORDER BY
          Id, AppstatusId...., whatever you want for the first 100 records
    ) t

这应该可以帮助您实现目标。它会生成一个行号，该行号仅在 AppStatusId = 80 时递增，并且它允许您选择任意记录顺序，因此 AppStatusId = 80 的前 100 条记录没有如果您不希望它们连续，则可以连续。如果您确实简单地这样订购，它仍然有效。