SQLite3でこんな便利なSQLはできますか?

質問

こんにちは。
SQLite3で次のようなクエリを書けるかどうかご存じの方、教えてください。

文房具の商品レコードを格納したテーブルがあります。

えんぴつ50円、ノート100円、ホチキス150円といった具合です。このテーブルから無作為にレコードを取り出し続けたいのですが、取り出した複数レコードの金額の合計が1000円を超えたところでストップしたいです。1050円でも1200円でもいいのですが、超えたところで止めたいのです。

インターフェイスとしてPHPを予定していて、そこででごにょごにょすればできるのですが、そもそもSQLの時点でこうしたことができるのかご存じの方教えてください。

回答No.1

mpro-gram · ベストアンサー

SQLiteには累積計算はないので、２つの同じ table を結合して、group 化という手順が必要だが、randam 順が同じ２つのテーブルを用意するには、temporary table を作る必要が有ると思う。
元のテーブルに primary keyカラム id が有るとして、以下の定義だとすると

create table bungu (
id integer primary key not null
,name char
,nedan int
);

create temporary table t_bungu
as select id ,nedan from bungu b order by random ;
-- 毎回作り直しすれば、毎回違う順になるので、temporary とする。

select id, name, nedan from bungu as a
where id in ( select id from t_bungu c join
( select a._rowid_ , sum(nedan) as x
from t_bungu a join t_bungu b on a._rowid_ >= b._rowid_
group by a._rowid_
having sum(nedan) > 1000
limit 1
) d on d._rowid_ >= c._rowid_
);
-- _rowid_ は、t_bunguテーブルに挿入された順。SQLite は、primary key がなくても、これで、行の特定が可能
-- 多重サブクエリは問題なく実行できます。実行速度的にどうなのかは、未検証。何千行もあるとかでなければ、そう待たされはしないと思うけど。

お礼

ありがとうございます!!!
すごいですね。技術的な面もそうですが、発想にも驚きました。

SQLiteの実力にも驚きます!

回答No.2

mpro-gram

あ、見直してたら、テーブルエイリアス名の抜けを発見。
sum(nedan)　のところ、２カ所とも、b エイリアスを加えて下さい
sum(b.nedan)

実行チェックまで行ってないのがばればれ。