Upgrade to Pro — share decks privately, control downloads, hide ads and more …

SQL Meisterへの道 ~基礎〜参照編~

C6a8cb5e13aa716521d522471ec4e4cd?s=47 ma2k8
November 02, 2020

SQL Meisterへの道 ~基礎〜参照編~

Biz職にもエンジニアリング教えていき!の流れができたので、社内勉強会用に書いた。

C6a8cb5e13aa716521d522471ec4e4cd?s=128

ma2k8

November 02, 2020
Tweet

Transcript

  1. SQL Meister ΁ͷಓ @ma2k8 2020-10-31 ~جૅʙࢀরฤ~

  2. ໨࣍ - SQL͸͍͍ͧ - ༻ޠղઆͱجຊͷཱͪճΓ - SQL(ΫΤϦ)ͬͯͳΜͧ - ςʔϒϧͬͯͳΜͧ -

    σʔλϕʔεͬͯͳΜͧ - ڞ௨Ͱ࢖͑ΔWHERE۟Λ֮͑Α͏ - SELECTจͰσʔλΛݟͯΈΑ͏ - SELECTจͷจ๏ - ࣮ߦ - ΫΤϦ࣮श(redashͷ࢖͍ํ) - ΫΤϦ࣮श(SELECT&WHERE) - ू߹ͷײ֮Λ௫Ή - JOIN͕Ͱ͖Ε͹͍ͬͪΐલ - ΫΤϦ࣮श(JOIN) - ूܭΫΤϦ ~ूܭؔ਺~ - ूܭΫΤϦ ~group by~ - ΫΤϦ࣮श(ूܭΫΤϦ) - αϒΫΤϦ - ΫΤϦ࣮श(αϒΫΤϦ)
  3. SQL͸͍͍ͧ

  4. SQL͸ίεύͷ͍͍εΩϧ ࠷ۙɺςΫχΧϧPMͱ͍͏৬छ͕ྲྀߦ͍ͬͯͨΓɺϏδωεͱΤϯδχΞϦϯά྆ ํ෼͔Δਓ͕ॏๅ͞Ε͍ͯ·͢ɻ ൴Β͸ɺΤϯδχΞϦϯά͕͍͢͝ͷͰ͸ͳ͘ɺσʔλߏ଄Λ೺ѲͰ͖͍ͯͯɺ޻ ਺ݟੵ΍ػೳ։ൃͷ೉қ౓͕͋Δఔ౓ਪଌͰ͖Δ͔Βॏๅ͞Ε͍ͯ·͢ɻ SQL͕ॻ͚Δͱ͍͏͜ͱ͸ɺσʔλߏ଄Λ೺Ѳ͢ΔखஈΛಘΔͱ͍͏͜ͱͰ͜ͷྗ ͸͔ͳΓڧ͍ͷͰ΍͍͖ͬͯ·͠ΐ͏ʂ

  5. ༻ޠղઆͱجຊͷཱͪճΓ

  6. ΫΤϦ(SQL)ͬͯͳΜͧ ΫΤϦͱ͍͏ͷ͸ɺσʔλʹରͯ͠ͳΜΒ ͔ͷૢ࡞Λߦ͏ͨΊͷݴޠͰ͢ɻ ཈͓͑ͯ͘΂͖จ๏ͷछྨ͸ͨͬͨͷ4ͭɻ SELECTจֻUPDATEจֻINSERTจֻDELETEจ ͚ͩͰ͢ɻ ͔͠΋ڞ௨Ͱ࢖͑Δ෦෼͕ͨ͘͞Μ͋Δͷ Ͱɺ1֮ͭ͑ͨΒ͙͢शಘͰ͖Δ͔΋ʂ

  7. ςʔϒϧͬͯͳΜͧ ςʔϒϧͱ͍͏ͷ͸ɺσʔλ͕ೖͬͯΔ ശͷ͜ͱͰ͢ɻ ςʔϒϧʹ͸ߏ଄(εΩʔϚ)͕͋ͬͯɺ ཁ͸ܗ͕ܾ·͍ͬͯ·͢ɻ ΫΤϦΛ࡞Δͱ͖͸͜ͷߏ଄Λҙࣝ͢Δ ඞཁ͕͋ΔͷͰɺͲ͏͍͏ςʔϒϧ͕ଘ ࡏ͢Δ͔ͱɺߏ଄Λ֬ೝ͢ΔίϚϯυΛ ͓͖֮͑ͯ·͠ΐ͏ɻ ٙڋ٩پ؋屢؋ׯענעطٴڋً

  8. ςʔϒϧͬͯͳΜͧ `show tables` ͱ͍͏ίϚϯυͰɺ ςʔϒϧͷҰཡΛ֬ೝͰ͖·͢ɻ ࣗ෼͸ࡉ͔͍ςʔϒϧ໊ͱ͔֮͑ͯͳ ͍ͷͰΊͬͪΌΑ͘ୟ͖·͢ɻ ~ςʔϒϧҰཡΛ֬ೝ͢Δͧ~ 熳נ䠜橃猳㋷׫؆נؤ猳

  9. ςʔϒϧͬͯͳΜͧ Bdesc ςʔϒϧ໊Bͱ͍͏ίϚϯυͰɺ ςʔϒϧͷߏ଄Λ֬ೝͰ͖·͢ɻ ΫΤϦΛॻ͘ͱ͖͸ߏ଄Λߏ੒͢Δɺ Filed(ΧϥϜ໊)Λҙࣝ͢Δඞཁ͕͋Γ ·͢ɻ ~ςʔϒϧͷߏ଄Λ֬ೝ͢Δͧ~ قػٽ㧙׫؄ש؋(KGNF׿؂؄ׯ؇㧙נ؂מا⓸⛮׼׭㖝ױפب،1- 屢؋ػقَپ؊⓸⛮خ䮛嵮׳؂ا㐀״؃׵؉

  10. σʔλϕʔε(DB)ͬͯͳΜͧ σʔλϕʔε(DB)ͱ͍͏ͷ͸ɺςʔϒϧΛ· ͱΊͨ΋ͷͰ͢ɻ DBαʔόʔͷ͜ͱΛ෺ཧDBɺ͜ͷςʔϒϧ ͷ·ͱ·ΓΛ࿦ཧDBͱݺΜͰ۠ผͨ͠Γ͠ ·͢ɻ `show databases` ͰDBͷҰཡΛ֬ೝͰ͖ ·͢ɻ

    KPHQOCVKQPAUEJGOG؋▾㔬؇∽إبا מدؚئ㻸؇׳؆נ؃ננؠ؀؃ֻ CNRAUWDUETKRVKQPAUCPFDQZר㓲־؊⹻劊׳׻ٙڋ٩پר ⑆׿؂ا&$؃׵
  11. σʔλϕʔε(DB)ͬͯͳΜͧ ΫΤϦΛ࣮ߦ͢Δࡍʹ͸ɺͲͷDBʹର͠ ࣮ͯߦ͢Δ͔બ୒͢Δඞཁ͕͋Γ·͢ɻ `use σʔλϕʔε໊` ͱ͢Δͱબ୒͢Δ ͜ͱ͕Ͱ͖·͢ɻ ෺ཧDBʹϩάΠϯͨ͠Β·ͣ࠷ॳʹɺ࿦ ཧDBΛબ୒͠·͠ΐ͏ɻ CNRAUWDUETKRVKQPAUCPFDQZخ≠ע׺猳猳

  12. ͜ͷล͸redashͱ͔࢖͑͹ ͋Δఔ౓ҙࣝ͠ͳ͍ͰࡁΈ·͕͢ɺ ͓͍֮͑ͯͯଛ͸ͳ͍Ͱ͢ʂ (࣮शͰ͸redash࢖͍·͢)

  13. ڞ௨Ͱ࢖͑Δ WHERE۟Λ֮͑Α͏

  14. WHERE۟ͬͯͳΜͧ WHERE۟͸ɺର৅ͷσʔλΛߜΔͨΊͷ৚ ݅Ͱ͢ɻ AND,ORͰ৚݅ΛͲΜͲΜ଍͍͖ͯ͠ɺࣗ ෼ͷ໨తͱ͢ΔσʔλΛಛఆ͢ΔͨΊʹ࢖ ͍·͢ɻ ͦͯ͠ɺWHERE۟͸ɺSELECTจֻUPDATE จֻDELETEจͰ࢖͑Δڞ௨ͷ஌ࣝͰ͢ʢί εύྑ͍Ͷʂʣ 屢؋ػقَپ؊٨ضپْ؛׻נ؆طٴڋً؃׵؉ʙ

    ؃؞ֻػقَپ؊٨ضپْؤئ؞׿؄㪵懀؃⊠┊猳
  15. WHERE۟ͬͯͳΜͧ WHERE۟Ͱ͸ɺ ׬શҰகɺ෦෼ҰகɺϦετҰக(Ϧετ಺ͷͲΕ͔ʹ׬શҰக)ͷ৚݅ ͕࢖͑·͢ɻ ׬શҰக͸ `${ΧϥϜ໊} = “৚݅"` ෦෼Ұக͸`${ΧϥϜ໊} LIKE

    “%৚݅%”` ϦετҰக͸ `${ΧϥϜ໊}` IN (“৚݅1”,“৚݅2”,“৚݅3”) ͱ͍͏Α͏ʹࢦఆͰ͖·͢ɻ ͜ΕΒͰSELECTͳΒࢀর͢Δର৅ɺINSERT,UPDATE,DELETEͳΒߋ৽͢Δ ର৅ΛߜΓ·͢ɻ 㩂⇗خؚ؄؝؆נ؄倱㩽ר⯪ثا揉ⓧ؋ ؃ؚ؄؝ؚ׵ 㙼׭乸╓ئ乸؄⛭״١ٽ؃׵猳 ρ׳ب׿؄.+/+6خ≠׿؂ؚ׵רֻׯب؋⇗㡑خ┗椱׵اؠ؀؃׵猳 ٚڋْ撰⯻׵ת؂ע׿؄צ׳נ㤣؋⇗㡑خ倿ئؚ׳أע
  16. WHERE۟ͬͯͳΜͧ ઌఔͷ׬શҰகɺ෦෼ҰகɺϦετҰக(Ϧετ಺ͷͲΕ͔ʹ׬શҰக)ͷ৚݅͸ɺ൱ఆ ͷ৚݅ʹ΋࢖͑·͢ɻ ׬શҰகͷ൱ఆ͸ `${ΧϥϜ໊} != “৚݅”` ෦෼Ұகͷ൱ఆ͸`${ΧϥϜ໊} NOT LIKE

    “%৚݅%”` ϦετҰகͷ൱ఆ͸ `${ΧϥϜ໊}` NOT IN (“৚݅1”,“৚݅2”,“৚݅3”) ͱ͍͏Α͏ʹࢦఆͰ͖·͢ɻ
  17. SELECTจͰ σʔλΛݟͯΈΑ͏

  18. SELECTจͷจ๏ SELECT * -> ࢀর͢ΔΧϥϜ໊Λࢦఆɻجຊ͸શ෦Λҙຯ͢Δ`*`ͰOK FROM ${ςʔϒϧ໊} -> ࢀর͢Δςʔϒϧ໊Λࢦఆ WHERE

    ${৚݅} -> ৚݅Λࢦఆ
  19. ࣮ߦ &$خ䮛嵮׳؂捙㕿 ٙڋ٩پ⃡岈خ䮛嵮 ٙڋ٩پ㲬持؄ٚڋْ؊⑦⺚خ䮛嵮 قػٽخ倥؛䶬؂؂⺀嬭猳

  20. ஫ҙ ΊͪΌͪ͘Ό݅਺͕ଟ͔ͬͨΓɺΧϥϜʹͰ͔͍σʔλͷೖͬͨςʔϒϧʹΏΔ͍৚݅ͷΫΤϦΛ࣮ߦ͠ ͯ͠·͏ͱɺ݁ՌΛฦ͢ͷʹ͕͔͔࣌ؒͬͨΓෛՙΛ͔͚ͯ͠·͍·͢ɻ ͦΜͳͱ͖͸ɺͱΓ͋͑ͣ݅਺ΛߜΔ limitͩͬͨΓɺऔಘ͢ΔΧϥϜΛ໌ࣔ͢ΔΑ͏ʹ͠·͠ΐ͏ɻ SELECT id,name -> ࢀর͢ΔΧϥϜ໊Λࢦఆ FROM

    ${ςʔϒϧ໊} -> ࢀর͢Δςʔϒϧ໊Λࢦఆ WHERE ${৚݅} -> ৚݅Λࢦఆ LIMIT 10 -> 10͚݅ͩऔಘɻWHEREͳ͠ͰɺFROMͷޙΖʹ௚઀ࢦఆ͢Δ͜ͱ΋Ͱ͖Δ
  21. ΫΤϦ࣮श(redashͷ࢖͍ํ) ЀJVVRUTGFCUJUECNGDCUGECOREQO؇صقٌَ׳؂׫׼ױנּ Ё%TGCVG 3WGT[خ捙㕿 Ђ&CVCDCUGخ捙㕿ּ↫⦿؋FGXGNQROGPV؃猳 Ѓמ؄؋قػٽخ㧙נ؂=⾣'ZGEWVG?؃⺀嬭׵ا؊؛猳 桴挶׿׻قػٽ㧙נ؂؞Ⰸ⃩Ⰼ؆؊؃㧙נ؂؛ؚ׳أ猳

  22. ΫΤϦ࣮शᶃ(SELECT & WHERE) Addressςʔϒϧ͔ΒɺҎԼͷ৚݅ͰσʔλΛऔಘ͠·͠ΐ͏ɻ - prefecture͕ “TOKYO”(=) ͔ͭ(and) - address_line1ʹ”3ஸ໨”ؚ͕·ΕΔ(like)

    ͔ͭ(and) - ( city͕”ौ୩۠”(=) ΋͘͠͸(or) தԝ۠(=) )
  23. ΫΤϦ࣮शᶄ(SELECT & WHERE) Addressςʔϒϧ͔ΒɺҎԼͷ৚݅ͰσʔλΛऔಘ͠·͠ΐ͏ɻ - prefecture͕ “TOKYOͰ͸ͳ͍”(!=) ͔ͭ(and) - city͕

    [”೔໺ࢢ”,”ࡳຈࢢ”]ͷ͍ͣΕ͔(in)
  24. ू߹ͷײ֮Λ௫Ή

  25. ू߹ͬͯͳΜͧ ू߹ͱ͍͏ͷ͸Ϟϊͷू·ΓͰ͢ɻ ςʔϒϧ͸ʮςʔϒϧఆٛʹैͬͨσʔλͷू߹ʯͱଊ͑Δ͜ͱ͕Ͱ͖ ·͢ɻ

  26. WHERE۟ͱON۟͸෦෼ू߹ΛऔΓग़͢࢓૊Έ 9*'4'⛆ؠ10⛆؃⑺⎰خ∽اּ ׯ؊⑺⎰؋⚣䑈␤؊揉ⓧ榧⛩؇؆اּ ؞׽ةدֻ䵛榧⛩؇؆اׯ؄؞מاּ ׯ؊䫃♑ר5'.'%6׼؄׵ا؄ֻ倱㩽#؋ ٙڋ٩پ$؊揉ⓧ榧⛩؄؆ئؚ׵ּ ׯע勤פا؄ֻ9*'4'⛆خ㗨⹻׳؆נ؄ ⑉ٚڋْר㧕㢑ױباطٴڋً؞؀׫ק؞獑 ٙڋ٩پ ρ恦⚔⺧؇峡ע؄ֻؾټٳ؋5'.'%6亡؃哋ヒ⹻劊

    ؃שا؊؃揉ⓧ榧⛩؃؆נ٤ْڋچ؞מئؚ׵רֻ 9*'4'؋ײ׿׫ئׯענעطٴڋً؄נעׯ؄؃ ※ON۟͸JOINʹ࢖͏ߏจͰ͜ͷޙग़͖ͯ·͢ɻWHEREͱಉ͡৚݅ࢦఆ͕Ͱ͖Δ΍ͭͰɺ JOINͷ৚݅ΛࢦఆͰ͖·͢ɻ
  27. JOIN΍αϒΫΤϦɺूܭΫΤϦͰ ͜ͷߟ͑ํ͕ॏཁʹͳΔ

  28. JOIN͕Ͱ͖Ε͹͍ͬͪΐલ

  29. JOINͬͯͳΜͧ JOIN͸ෳ਺ͷςʔϒϧΛ߹ମͤ͞ΔٕͰ͢ɻ ʮςʔϒϧఆٛʹैͬͨσʔλͷू߹ʯͳͷͰɺςʔϒϧಉ࢜ͷఆٛΛ ૊Έ߹Θͤͯू߹Λ͕ͬͪΌΜ͜Ͱ͖ΔͷͰ͢ɻ ΄ͱΜͲͷέʔεͰɺσʔλΛࢀর͢Δ࣌(SELECT)ʹ࢖͍·͢ɻ

  30. JOINͷछྨ

  31. None
  32. None
  33. JOINͷछྨ ͨ͘͞Μछྨ͸͋Γ·͕͢ɺ࣮ࡍ࢖͏99%͸LEFT JOINͱINNER JOINͳͷ Ͱ͜ͷ2ͭΛ͓͚֮͑ͯ͹OKɻ

  34. INNER JOINͰ σʔλΛݟͯΈΑ͏

  35. INNER JOINจͷจ๏ SELECT * -> ࢀর͢ΔΧϥϜ໊Λࢦఆɻجຊ͸શ෦Λҙຯ͢Δ`*`ͰOK FROM ${ςʔϒϧ໊A} -> ࢀর͢Δςʔϒϧ໊Λࢦఆ

    INNER JOIN ${ςʔϒϧ໊B} -> JOIN͍ͨ͠ςʔϒϧ໊Λࢦఆ ON ${৚݅} -> WHERE۟ͱಉ͡ॻ͖ํͰ৚݅Λࢦఆ͢Δɻ͜͜ͷ৚݅͸ݫ͍͠΄͏͕ JOIN͢Δର৅͕গͳ͘ͳΔͷͰύϑΥʔϚϯε΋ྑ͘ͳΔɻ WHERE ${৚݅} -> ৚݅Λࢦఆ
  36. INNER JOINͰԿ͕ى͖Δ͔ ͲΜͳಈ͖Λ͢Δͷ͔ɻdogsςʔϒϧʹownerςʔϒϧΛɺ dogs.owner_idͱowner.idͰINNER JOIN͢ΔͱҎԼͷ༷ʹͳΓ·͢ɻ

  37. INNER JOINͰԿ͕ى͖Δ͔ dogs.owner_id = owner.id ͷ৚݅Λຬͨ͞ͳ͍ߦ͕ল͔Εͨ݁Ռ͕खʹೖΔ͜ ͱ͕Θ͔Γ·͢ɻdogs.owner_idʹಉ͡ID͕͋ΔͷͰɺ݁Ռʹ΋ಉ͡ownerςʔ ϒϧͷߦ͕ෳ਺ೖΔ͜ͱʹ΋஫ҙ

  38. INNER JOINͰԿ͕ى͖Δ͔ ͜ΕΛϕϯਤͰද͢ͱ JOINݩɺJOINઌɺ྆ํʹҰகͨ͠෦෼͚ͩऔΓग़͢ͷ͕INNER JOINͰ ͢ɻ

  39. LEFT JOINͰ σʔλΛݟͯΈΑ͏

  40. LEFT JOINจͷจ๏ SELECT * -> ࢀর͢ΔΧϥϜ໊Λࢦఆɻجຊ͸શ෦Λҙຯ͢Δ`*`ͰOK FROM ${ςʔϒϧ໊A} -> ࢀর͢Δςʔϒϧ໊Λࢦఆ

    LEFT JOIN ${ςʔϒϧ໊B} -> JOIN͍ͨ͠ςʔϒϧ໊Λࢦఆ ON ${৚݅} -> WHERE۟ͱಉ͡ॻ͖ํͰ৚݅Λࢦఆ͜͜ͷ৚݅͸ݫ͍͠΄͏͕JOIN͢ Δର৅͕গͳ͘ͳΔͷͰύϑΥʔϚϯε΋ྑ͘ͳΔɻ WHERE ${৚݅} -> ৚݅Λࢦఆ +00'4,1+0؄ؘؗ⃡傳׼׸猳
  41. LEFT JOINͰԿ͕ى͖Δ͔ ͲΜͳಈ͖Λ͢Δͷ͔ɻdogsςʔϒϧʹownerςʔϒϧΛɺ dogs.owner_idͱowner.idͰLEFT JOIN͢ΔͱҎԼͷ༷ʹͳΓ·͢ɻ

  42. LEFT JOINͰԿ͕ى͖Δ͔ LEFT(ࠨ)ͱ͍͏ͷ͸ɺJOINݩςʔϒϧͷ͜ͱͰ͢ɻ(RIGHT JOIN΋͋Δ) JOINݩΛ༏ઌͯ͠JOIN͠Ζͱ͍͏͜ͱͰɺdogs.owner_id = owner.id ͷ৚݅Λຬͨ͞ͳͯ͘΋ɺJOINݩ ͷߦ͕ল͔Εͣʹ݁Ռ͕खʹೖΔ͜ͱ͕Θ͔Γ·͢ɻ

  43. LEFT JOINͰԿ͕ى͖Δ͔ ͜ΕΛϕϯਤͰද͢ͱ JOINݩ͸༏ઌ͠ɺJOINઌ͕Ұகͨ͠ΒऔΓग़͠ɺJOINઌʹҰக͢Δߦ ͕ͳ͚Ε͹NULLͰຒΊͯऔΓग़͢ͷ͕LEFT JOINͰ͢ɻ

  44. ΫΤϦ࣮श(JOIN) providerςʔϒϧͱcloud_sign_settingςʔϒϧΛJOINͯ͠Έ·͠ΐ͏ɻ - දࣔ͢ΔΧϥϜ͸ - providerςʔϒϧ͔Βcompany_name - cloud_sign_settingςʔϒϧ͔Βauto_create_contract - LEFTͱINNERɺ྆ํͷ࣮ߦ݁ՌΛ֬ೝͯ͠Έ·͠ΐ͏

  45. ूܭΫΤϦ ~ूܭؔ਺ฤ~

  46. SELECT΍WHERE߲໨ʹ͸ΧϥϜ͚ͩͰ͸ͳ͘ɺܭࢉΛͯ͘͠ΕΔؔ਺Λࢦఆ͢Δ͜ͱ͕Ͱ͖·͢ɻ Α͘࢖͏ͷ͸5ͭ => SUM,AVG,MAX,MIN,COUNT ͜ΕΒ͸ޙड़ͷGROUP BYͱ૊Έ߹Θͤͯ࢖͏͜ͱͰޮྗΛൃش͠·͢ɻ ※ͷΑ͏ʹ୯ମͰ΋࢖͑·͢͸͠·͢ɻ ूܭؔ਺ͬͯʁ // customerςʔϒϧͷ૯݅਺Λग़͢

    > SELECT COUNT(*) FROM customer; // ͋Δproviderͷcustomerͷ૯਺ΛͱΔ > SELECT COUNT(*) FROM customer WHERE provider_id = "aaa";
  47. ूܭΫΤϦ ~GROUP BYฤ~

  48. GROUP BYͬͯʁ GROUP BY͸ɺࢦఆͨ͠ΧϥϜͰ݁ՌΛ·ͱΊͯ͘Ε·͢ɻ ઌఔͷͷूܭΫΤϦ͸1ͭͷϓϩόΠμͷquantity߹ܭΛूܭ͍ͯ͠·͢ɻ GROUP BY͸ϓϩόΠμຖͷquantityͷ߹ܭΛશͯݟ͍ͨ৔߹ʹ࢖͑·͢ɻ // ͋Δproviderͷcustomerͷ૯਺ΛͱΔ >

    SELECT COUNT(*) FROM customer WHERE provider_id = "aaa"; // ܖ໿ຖͷbilling_itemͷquantity૯਺ΛͱΔ > SELECT provider_id, COUNT(*) FROM customer GROUP BY provider_id;
  49. GROUP BYͬͯʁ GROUP BYͰ͸ɺෳ਺ͷΧϥϜΛࢦఆ͢Δ͜ͱ΋Ͱ͖·͢ɻ // contract,contract_charge_item_idຖͷbilling_itemͷquantity૯਺ΛͱΔ > SELECT contract_id, contract_charge_item_id,

    sum(quantity) FROM billing_item GROUP BY contract_id,contract_charge_item_id;
  50. Πϝʔδਤ GROUP BYͰ͸ɺෳ਺ͷΧϥϜΛࢦఆ͢Δ͜ͱ΋Ͱ͖·͢ɻ // contract,contract_charge_item_idຖͷbilling_itemͷquantity૯਺ΛͱΔ > SELECT contract_id, contract_charge_item_id, sum(quantity)

    FROM billing_item GROUP BY contract_id,contract_charge_item_id;
  51. ෳ਺ࢦఆ͢Δͱ͖͸σʔλͷߏ଄Λҙࣝ͢Δ ෳ਺ࢦఆ͢Δࡍ͸σʔλߏ଄తʹҙຯͷ͋Δ΋ͷͰ͋Δ͔Λҙࣝ͢Δඞཁ ͕͋Γ·͢ɻ 1ϖʔδલͷΫΤϦ݁Ռͷߏ଄Λ1ܖ໿෼நग़͢ΔͱͷΑ͏ʹͳΓ·͢ɻ ITQWRD[EQPVTCEVAKF؄׵ا؄ֻSWCPVKV[؋؇؆ا ITQWRD[EQPVTCEVAKFEQPVTCEVAEJCTIGAKVGO؄׵ا؄ֻ SWCPVKV[؋؄EQPVTCEVAEJCTIGAKVGO؃؞ITQWR◷ױبا

  52. ΫΤϦ࣮श(ूܭΫΤϦ) billingςʔϒϧ͔ΒɺϓϩόΠμ͝ͱͷtotal_billing_priceͷ߹ܭ஋Λग़ͯ͠Έ·͠ΐ͏ ৄࡉͳ৚݅͸ҎԼ - total_billing_unit͕”YEN” - provider_idͰgroupԽ - total_billing_priceͷ߹ܭ஋(SUM)Λूܭ -

    දࣔ͢Δ߲໨͸provider_id, total_billing_priceͷ߹ܭ஋
  53. αϒΫΤϦ

  54. αϒΫΤϦ ςʔϒϧ͸ʮςʔϒϧఆٛʹैͬͨσʔλͷू߹ʯɺΫΤϦͷ݁Ռ͸ʮ෦෼ू߹ʯͱͯ͠ଊ͑ΒΕΔલड़͠·ͨ͠ɻ αϒΫΤϦ͸ɺʮ෦෼ू߹ʯΛΫΤϦʹ૊ΈࠐΉ͜ͱͰ͢ɻ SELECTจʹ͓͚ΔཁૉΛͬ͘͟Γઆ໌͢ΔͱͷΑ͏ʹͳΓ·͢ɻ ${ू߹} ͱॻ͍ͯ͋Δ෦෼͸ී௨ʹॻ͘ͱςʔϒϧ͕ೖΓ·͕͢ɺΫΤϦͰ࡞ͬͨʮ෦෼ू߹ʯΛ౰ͯ͸ΊΔ͜ͱ ΋Ͱ͖·͢ɻ ͜Ε͕αϒΫΤϦͰ͢ɻ SELECT ${ू߹ͷཁૉ}

    FROM ${ू߹} XXX JOIN ${ू߹} ON ${ू߹ͷཁૉʹΑΔ৚݅} WHERE ${ू߹ͷཁૉʹΑΔ৚݅}
  55. Ͳ͏ॻ͘ͷʁ ${ू߹} ͷ෦෼Λ ()ͰғͬͨSELECTจʹஔ͖׵͑·͢ɻ ͜Μͳײ͡Ͱॻ͖·͢ɻ(΍΍͍͜͢͠Ͷʘ(^o^)ʗ) SELECT ${ू߹ͷཁૉ} FROM ( SELECT

    * FROM ${ू߹} WHERE aa=“xx” ) XXX JOIN ( SELECT * FROM ${ू߹} WHERE aa=“xx” ) ON ${ू߹ͷཁૉʹΑΔ৚݅} WHERE ${ू߹ͷཁૉʹΑΔ৚݅}
  56. ΩϨΠʹॻ͚Δwith ※MySQLͰ͸ɺ5.8͔ΒͷػೳͳͷͰ·ͩ࢖͑ͳ͍ͷͰ͕͢ɺαϒΫΤϦͷཧղʹ໾ͨͪͦ͏ͳͷͰҰԠࡌͤ·͢ɻ αϒΫΤϦ͸ɺ෦෼ू߹ʹ໊લΛ͚ͭͯςʔϒϧͷ༻ʹѻ͏ख๏Ͱ͢ɻ ͜ΕΛΘ͔Γ΍͘͢ॻ͚Δwith۟ͱ͍͏΋ͷ͕͋Γ·͢ɻ ཁ͸ઌʹ෦෼ू߹Λ࡞໊ͬͯલΛ͚ͭɺͦͷ໊લΛΫΤϦ಺Ͱར༻͍ͯ͠Δ͚ͩͰ͕͢ɺೖΓ૊Μͩߏ଄͕ղফͰ͖ΔͷͰΧφϦ ಡΈ΍͘͢ͳΓ·͢ɻ WITH tableA as (

    SELECT * FROM ${ू߹} WHERE aa=“xx” ), tableB as ( SELECT * FROM ${ू߹} WHERE aa=“xx” ) SELECT ${ू߹ͷཁૉ} FROM tableA XXX JOIN tableB ON ${ू߹ͷཁૉʹΑΔ৚݅} WHERE ${ू߹ͷཁૉʹΑΔ৚݅}
  57. ͳΜʹ࢖͏ͷʁ ςʔϒϧ͚ͩΛࡐྉʹΫΤϦΛ૊ΈཱͯΔͱɺͲ͏ͯ͠΋ूܭͷ୯Ґ͕߹Θͳ͘ͳ͍͖ͬͯ·͢ɻ ྫ͑͹ɺcontractͷςʔϒϧ͔Βʮࠓ೥։࢝ͨ͠ܖ໿ʯͱɺʮࠓ݄։࢝ͨ͠ܖ໿ʯΛऔಘ͍ͨ͠ͱ͠·͢ɻ αϒΫΤϦͳ͠Ͱී௨ʹॻ͘ͱɺ୯Ґ͕ҧ͏ͷͰ1౓ͷΫΤϦͰऔಘ͢Δ͜ͱ͸Ͱ͖·ͤΜɻ αϒΫΤϦͩͱɺ͜Μͳײ͡Ͱॻ͘ͱҰ౓ʹͱΕ·͢ɻ جຊతʹ͸ɺҰ౓ʹऔಘ͢Δඞཁ͕͋Δͷ͸෼ੳΫΤϦͱ͔ͳͷͰɺௐࠪ΍ঢ়گ֬ೝͰ͸ී௨ʹผʑͷΫΤϦͰ2ճʹ෼͚ͯऔΔ΄͏͕ྑ͍Ͱ͢ɻ SELECT y.provider_id, m.this_month_begin_num, y.this_year_begin_num

    FROM ( SELECT provider_id, COUNT(*) as this_year_begin_num FROM contract WHERE DATE_FORMAT(contract_begin_date, “%Y”) = DATE_FORMAT(now(), “%Y”) ) m LEFT JOIN ( SELECT provider_id, COUNT(*) as this_month_begin_num FROM contract WHERE DATE_FORMAT(contract_begin_date, “%Y-%M”) = DATE_FORMAT(now(), “%Y-%M”) ) y ON m.provider_id = y.provider_id
  58. ΫΤϦ࣮श(αϒΫΤϦ) billingςʔϒϧ͔ΒɺҎԼͷ৚݅ͰσʔλΛऔಘ͍ͯͩ͘͠͞ - billing_date͕ࠓ݄ʹͳ͍ͬͯΔσʔλͷtotal_billing_priceͷ߹ܭ - billing_date͕ࠓ೥ʹͳ͍ͬͯΔσʔλͷtotal_billing_priceͷ߹ܭ ※͕ ࠓ݄ͷbilling_dateΛऔಘ͢Δ৚݅ DATE_FORMAT(billing_date, “%Y-%M”)

    = DATE_FORMAT(now(), “%Y-%M”) ※͕ ࠓ೥ͷbilling_dateΛऔಘ͢Δ৚݅ DATE_FORMAT(billing_date, “%Y”) = DATE_FORMAT(now(), “%Y”)
  59. ͜ΕͰSQL Meister ΁ͷಓ~ࢀরฤ~ ͸मྃͰ͢ɻ ࡉ͔͍ߏจͱ͔͸͋Μ·Γ֮͑ͯͳͯ͘ɺׂͱຖճ άάͬͨΓͯ͠ΔͷͰͲ͏͍͏ಈ͖Λ͢Δ͔ͷΠ ϝʔδ͑͞ग़དྷ͍ͯΕ͹ྑ͍ͱࢥ͍·͢ʂ ͋ͱ͸ॻ͖·͘Δ͚ͩʂ