SAK }‘ŠÙ

SAK Streets ƒƒCƒ“ƒXƒgƒŠ[ƒg }‘ŠÙEƒJƒtƒF ˆê—— SAK Streets ŒŸõ
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | ƒtƒŠ[ƒ\ƒtƒg |

SQL –â‚¢‡‚킹 - rollupAcubeAgrouping setsAhavingAusingAcrossAnatural

‚±‚ê‚ç‚Ì‹@”\‚́AOracle ê—p‚Ì‹C‚ª‚·‚éB(Oracle8i ˆÈ~?)

¡ROLLUP

  E¬Œv’l‚ðo—Í‚·‚éB

select •i”Ô, count(*), sum(Žó’”), avg(Žó’”) from Žó’m
  group by rollup(•i”Ô);

•i”Ô                 COUNT(*) SUM(Žó’”) AVG(Žó’”)
------------------ ---------- ----------- -----------
a001                       25       10562      422.48
b001                        7       16797  2399.57143
c001                        3        6010  2003.33333
                           35       33369       953.4

select “¾ˆÓæCD, •i”Ô, count(*), sum(Žó’”), avg(Žó’”) from Žó’m
  group by rollup(“¾ˆÓæCD, •i”Ô);

“¾ˆÓ •i”Ô                 COUNT(*) SUM(Žó’”) AVG(Žó’”)
---- ------------------ ---------- ----------- -----------
1000 a001                       22        6452  293.272727
1000 b001                        1        2000        2000
1000 c001                        2        4510        2255
1000                            25       12962      518.48
2000 a001                        3        4110        1370
2000 b001                        6       14797  2466.16667
2000 c001                        1        1500        1500
2000                            10       20407      2040.7
                                35       33369       953.4

¡CUBE

  E‚ ‚ç‚ä‚é‘g‚ݍ‡‚킹‚̏¬Œv’l‚ðo—Í‚·‚éB(ƒNƒƒXWŒv’l)

select •i”Ô, count(*), sum(Žó’”), avg(Žó’”) from Žó’m
  group by cube(•i”Ô);

•i”Ô                 COUNT(*) SUM(Žó’”) AVG(Žó’”)
------------------ ---------- ----------- -----------
a001                       25       10562      422.48
b001                        7       16797  2399.57143
c001                        3        6010  2003.33333
                           35       33369       953.4

select “¾ˆÓæCD, •i”Ô, count(*), sum(Žó’”), avg(Žó’”) from Žó’m
  group by cube(“¾ˆÓæCD, •i”Ô);

“¾ˆÓ •i”Ô                 COUNT(*) SUM(Žó’”) AVG(Žó’”)
---- ------------------ ---------- ----------- -----------
1000 a001                       22        6452  293.272727
1000 b001                        1        2000        2000
1000 c001                        2        4510        2255
1000                            25       12962      518.48
2000 a001                        3        4110        1370
2000 b001                        6       14797  2466.16667
2000 c001                        1        1500        1500
2000                            10       20407      2040.7
     a001                       25       10562      422.48
     b001                        7       16797  2399.57143
     c001                        3        6010  2003.33333
                                35       33369       953.4

¡GROUPING SETS

  Egrouping set ‚ÅŽw’肵‚½€–Ú‚Ì’l‚¾‚¯‚ð‚à‚Á‚Ä‚­‚銴‚¶B

select •i”Ô, count(*), sum(Žó’”), avg(Žó’”) from Žó’m
  group by grouping sets(•i”Ô);

•i”Ô                 COUNT(*) SUM(Žó’”) AVG(Žó’”)
------------------ ---------- ----------- -----------
a001                       25       10562      422.48
b001                        7       16797  2399.57143
c001                        3        6010  2003.33333



select “¾ˆÓæCD, •i”Ô, count(*), sum(Žó’”), avg(Žó’”) from Žó’m
  group by grouping sets(“¾ˆÓæCD, •i”Ô);

“¾ˆÓ •i”Ô                 COUNT(*) SUM(Žó’”) AVG(Žó’”)
---- ------------------ ---------- ----------- -----------
1000                            25       12962      518.48
2000                            10       20407      2040.7
     a001                       25       10562      422.48
     b001                        7       16797  2399.57143
     c001                        3        6010  2003.33333

¡CONNECT BY

  EŠK‘w–â‚¢‡‚킹‚ÉŽg‚¦‚éƒe[ƒuƒ‹‚ª‚È‚©‚Á‚½B
    ŽŸ‚́A’P‚È‚é\•¶—á‚Å‚·B

select •i”Ô, Žó’” from Žó’m
  connect by •i”Ô = “¾ˆÓæCD;

select •i”Ô, Žó’” from Žó’m
  connect by prior •i”Ô = “¾ˆÓæCD and Žó’” >= 1000;

¡HAVING

  EWŒvŒ‹‰Ê‚ɑ΂·‚é–â‚¢‡‚킹ðŒ‚ðŽw’è‚·‚éB

select “¾ˆÓæCD, •i”Ô, count(*), sum(Žó’”), avg(Žó’”) from Žó’m
  group by “¾ˆÓæCD, •i”Ô
  having sum(Žó’”) > 1000;

“¾ˆÓ •i”Ô                 COUNT(*) SUM(Žó’”) AVG(Žó’”)
---- ------------------ ---------- ----------- -----------
1000 a001                       22        6452  293.272727
1000 b001                        1        2000        2000
1000 c001                        2        4510        2255
2000 a001                        3        4110        1370
2000 b001                        6       14797  2466.16667
2000 c001                        1        1500        1500

¡USING

  E€–Ú‚ÅŒ‹‡‚·‚éB

select * from Žó’m left join “¾ˆÓæm
  using (“¾ˆÓæCD);

¡CROSS JOIN

  EOracle ‚́u,v•W‹L‚Æ“¯‚¶B(ƒNƒƒXŒ‹‡AƒNƒƒXƒWƒ‡ƒCƒ“)

select * from Žó’m cross join “¾ˆÓæm
  where Žó’m.“¾ˆÓæCD = “¾ˆÓæm.“¾ˆÓæCD;

¡NATURAL JOIN

  E“¯‚¶–¼‘O‚Ì—ñ‚ðŽ©‘RŒ‹‡‚·‚éB(ƒiƒ`ƒ…ƒ‰ƒ‹ƒWƒ‡ƒCƒ“)

select * from Žó’m natural left join “¾ˆÓæm;

¡SQL Šî‘bŽÀ’n•ÒŽ‘—¿
¡SQL Šî‘b•ÒŽ‘—¿
¡SQL ƒ`ƒ…[ƒjƒ“ƒO•ÒŽ‘—¿
¡Oracle PL/SQL •ÒŽ‘—¿
¡PostgreSQL •ÒAJAVA ServletAJSP •ÒŽ‘—¿
¡MySQL •ÒŽ‘—¿
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | ƒtƒŠ[ƒ\ƒtƒg |

SAK Streets ƒƒCƒ“ƒXƒgƒŠ[ƒg }‘ŠÙEƒJƒtƒF ˆê—— SAK Streets ŒŸõ



Copyright (C) 1999-2017 Y.SAK