我創建了一個接收文本陣列作為引數的 postgres 函式。對于陣列中的每個文本，我想檢查查詢中的任何列是否與 like 子句匹配。
是否可以在 for 回圈中聚合這樣的 where 子句？
例如為了澄清，我有上面的表格：
lessons
---- ----------- -----------------
| id | name | teacher_id |
---- ----------- -----------------
| 1 | English | 1 |
---- ----------- -----------------
| 2 | Spanish | 2 |
---- ----------- -----------------
Teachers
---- -----------
| id | name |
---- -----------
| 1 | Teacher 1 |
---- -----------
| 2 | Teacher 2 |
---- -----------
我想獲取引數 text[] 并且對于陣列中的每個文本，我想在兩個表中的列之間執行 OR 子句并回傳 jsons( [{"id": 1, "teacher": {"id":1, "name": "Teacher1"}}])陣列
例如，如果引數是 ["lish", "er"] 我希望它執行：
where
lessons.name like '%lish%' or teachers.name like '%lish%'
and
lessons.name like '%er%' or teachers.name like '%er%'
id 為 1 的老師將回傳。
問題是我事先不知道引數，所以這就是為什么我認為我需要一個 FOR 回圈。
另外，如何進行查詢以回傳每個教師將成為每節課的內部 json 物件的 json 陣列？
如果是這樣，將不勝感激一些例子。謝謝！
uj5u.com熱心網友回復：
您不需要像 plpgsql 中的類似回圈的函式來獲得預期的結果。基本sql查詢將更有效：
SELECT l.id, l.name, t.id, t.name
FROM lessons AS l
INNER JOIN teachers AS t
ON t.id = l.teacher_id
INNER JOIN unnest( array['lish', 'er']) WITH ORDINALITY AS c(cond, id)
ON t.name ~ c.cond OR l.name ~ c.cond
GROUP BY l.id, l.name, t.id, t.name
HAVING count(*) = max(c.id)
- 首先，此查詢連接兩個表
lessons并teachers在teacher_id. unnest然后通過函式將它與輸入陣列的每個元素連接起來。- 對于連接表
lessons和的每一行，teachers使用正則運算式~運算子將兩個名稱與輸入陣列元素進行比較。 - 然后將選定的行分組在一起，以便計算與所有陣列元素匹配的行數。此檢查在
HAVING子句中執行，其中count(*)是相同（lessons.id，teacher.id）元組的結果行數，并將其與陣列中的元素數進行比較max(c.id)
如果要回傳 jsonb 陣列，只需格式化查詢結果：
SELECT json_agg(c.result)
FROM (
SELECT (json_build_object('lesson_id', l.id, 'lesson_name',l.name, 'teacher', json_build_object('teacher_id', t.id, 'teacher_name', t.name))) AS result
FROM lessons AS l
INNER JOIN teachers AS t
ON t.id = l.teacher_id
INNER JOIN unnest( array['lish', 'er']) WITH ORDINALITY AS c(cond, id)
ON t.name ~ c.cond OR l.name ~ c.cond
GROUP BY l.id, l.name, t.id, t.name
HAVING count(*) = max(c.id)) AS c
在dbfiddle中查看完整的測驗結果
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/531588.html