SQL 髢「謨ーシ壹げ繝ォ繝シ繝鈴未謨ー
讎りヲ
繧ー繝ォ繝シ繝怜喧縺励◆隍謨ー陦後ョ繝繝シ繧ソ縺ォ蟇セ縺励※蜊倅ク縺ョ蛟、繧定ソ斐☆髢「謨ー
窶サ蜊倅ク陦碁未謨ーシ晢シ題。後ョ繝繝シ繧ソ縺ォ蟇セ縺励※蜊倅ク縺ョ蛟、繧定ソ斐☆
繧ー繝ォ繝シ繝怜喧縺ォ縺、縺縺ヲ縺ッ縲SQL DMLシ夐寔險縲榊盾辣ァ
窶サ莉・荳九ョ繧ー繝ォ繝シ繝鈴未謨ー縺ァ繧ー繝ォ繝シ繝怜喧繧定。後≧陦ィ
ID | NAME | DATE |
---|---|---|
1 | GROUP-1 | 99-01-01 |
2 | GROUP-1 | 99-01-01 |
3 | GROUP-1 | 99-01-02 |
4 | GROUP-1 | 99-01-03 |
5 | GROUP-1 | 99-01-04 |
6 | GROUP-1 | 99-01-05 |
7 | GROUP-2 | 99-02-01 |
8 | GROUP-2 | 99-02-02 |
9 | GROUP-2 | 99-02-03 |
10 | GROUP-2 | 99-02-04 |
11 | GROUP-2 | 99-02-05 |
12 | GROUP-2 | 99-02-06 |
荳隕ァ
髢「謨ー | 蜿門セ | * | 謨ー蛟、 | 譁蟄 | 譌・莉 |
---|---|---|---|---|---|
COUNT | 繝繝シ繧ソ莉カ謨ー | 笳 | 笳 | 笳 | 笳 |
MIN | 譛蟆 | 笳 | 笳 | 笳 | |
MAX | 譛螟ァ | 笳 | 笳 | 笳 | |
AVG | 蟷ウ蝮 | 笳 | |||
SUM | 蜷郁ィ | 笳 | |||
STDDEV | 讓呎コ門¥蟾ョ | 笳 | |||
VARANCE | 豈埼寔蝗」讓呎コ門¥蟾ョ | 笳 |
COUNT
SELECT
縲縲COUNT(ID) AS COUNT,
縲縲TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);
COUNT縲YYYY/MM
シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ
6縲1999/02
6縲1999/01
SUM
SELECT
縲縲SUM(ID),
縲縲TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);
SUM縲YYYY/MM
シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ
57縲1999/02
21縲1999/01
MAX/MIN
SELECT
縲縲MAX(ID),
縲縲MIN(ID),
縲縲TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);
MAX縲 MIN縲YYYY/MM
シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ
12縲 7縲1999/02
66縲 1縲1999/01
AVG
蟷ウ蝮
SELECT
縲縲AVG(ID) AS AVG,
縲縲TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);
AVG縲YYYY/MM
シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ
ス槭1999/01
STDDEV
讓呎コ門¥蟾ョ
SELECT
縲縲STDDEV(ID) AS STDDEV,
縲縲TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);
STDDEV縲YYYY/MM
シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ搾シ
ス槭1999/01
蜈ィ蛻玲欠螳(*)
蜈ィ縺ヲ縺ョ蛻励〒菴ソ逕ィ蜿ッ閭ス
SELECT COUNT(*)
SELECT COUNT('X')
FROM 陦ィ蜷
WHERE ス;
NULL縺ョ髮險
繝繝シ繧ソ蝙 | NULL | ||
---|---|---|---|
MIN (蛻怜錐) | 謨ー蛟、縲∵枚蟄励∵律莉 | 辟。隕 | |
MAX (蛻怜錐) | 謨ー蛟、縲∵枚蟄励∵律莉 | 辟。隕 | |
COUNT (蛻怜錐) | 謨ー蛟、縲∵枚蟄励∵律莉 | 辟。隕 | COUNT(*) シ哢ULL縺碁寔險医&繧後k |
AVG (蛻怜錐) | 謨ー蛟、 | 辟。隕 | |
SUM (蛻怜錐) | 謨ー蛟、 | 辟。隕 |
ID蛻暦シ1,1,(NULL),2,2 縺ョ蝣エ蜷
COUNT(ALL ID) = 4
窶サ(NULL)縺ッ髮險医&繧後↑縺
COUNT(ID) シ 4
窶サALL縺ィ蜷後§
COUNT(DISTINCT ID) シ 2
COUNT(*) = 5
窶サ(NULL)繧る寔險医☆繧
NULL縺ョ蛻、螳/鄂ョ謠帙ッ縲SQL 螟画鋤髢「謨ーNULL蛟、縺ョ蛻、螳壹サ螟画鋤縲榊盾辣ァ
繧ー繝ォ繝シ繝鈴未謨ー縺悟茜逕ィ蜿ッ閭ス縺ェ蜿・
繝サSELECT蜿・
SELECT
縲COUNT(ス)
FROM ス
繝サHAVING蜿・
SELECT ス FROM ス GROUP BY ス
HAVING COUNT(ス) = ス
繝サORDER BY蜿・
SELECT ス FROM ス
ORDER BY COUNT(ス)