PostgreSQL値による抽出

Question

前提

Postgresqlでデータベースの参照SQLを書いています。

実現したいこと

テーブル1

id	date	total
1	hoge	100

テーブル2

id	name	price
1	a	50
2	b	30
3	c	100
4	d	110
5	e	40

テーブル2の各レコードのpriceの和が、テーブル1のid1のtotalの値以下のレコードを抽出したいです。

結果1

id	name	price
1	a	50
2	b	30

結果2

id	name	price
3	c	100

結果3

id	name	price
2	b	30
5	e	40

上記のようにテーブル2の各レコードのpriceの和が100以下の組み合わせは複数存在すると思いますが
抽出された各レコードのpriceの和がtotal以下であればどの組み合わせでも構いません。

当方SQLに不慣れなもので実現可能化含めて、よろしくお願いします

補足情報（FW/ツールのバージョンなど）

環境
PostgreSQL 12.6

Comments

[Orlofsky]

質問は修正できます。
現行のうまく実行できていないSELECT文を質問に Markdownの[コード]で追記してください。
https://teratail.com/help/question-tips#questionTips37

[YT0014]

id name price id name price
1 a　　 50　 2 b　　30
3 c　　100　null null null
2 b　　 30　 5 e　　40
のように、横に並べる形式での取得で、仕様を満たすことはできますか？

Answer 1

以下のクエリでは、priceの和が100以下になるすべての組み合わせを取得できます。

WITH RECURSIVE 
X (total) AS (
  SELECT total FROM tbl1 WHERE id = 1
),
Y (ids, total) AS (
    SELECT ARRAY[id], price
    FROM tbl2
    WHERE price <= (SELECT total FROM X)
  UNION ALL
    SELECT ids || id, Y.total + T.price
    FROM Y
    JOIN tbl2 T ON Y.ids[ARRAY_LENGTH(ids, 1)] < T.id
    WHERE Y.total + T.price <= (SELECT total FROM X)
)
SELECT A.grp, A.id, B.name, B.price
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY ARRAY_LENGTH(ids, 1)), UNNEST(ids)
  FROM Y 
) A (grp, id)
JOIN tbl2 B ON A.id = B.id

結果は以下のようになります。

grp	id	name	price
1	1	a	50
2	2	b	30
3	3	c	100
4	5	e	40
5	1	a	50
5	2	b	30
6	1	a	50
6	5	e	40
7	2	b	30
7	5	e	40

Answer 2

テーブル同士の組み合わせにはCROSS JOINを使うと実現できます。

SELECT
	cmb1.id,
	cmb1.price,
	cmb2.id,
	cmb2.price
FROM
	table2 AS cmb1
	CROSS JOIN
		table2 AS cmb2

ただ、どんな出力にしたいかの具体例を出さないと、これ以上の回答は難しいかと思います。特に質問内容の下記の部分が不明瞭です。

• 「priceの組みあわせ」とは和なのか積なのかその他なのか
• 「抽出できる組み合わせは毎回ランダムでも変わりません」の意図

【追記】
質問の要件をちゃんと満たせていませんが、2組限定で横持ちであれば、下記のクエリで取得可能になります。ご参考までに。

SELECT
	cmb1.id AS id1,
	cmb1.name AS name1,
	cmb2.id AS id2,
	cmb2.name AS name2,
	cmb1.price + cmb2.price AS sum
FROM
	TABLE2 AS cmb1
	CROSS JOIN
		TABLE2 AS cmb2
WHERE
	cmb1.price + cmb2.price <= (
		SELECT total FROM TABLE1 WHERE id = 1
	)

【出力】

id1	name1	id2	name2	sum
1	a	1	a	50
1	a	2	b	80
1	a	5	e	90
2	b	1	a	80
2	b	2	b	30
2	b	5	e	70
3	c	3	c	100
5	e	1	a	90
5	e	2	b	70
5	e	5	e	40

Comments

[退会済みユーザー]

マトリックス表よりも実際に取得したいレコード一覧のほうが
取得したい情報のイメージが湧きそうですね。

[sasakiyo]

不明瞭な部分があったので修正しました。
そもそも可能なのかどうか、、、

[Freedom645]

質問内容の修正ありがとうございました。大分イメージがつかめました。

ただ、出力例を見ると私が考慮していなかった「2組以外の場合はCROSS JOINでは出来ない」というのが判明しました。全組み合わせを正確に取るには、neko_the_shadowさんの記載された再帰クエリで取得する方法になるかと思います。

2組限定で良ければ、回答に追記した方法となります。