[µo°Ý] SQL ¦p¦óLEFT JOIN ¦P¤@­ÓTABLE ¤£¦P±ø¥ó? SQL, LEFT JOIN, SUM

PKKO

¦U¦ì¤j¤j¦n,·Q½Ð±Ð¦p¦ó¤USQL »yªkªº°ÝÃD
Aªí:
ID,©m¦W
1,ªô¥ý¥Í
2,¶À¥ý¥Í
3,³¯¥ý¥Í

Bªí:
ID,ª÷ÃB,µ²²M»P§_,¤é´Á
1,100,FALSE,2019/1/1
1,100,TRUE,2020/1/1
2,100,FALSE,2019/1/1
2,100,TRUE,2020/1/
3,100,FALSE,2019/1/1

¹w´Áµ²ªG
©m¦W(¥ÎID¤ñ¹ï),ª÷ÃB(¤£ºÞµ²²M»P§_¡Aª½±µ¥Hid¥[Á`),¤é´Á(¥u§ì¨úµ²²M»P§_¬°TRUEªº¤é´Á)
ªô¥ý¥Í,200,2020/1/1
¶À¥ý¥Í,200,2020/1/1
³¯¥ý¥Í,100,NULL

½Ð°Ý³o¼Ëªº¹w´Áµ²ªG­n¦p¦ó¤U»yªk?(¥D­n¬O¤é´Á¥u§ì¨úµ²²MÄæ¦ì¬°TRUEªº¤é´Á¡A¦P®É¤S­n¥HID¬Û¦Pªº¥[Á`ª÷ÃB)
³o¨â­Ó²V¦b¤@°_
¤£ª¾¹D¸Ó¦p¦ó¤U»yªk...

joey0415

¦U¦ì¤j¤j¦n,·Q½Ð±Ð¦p¦ó¤USQL »yªkªº°ÝÃD
Aªí:
ID,©m¦W
1,ªô¥ý¥Í
2,¶À¥ý¥Í
3,³¯¥ý¥Í

Bªí:
ID,ª÷ÃB, ...
PKKO µoªí©ó 2020-1-14 12:54

½Ð¬Ý¤U­±ªº³sµ²

ÃöÁä¦r  group by  sum()      left join   on     ¤£·|¤ÓÃø

https://www.1keydata.com/tw/sql/sqlgroupby.html
https://www.1keydata.com/tw/sql/sqlouterjoin.html

PKKO

¦^´_ 2# joey0415


    ·PÁ¤j¤j¦^´_
sum ©M left join
³o¨â­Ó¥\¯à§Ú³£·|¥Î

¦Ó¦¹¦¸µo°Ýªº­ì¦]
¬O¦]¬°
¦³¨â­Ó±ø¥ó

1.ª÷ÃB¥²¶·¥[Á`
2.¤é´Á¥²¶·¥u±a¤J¦³µ²²M§À´Úªº¤é´Á

¦Óleft join ­Y¥Îid ¬Û¦P,«h¤é´Á·|¿ù»~
­Yleft join ­Y¥Îid +µ²²M§À´Ú¬°true «h ª÷ÃB·|¿ù»~

©Ò¥H·Q½Ð±Ð¤@¤U¦U¦ì¤j¤j

joey0415

¦^´_  joey0415


    ·PÁ¤j¤j¦^´_
sum ©M left join
³o¨â­Ó¥\¯à§Ú³£·|¥Î

¦Ó¦¹¦¸µo°Ýªº­ì¦]
¬O¦] ...
PKKO µoªí©ó 2020-1-14 17:09

    ¥ý group by  ©M sum()  ¦A¨â¦¸  left join  (²Ä¤@¦¸¥ý  id¡A²Ä¤G¦¸¥ý±Nªí¤G¬°true  select «á¡A¦A¦ê±µ°_¨Ó¡A¦P¼Ë¤]¬O  id=id)

jcchiang

¦^´_ 1# PKKO

¸Õ¸Õ¬Ý
SELECT a.©m¦W,a.ª÷ÃB,b.¤é´Á
FROM (Select A.©m¦W,sum(b.ª÷ÃB) as ª÷ÃB
from (a inner join B on a.ID=B.ID) group by A.©m¦W  ) as a
LEFT OUTER JOIN (select a.©m¦W,b.¤é´Á  
from a,b
where b.µ²²M»P§_='True' and a.ID=b.ID  ) as b ON a.©m¦W=b.©m¦W