sql - SQLite查询以查找表中单独存储的项目的特定顺序

Question

假设我有一个要乘火车的SQLite商品数据库。
我的数据库是这样组织的：

Merchandise table
Merchandise ID
Name (some other info)

Train table
Train ID
(some other info)

Loading table
Train ID
Merchandise ID
Car number (0 for the locomotive and going down from there).

如果要查找特定的汽车序列，什么是正确的查询。假设我知道什么火车上有两辆紧挨着的玩具车，接着是一辆西红柿和一辆土车。

（我的应用程序不是真的那样，但这使我真正需要的东西更简单了……）

编辑...头有些划伤后...

这是我到目前为止的内容：

SELECT * FROM
 (SELECT * FROM merchandise JOIN loading ON merchandise.merchandise_id=loading.merchandise_id WHERE name="toys") AS car1
  JOIN (SELECT * FROM merchandise JOIN loading ON merchandise.merchandise_id=loading.merchandise_id WHERE name="toys") AS car2
    ON (car2.position=car1.position+1) AND car1.train_id=car2.train_id
  JOIN (SELECT * FROM merchandise JOIN loading ON merchandise.merchandise_id=loading.merchandise_id WHERE name="tomatoes") AS car3
    ON (car3.position=car2.position+1) AND car2.train_id=car3.train_id
  JOIN (SELECT * FROM merchandise JOIN loading ON merchandise.merchandise_id=loading.merchandise_id WHERE name="dirt") AS car4
    ON (car4.position=car3.position+1) AND car3.train_id=car4.train_id

我需要做更多测试，但这似乎正常。

但是，不确定是否有更有效的方法（是否需要查看“ EXPLAIN QUERY PLAN”的详细信息）

Answer 1

我真的不确定是否有一个很好的方法可以完全通过sql来完成此任务。但是，这是一个能够使它正常工作的工具。话虽这么说，但我在查询后会提到一些警告：

select
    sub.train_id
from
    (   -- We need a sub-query here to we can sort
        -- *before* the group by (and group_concat)
        select
            l.train_id,
            m.name
        from
            loading l
            join merchandise m
                on l.merchandise_id = m.id
        order by
            l.car_no
    ) as sub
group by
    sub.train_id
having
    -- If you want no other items, you'll want to remove the '%s's
    -- Also, you could use ids instead of names to make this a little more robust
    group_concat(sub.name) like '%toys,toys,tomatoes,dirt%'

好的，这是怎么回事：


group_concat可能仅适用于特定长度的序列。我没有在sqlite中使用过很多，但是mysql具有（或不久前）有256个字符的限制。我不确定sqlite有什么限制，但是要牢记这一点。
尽管这可行，并且排序似乎保留在 group_concat（sqlite版本3.7.7.1）中，但是文档明确声明：“ The order of the concatenated elements is arbitrary”


话虽这么说，我可能只会尝试以编程方式找到它，但这似乎是一种对我有用的聪明方法。