JWorld@TW the best professional Java site in Taiwan
      註冊 | 登入 | 全文檢索 | 排行榜  

» JWorld@TW » JDBC/SQL討論區 » SQL 語法  

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友    訂閱主題
reply to postflat modego to previous topicgo to next topic
本主題所含的標籤
無標籤
作者 Re:SQL語法問題(急) [Re:markk1933]
kentyeh





發文: 643
積分: 6
於 2018-01-09 00:56 user profilesend a private message to userreply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
下次請排一下版,不然看的很辛苦
1
2
3
4
5
6
7
8
9
10
11
12
13
14
select subStr(safpf_buss_date,0,11) as subStr_buss_date,
       expsm8_id||expsm8_name as driverid, 
       safpf_route_no as route_no,
       safpf_bus_no as bus_no,
       psc5_service_rate as service_rate,
       psc5_type_name as type_name,
       sum(safpf_boarding_qty)as sum, 
       sum(safpf_boarding_qty)as amt,
       sum(safpf_boarding_qty)as people 
from expsm8 
inner join safpf on CAST(safpf_driver_no AS INTEGER)=CAST(expsm8_id AS INTEGER) 
inner join psc5 on psc5_card_type=safpf_card_type 
group by driverid,subStr_buss_date 
order by subStr_buss_date

從語法來看,既然沒有Group By 到 safpf_route_no 欄位,所以該有的值都應該出現
所以明顯的是在 JOIN safpf 與 psc5 時因為不符 Join條件被排除掉了,
你不要急著直接Goup By 資料,可以試著先找出JOIN 條件是否可以得出safpf_route_no='2'的資料
1
2
3
4
5
select safpf_driver_no,safpf_card_type,expsm8_id,psc5_card_type
from expsm8 
inner join safpf on CAST(safpf_driver_no AS INTEGER)=CAST(expsm8_id AS INTEGER) 
inner join psc5 on psc5_card_type=safpf_card_type 
where safpf_route_no='2'

如果不行再找找JOIN條件的欄位(card_type)是否有空白字元照成的原因


reply to postreply to post
話題樹型展開
人氣 標題 作者 字數 發文時間
361 SQL語法問題(急) markk1933 0 2018-01-08 08:05
356 Re:SQL語法問題(急) kentyeh 1064 2018-01-09 00:56
» JWorld@TW »  JDBC/SQL討論區 » SQL 語法

reply to postflat modego to previous topicgo to next topic
  已讀文章
  新的文章
  被刪除的文章
Jump to the top of page

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8