[µo°Ý] Excel VBA SQL ¬d¸ß°ÝÃD

mgame168

½Ð±Ð¦U¦ì¥ý¶i~

§Ú¦³¤@mdb¸ê®Æ®w,¤º¦³3­Ótable¤À§O¬°
1. dbo_pro
   prno, kano, a01,.........
2. dbo_prok
   kano, name,......
3. dbo_aa
   a01, name, names.......

§Ú·Q¨Ì±ø¥óSheet1.Range("K9")¬°·j´M±ø¥ó(prno),
±q¸ê®Æ®wdbo_pro -->kano, a01 left join ¥t¥~
¨â­ÓTable¤ºªº¸ê®Æ,¦ý°õ¦æ®ÉÅã¥Ü¿ù»~"Automation ¿ù»~'

¤U¸ü (11.93 KB)

2011-10-17 11:11

·Q½Ð±Ð¬O§ÚªºSQL»yªk¿ù»~ÁÙ¬OEXCEL VBA ¹ïSQL
¬d¸ß»yªk¦³­­¨î©O?

·P®¦~

¦pªþ¥ó
Input2.zip (39.63 KB)

¤U¸ü¦¸¼Æ: 16

2011-10-17 11:21

mgame168

¦^´_ 2# kimbal


    ·PÁ kimbal ª©¤j
     isnull() ¸g¬dÃÒ«á, ¦bMS-SQL ¥i¥Î,

     Access¤¤ªºIsNull()     
      ¦bAccess¤¤¡AIsNullªº§@¥Î¶È¶È¬O§PÂ_¬O§_¬°ªÅ­È
     ¤£¹LAccessÁÙ¬O¦³¤ä´©MS-SQL IsNullªº¬Û¦ü«ü¥O½X¡A
     ¦bAccess¬O¥Î iif ´À¥N..
Select iif(IsNull( express ), value1, value2 ) From TableName
»yªk»¡©ú¡A§PÂ_express¬O§_¬°ªÅ¡A­Y¬OªÅªº¦^¶Çvalue1¡A¤Ï¤§«h¦^¶Çvalue2

LEFT JOIN ªº¼gªk¦p¦P±z¼gªº¤è¦¡

dbo_aa.names
names ¦n¹³­è¦n¬OÃöÁä¦r, ¨S¿ùªº½T¬OÃöÁä¦r
¦ý¸ê®Æ®wªºÄæ¦ì¦WºÙ§ÚµLªk§ó§ï, ©Ò¥H¹ï©ó
«O¯d¦rªº½Ä¬ð
¸Ñ¨M¿ìªk´N¬O±Nnames§ï¦¨[names]´N¸Ñ¨M¤F

¥H¤Uªº»yªk§Ú¦³­×§ï¹L
     SQL = "Select dbo_pro.*, dbo_prok.name, dbo_aa.[names] "
     SQL = SQL & "From (dbo_pro left outer join dbo_aa on dbo_pro.a01=dbo_aa.a01) "
     SQL = SQL & "left join dbo_prok on dbo_pro.kano=dbo_prok.kano "
     SQL = SQL & "Where dbo_pro.prno ='" & Sheet1.Range("K9") & "'"
     SQL = SQL & " Order by dbo_pro.prno"

·PÁÂkimbalª©¤jªº«ü¾É

Input2.zip (40.9 KB)

¤U¸ü¦¸¼Æ: 8

2011-10-24 17:51

kimbal

dbo_aa.names
names ¦n¹³­è¦n¬OÃöÁä¦r,  Â²³æªº¸Ü´«¤@¤UÄæ¦W

¥t¥~LEFT JOIN, ISNULL ªº¦bACCESS·|¦³¤£¦P¼gªk:
(¥H¤U§Ú§âDBO_AA.NAMES§ï¦¨DBO_AA.NAME)
      SQL = "Select dbo_pro.*,  dbo_aa.name as anames, iif(isnull(dbo_prok.name), '',dbo_prok.name)as namek "
     SQL = SQL & "From (dbo_pro left outer join dbo_aa on dbo_pro.a01=dbo_aa.a01) "
     SQL = SQL & "left join dbo_prok on dbo_pro.kano=dbo_prok.kano "
     SQL = SQL & "Where dbo_pro.prno ='" & Sheet1.Range("K9") & "'"
     SQL = SQL & " Order by dbo_pro.prno"