Category Archives: DataBase

「Postgresql入門」CASE WHENで複数の列から最大値を取得するサンプル

例1
SELECT CASE WHEN a < b THEN b
ELSE a END AS maxVal
FROM TEST_TBL;

例2
SELECT CASE WHEN CASE WHEN a < b THEN b
ELSE a END < c THEN c
ELSE CASE WHEN a < b THEN b
ELSE a END END AS maxVal
FROM TEST_TBL;

「PostgreSQL入門」SUM(CASE WHEN)で条件付きで集計するサンプル

SQL構文
SELECT SUM(CASE WHEN ID >=21 THEN 1 ELSE 0 END ) AS ‘COLA’,
SUM(CASE WHEN ID <=20 THEN 1 ELSE 0 END) AS ‘COLB’
FROM DSTU_TBL

「MySQL入門」count(distinct case xxxで重複要素を排除してカウントする

例1
SELECT COUNT(DISTINCT CASE
WHEN addId >= 202 AND addId <= 258
THEN addId2 END)
FROM t_addressTbl

例2
SELECT SUM(CASE
WHEN addId >= 202 AND addId <= 258
THEN 1 ELSE 0 END)
FROM t_addressTbl

例3
SELECT column1,column2,column3,sum(column4)
FROM TEST_TBL
GROUP BY column1,column2,column3

11 / 11« 先頭...7891011