mysql - 如何限制每个特定结果组的 SQLite 结果？

Question

我在工作中遇到以下问题。我有一个包含不同列和几 100 000 行的大表。我只会发布我感兴趣的内容。

假设以下数据集

设备 ID、功能 ID、功能状态

1,        1,        0
1,        2,        0
1,        3,        1
1,        4,        1
1,        5,        1

2,        1,        1
2,        2,        0
2,        3,        0 
2,        4,        1
2,        5,        0

3,        1,        1
3,        2,        1
3,        3,        1
3,        4,        1
3,        5,        1

4,        1,        0
4,        2,        0
4,        3,        1
4,        4,        0
4,        5,        0

我需要选择功能状态 = 1 的行，但只选择每个设备 ID 的前 2 行。

查询的结果应该是:

1,3,1
1,4,1
2,1,1
2,4,1
3,1,1
3,2,1
4,3,1

我试过这样的:

SELECT brdsurfid,featureidx,FeatStatus FROM Features F1 WHERE FeatStatus = 1 AND
  (SELECT COUNT(*) FROM Features F2 
  WHERE F2.FeatureIdx <= F1.FeatureIdx AND F2.FeatStatus = 1) < 2
  ORDER BY BrdSurfId,FeatureIdx;

这是我在另一个回复中发现的，但效果不佳。

我知道我需要混合使用 LIMIT 或 COunt(*) 和一些嵌套选择，但我无法弄清楚。谢谢

Answer 1

这可能不是一种非常有效的方法，但我认为没有更好的 sqlite 解决方案(涉及单个查询):

SELECT *
FROM t t0
WHERE FeatureStatus AND
      (SELECT count(*)
       FROM t t1
       WHERE t0.DeviceID=t1.DeviceID
         AND FeatureStatus
         AND t1.FeatureId<t0.FeatureId
      )<2;

我假设该表名为 t。这个想法是找到状态为 1 的所有特征，然后为每个特征计算同一产品具有该状态的先前特征。如果该计数大于 2，则拒绝该行。