SQLで使いそうな記法Tips

備忘録。今後使うかもしれない記法。
随時更新予定。
postgresで動作確認。

複数のカラムの中でいずれか特定の値で部分一致するレコードを検索する

例
xテーブル

id	memo_a	memo_b	memo_c
1	卵を買った	水泳をした	ドライブに行った
2	豆腐を買った	ランニングをした	釣りに行った

memo_a ~ memo_c のなかで、「釣り」を含むレコードを取り出したい場合

ORでつなげれば実現できるが、カラムが多いほど記述量が増えてしまう。
対象のカラムをすべて繋げてから検索すると１度で済む。

SELECT * FROM x
WHERE concat(memo_a, memo_b, memo_c) ILIKE '%$input%'

親テーブルのIDごとに連番を振りたい

例

1:N 関係の schoolsテーブル : studentsテーブル があるとする

students テーブル

id	school_id	name
1	1	田中
2	1	鈴木
3	2	本田
4	1	佐藤
5	2	後藤

これを親テーブルの school_id ごとに連番を算出したい場合、ROW_NUMBER関数を利用する

SELECT 
    s.*, 
    ROW_NUMBER() OVER(partition by s.school_id ORDER BY s.id) AS order
FROM students s

結果

id	school_id	name	order
1	1	田中	1
2	1	鈴木	2
4	1	佐藤	3
3	2	本田	1
5	2	後藤	2