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

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

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友    訂閱主題
reply to topicthreaded modego to previous topicgo to next topic
本主題所含的標籤
無標籤
作者 請問關於group by用法(已解決...謝謝各位的力挺)
Attached0124





發文: 8
積分: 0
於 2011-10-27 10:50 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

目前select id,max(date) from tablename group by id 只能SHOW出ID和他的最大date
但我想多SHOW出status 不曉得 這邊該怎麼去做修改

我是想依照各ID的最大date值來取得資料


Attached0124 edited on 2011-10-28 14:33
reply to postreply to post
作者 Re:請問關於group by用法 [Re:Attached0124]
dolphin92





發文: 33
積分: 0
於 2011-10-27 11:53 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
多加個status欄位就好了吧?
select id,status,max(date) from tablename group by id


reply to postreply to post
作者 Re:請問關於group by用法 [Re:dolphin92]
Attached0124





發文: 8
積分: 0
於 2011-10-27 12:39 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
訊息 8120,層級 16,狀態 1,行 1
資料行 'TABLENAME.STATUS' 在選取清單中無效,因為它並未包含在彙總函數或 GROUP BY 子句中。
他會要求我把 status 寫進group by
這樣子跑出來的結果也不會是我要的


reply to postreply to post
作者 Re:請問關於group by用法 [Re:Attached0124]
winddknight





發文: 162
積分: 0
於 2011-10-27 13:00 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
select STATUS from table where id = ?


subquery ?? join table ??


reply to postreply to post
作者 Re:請問關於group by用法 [Re:Attached0124]
kenshin520





發文: 109
積分: 0
於 2011-10-27 14:27 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
試試這個不用group by可是performance不是很好的方法

select id, status, date from tablename t
where not exists (select 1 from tablename tt where t.id = tt.id and t.date < tt.date)


reply to postreply to post
作者 Re:請問關於group by用法 [Re:kenshin520]
Attached0124





發文: 8
積分: 0
於 2011-10-27 14:40 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
感謝這位大大的解答...就暫時不先考慮效能吧><應急應急

reply to postreply to post
作者 Re:請問關於group by用法 [Re:Attached0124]
roytsang





發文: 620
積分: 1
於 2011-10-27 15:59 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
select id,max(date),status from tablename group by id,status,date

reply to postreply to post
作者 Re:請問關於group by用法 [Re:roytsang]
Attached0124





發文: 8
積分: 0
於 2011-10-27 17:03 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
select id,max(date),status from tablename group by id,date,status
這樣子會SHOW出

因為同樣ID也有不同的status 這樣子就不能縮成一筆


reply to postreply to post
作者 Re:請問關於group by用法 [Re:Attached0124]
roytsang





發文: 620
積分: 1
於 2011-10-27 17:49 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
你是用mysql server 嗎?
select max(date),id,status from tablename group by id

這樣就行了

(縮略圖,點擊圖片鏈接看原圖)


roytsang edited on 2011-10-27 17:55
reply to postreply to post
作者 Re:請問關於group by用法 [Re:roytsang]
roytsang





發文: 620
積分: 1
於 2011-10-27 17:55 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
result

(縮略圖,點擊圖片鏈接看原圖)


reply to postreply to post
作者 Re:請問關於group by用法 [Re:roytsang]
Attached0124





發文: 8
積分: 0
於 2011-10-27 18:34 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
我是用MSSQL 2008~"~





reply to postreply to post
作者 Re:請問關於group by用法 [Re:Attached0124]
fsz570





發文: 19
積分: 0
於 2011-10-28 09:43 user profilesend a private message to usersend email to fsz570reply to postreply to postsearch all posts byselect and copy to clipboard. 
ie only, sorry for netscape users:-)add this post to my favorite list
This work on my Oracle, it is ANSI sql
I think it should be work on MS SQL too

1
2
3
4
5
6
SELECT id, date, status
  FROM tablename
 WHERE (id, date) in(
SELECT DISTINCT id, MAX(date)
  FROM tablename
 GROUP BY id)


reply to postreply to post

David Wu(吳清霖)
E-mail: fsz570@gmail.com

世事短如春夢,人情薄似秋雲。 不須計較苦勞心。萬事原來有命。
幸遇三杯酒好,況逢一朵花新。 片時歡笑且相親。明日陰晴未定。
作者 Re:請問關於group by用法 [Re:Attached0124]
roytsang





發文: 620
積分: 1
於 2011-10-28 11:19 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
身邊沒有ms sql server 2008,只有access,剛剛試過這條sql ok:

select a.id,a.status,b.vv
from
test a,
(sELECT test.id, max(test.date) AS vv
FROM test
GROUP BY test.id)b
where
a.id=b.id and
b.vv=a.date


reply to postreply to post
作者 Re:請問關於group by用法 [Re:fsz570]
Attached0124





發文: 8
積分: 0
於 2011-10-28 14:23 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

這可能是小問題吧><
但我是初學者不太會改


reply to postreply to post
作者 Re:請問關於group by用法 [Re:roytsang]
Attached0124





發文: 8
積分: 0
於 2011-10-28 14:28 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

這位大大真是太強了!!!
結果出來正確!!
我的思緒一整個就是被這段給打亂了...((我是新手的關係吧~"~


reply to postreply to post
作者 Re:請問關於group by用法 [Re:Attached0124]
roytsang





發文: 620
積分: 1
於 2011-10-28 14:51 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
多謝你的過獎,seqno + task 這兩個field 的組合要unique 才可以保證答案準確。

reply to postreply to post
» JWorld@TW »  JDBC/SQL討論區 » SQL 語法

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

JWorld@TW 本站商標資訊

Powered by Powerful JuteForum® Version Jute 1.5.8