SQL DMLシ夐寔險(繧ー繝ォ繝シ繝怜喧)

繧ー繝ォ繝シ繝怜喧

讎りヲ

謖螳壼励↓縺翫¢繧九∝推陦後ョ蜷後§蛟、縺ァ繧ー繝ォ繝シ繝励r菴懈舌
譖エ縺ォ繧ー繝ォ繝シ繝鈴未謨ー繧堤畑縺繧倶コ九〒繧ー繝ォ繝シ繝玲ッ弱ョ髮險医r陦後≧莠九b縺ァ縺阪k縲
繧ー繝ォ繝シ繝鈴未謨ー縺ォ縺、縺縺ヲ縺ッ縲SQL繧ー繝ォ繝シ繝鈴未謨ー縲榊盾辣ァ

EMPNO ENAME DEPTNO SAL COMM HIREDATE CITYNAME
1 ‘C#’ 1 2000000 1000000 2000/1/1 ‘隘ソ螳ョ’
2 ‘C++’ 2 1000000 2000000 2000/1/1 ‘隘ソ螳ョ’
3 ‘VB.NET’ 3 1000000 1000000 2000/1/1 ‘隘ソ螳ョ’
4 ‘VB6’ 2 1000000 5000000 2000/1/1 ‘隘ソ螳ョ’
5 ‘JAVA’ 3 1500000 1000000 2000/1/1 ‘隘ソ螳ョ’
6 ‘JS’ 1 2000000 1000000 2000/1/1 ‘逾樊虻’
7 ‘PERL’ 3 1000000 100000 2000/1/1 ‘逾樊虻’

CITYNAME縺ョ繧ー繝ォ繝シ繝怜喧
SELECT CITYNAME FROM EMPLOYEE GROUP BY CITYNAME;
CITYNAME
シ搾シ搾シ搾シ搾シ
逾樊虻
隘ソ螳ョ

DEPTNO縺ョ繧ー繝ォ繝シ繝怜喧
SELECT DEPTNO FROM EMPLOYEE GROUP BY DEPTNO;
DEPTNO
シ搾シ搾シ搾シ搾シ
1
2
3

繧ー繝ォ繝シ繝鈴未謨ー

CITYNAME縺ョ繧ー繝ォ繝シ繝怜喧
SELECT TO_CHAR(AVG(SAL),'99,999,999') AS AVG, CITYNAME FROM EMPLOYEE GROUP BY CITYNAME;
AVG縲CITYNAME
シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
1,500,000縲逾樊虻
1,300,000縲隘ソ螳ョ

DEPTNO縺ョ繧ー繝ォ繝シ繝怜喧
SELECT TO_CHAR(AVG(SAL),'99,999,999') AS AVG, DEPTNO FROM EMPLOYEE GROUP BY DEPTNO;
AVG縲DEPTNO
シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
2,000,000縲1
1,000,000縲2
1,166,667縲3

繧ー繝ォ繝シ繝怜励ョ逵∫払

SELECT TO_CHAR(AVG(SAL),'99,999,999') AS AVG FROM EMPLOYEE;
AVG
シ搾シ搾シ搾シ搾シ
1,357,143
繧ー繝ォ繝シ繝怜喧縺吶k蛻励r謖螳壹@縺ェ縺蝣エ蜷医√げ繝ォ繝シ繝怜喧縺帙★縺ォ髮險医ョ縺ソ繧定。後≧縲
蜷陦後〒繧ー繝ォ繝シ繝怜喧縺励※縺繧九→繧りィ縺医k縲

繧ー繝ォ繝シ繝礼オ先棡縺ョ譚。莉カ莉倥″謚ス蜃コ

SELECT TO_CHAR(AVG(SAL),’99,999,999′) AS AVG, CITYNAME FROM EMPLOYEE GROUP BY CITYNAME HAVING AVG(SAL) >= 1500000;
AVG縲CITYNAME
シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
1,500,000縲逾樊虻

SELECT TO_CHAR(AVG(SAL),’99,999,999′) AS AVG, DEPTNO FROM EMPLOYEE GROUP BY DEPTNO HAVING AVG(SAL) >= 1500000;
AVG縲DEPTNO
シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
2,000,000縲1

隍謨ー蛻励ョ繧ー繝ォ繝シ繝怜喧

SELECT CITYNAME, DEPTNO FROM EMPLOYEE
ORDER BY CITYNAME, DEPTNO;
CITYNAME縲DEPTNO
シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
逾樊虻縲1
逾樊虻縲3
隘ソ螳ョ縲1
隘ソ螳ョ縲2
隘ソ螳ョ縲2
隘ソ螳ョ縲3
隘ソ螳ョ縲3

SELECT CITYNAME, DEPTNO FROM EMPLOYEE
GROUP BY CITYNAME, DEPTNO
ORDER BY CITYNAME, DEPTNO;
CITYNAME縲DEPTNO
シ搾シ搾シ搾シ搾シ シ搾シ搾シ搾シ
逾樊虻縲1
逾樊虻縲3
隘ソ螳ョ縲1
隘ソ螳ョ縲2
隘ソ螳ョ縲3
CITYNAME縲DEPTNO
シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
逾樊虻縲1縲竊偵逾樊虻縲1縲
シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
逾樊虻縲3縲竊偵逾樊虻縲3
シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
隘ソ螳ョ縲1縲竊偵隘ソ螳ョ縲1
シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
隘ソ螳ョ縲2縲竊偵隘ソ螳ョ縲2
隘ソ螳ョ縲2
シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
隘ソ螳ョ縲3縲竊偵隘ソ螳ョ縲3
隘ソ螳ョ縲3
CITYNAME縺ァ繧ー繝ォ繝シ繝怜縺代@(逾樊虻/隘ソ螳ョ)縲
縺薙ョ繧ー繝ォ繝シ繝励r譖エ縺ォDEPTNO縺ァ繧ー繝ォ繝シ繝怜縺代☆繧(1/2/3)縲

繧ー繝ォ繝シ繝怜喧シ髮險
SELECT COUNT(*) AS CNT, CITYNAME, DEPTNO FROM EMPLOYEE
GROUP BY CITYNAME, DEPTNO
ORDER BY CITYNAME, DEPTNO;
CNT縲CITYNAME縲DEPTNO
シ搾シ阪シ搾シ搾シ搾シ搾シ シ搾シ搾シ搾シ
1縲逾樊虻縲1
1縲逾樊虻縲3
1縲隘ソ螳ョ縲1
2縲隘ソ螳ョ縲2
2縲隘ソ螳ョ縲3
CNT縲CITYNAME縲DEPTNO
シ搾シ阪シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
1縲逾樊虻縲1縲竊偵1縲逾樊虻縲1縲
シ搾シ阪シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
1縲逾樊虻縲3縲竊偵1縲逾樊虻縲3
シ搾シ阪シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
1縲隘ソ螳ョ縲1縲竊偵1縲隘ソ螳ョ縲1
シ搾シ阪シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
1縲隘ソ螳ョ縲2縲竊偵2縲隘ソ螳ョ縲2
1縲隘ソ螳ョ縲2
シ搾シ阪シ搾シ搾シ搾シ搾シ阪シ搾シ搾シ搾シ搾シ
1縲隘ソ螳ョ縲3縲竊偵2縲隘ソ螳ョ縲3
1縲隘ソ螳ョ縲3

繧ー繝ォ繝シ繝怜喧SQL縺ョ譖ク蠑(鬆逡ェ)

笳具シ售ELECT ス FROM ス GROUP BY ス HAVING ス ORDER BY ス
笳具シ售ELECT ス FROM ス HAVING ス GROUP BY ス ORDER BY ス
GROUP BY 縺ィHAVING 縺ッ縺ゥ縺。繧峨′蜈医〒繧り憶縺
テ暦シ售ELECT ス FROM ス ORDER BY ス GROUP BY ス HAVING ス
テ暦シ售ELECT ス FROM ス GROUP BY ス ORDER BY ス HAVING ス
ORDER BY 縺ッ譛蠕

蛻怜腰菴阪ョGROUP蛹

COUNT() OVER()

窶サ邁。譏敵ROUP BY

SELECT
縲縲COUNT( A ) OVER( PARTITION BY A,B,C ) AS SEQ
縲縲A,
縲縲B
FROM
縲縲TESTTABLE

RANK() OVER()

窶サ鬆菴堺サ倥¢
–繝ゥ繝ウ繧ュ繝ウ繧ーシ域球蠖楢蛻・縺ォ螢イ荳企。阪ョ鬮倥>鬆縺ォ繧ス繝シ繝医@縺ヲ鬆菴阪r莉倥¢繧具シ

SELECT
縲縲RANK() OVER(ORDER BY ス DESC, ORDER BY ス ASC) AS '繝ゥ繝ウ繧ッ',
縲縲ス
FROM
縲縲ス
ORDRER BY
縲縲'繝ゥ繝ウ繧ッ'
縲縲
SELECT
縲縲RANK() OVER( ORDER BY SUM(Uriagegaku) DESC ) AS '繝ゥ繝ウ繧ッ',
縲縲TantosyaCD AS '諡蠖楢',
縲縲SUM(Uriagegaku) AS '螢イ荳企。'
FROM
縲縲TESTTABLE
GROUP BY
縲縲TantosyaCD

ROW_NUMBER() OVER()

窶サ騾」逡ェ繧呈険繧

SELECT
縲縲ROW_NUMBER() OVER( PARTITION BY A,B,C ORDER BY A,B,C ) AS SEQ
縲縲A,
縲縲B
FROM
縲縲TESTTABLE

Follow me!

蜑阪ョ險倅コ

SQL DMLシ夂オ仙粋(JOIN)

谺。縺ョ險倅コ

SQL DMLシ壼憶蝠上>蜷医o縺