android - 如果不使用 sqlite 中的 row_number() 函数，如何提供相同的函数？

Question

如果用户的手机支持 sqlite 3.25.0 或更高版本，则以下查询运行良好。否则，您可以猜到，查询不起作用。如何在不使用 row_number () 函数的情况下转换代码？

SELECT *
FROM
    (
        SELECT
            ROW_NUMBER() OVER (PARTITION BY recipe_name) AS rn,
            rt.*
        FROM
            SyncRecipeIngredientTable sr
            JOIN RecipeIngredientTable ri ON ri.recipe_ingredient_id = sr.recipe_ingredient_id
            JOIN RecipeTable rt ON rt.recipe_id = sr.recipe_id
        WHERE ri.recipe_item_name in ("patates", "soğan", "su")
        GROUP by rt.recipe_id
        HAVING COUNT(*) >= 3
    )
WHERE rn = 1

数据库的ER图：

预期结果：

标签： androidsqlsqliteflutter

Answer 1

我希望食谱有唯一的名称。如果是这样，则根本不需要外部查询：

SELECT rt.*
FROM SyncRecipeIngredientTable sr JOIN
     RecipeIngredientTable ri 
     ON ri.recipe_ingredient_id = sr.recipe_ingredient_id JOIN
     RecipeTable rt
     ON rt.recipe_id = sr.recipe_id
WHERE ri.recipe_item_name in ('patates', 'soğan', 'su')
GROUP by rt.recipe_id
HAVING COUNT(*) >= 3;

如果食谱可以有重复的名称，那么您可以使用相关子查询：

WITH rt AS (
      SELECT rt.*
      FROM SyncRecipeIngredientTable sr JOIN
           RecipeIngredientTable ri 
           ON ri.recipe_ingredient_id = sr.recipe_ingredient_id JOIN
           RecipeTable rt
           ON rt.recipe_id = sr.recipe_id
      WHERE ri.recipe_item_name in ('patates', 'soğan', 'su')
      GROUP by rt.recipe_id
      HAVING COUNT(*) >= 3
     )
SELECT rt.*
FROM rt
WHERE rt.recipe_id = (SELECT MAX(rt2.recipe_id)
                      FROM rt rt2
                      WHERE rt2.recipe_name = rt.recipe_name
                     );