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

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

按列印兼容模式列印這個話題 列印話題    把這個話題寄給朋友 寄給朋友    訂閱主題
reply to topicthreaded modego to previous topicgo to next topic
本主題所含的標籤
無標籤
作者 請問如何篩選出重複性資料
akamikan





發文: 7
積分: 0
於 2006-03-29 09:42 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
想請教一下,
如果想篩選出重複性資料但是必須擁有鍵值欄位,該如何下指令?
例如
001 A B
002 A B
005 C D
009 C D
011 C D

謝謝各位大大幫忙


reply to postreply to post
作者 Re:請問如何篩選出重複性資料 [Re:akamikan]
anthonychen

外線交給我

版主

發文: 2033
積分: 8
於 2006-03-29 09:57 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
使用 DISTINCT 語句。
即 SELECT DISTINCT "欄位" FROM "表格"。


reply to postreply to post
作者 Re:請問如何篩選出重複性資料 [Re:akamikan]
akamikan





發文: 7
積分: 0
於 2006-03-29 10:06 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:請問如何篩選出重複性資料 [Re:akamikan]
anthonychen

外線交給我

版主

發文: 2033
積分: 8
於 2006-03-29 10:37 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 與 HAVING 就可以達到你的需求。
例如:

1
2
3
SELECT * FROM "表格"  
GROUP BY "有重複值的欄位名稱"
HAVING count(*)>1


這表示找出該欄位值出現超過一次的資料。


reply to postreply to post
作者 Re:請問如何篩選出重複性資料 [Re:akamikan]
akamikan





發文: 7
積分: 0
於 2006-03-29 12:36 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
恩 十分感謝Smile

reply to postreply to post
作者 Re:請問如何篩選出重複性資料 [Re:akamikan]
雨末星塵





發文: 5
積分: 0
於 2006-04-13 12: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
我想找出Isin這個欄位,相同的資料,出現在哪幾個Acct_Id
譬如有哪幾個Acct_Id有Isin="0035110000017"
語法這樣下:
SELECT Acct_Id,Isin FROM CBF001D1 GROUP BY Isin HAVING count(*)>1;
SQLServer會出現錯誤訊息:
伺服器: 訊息 8120,層級 16,狀態 1,行 1
資料行 'CBF001D1.Acct_Id' 在選取清單中無效,因為它並未包含在彙總函數或 GROUP BY 子句中。
請問我應該怎麼下才對?!


browser edited on 2006-04-13 13:05
reply to postreply to post
作者 Re:請問如何篩選出重複性資料 [Re:akamikan]
ajax

Thiago Alves



發文: 675
積分: 3
於 2006-04-13 13:31 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
TO 雨末星塵
1
SELECT Acct_Id FROM CBF001D1 WHERE Isin='0035110000017'

照你的意思,應該是這樣吧???

ajax.chien


reply to postreply to post
作者 Re:請問如何篩選出重複性資料 [Re:ajax]
雨末星塵





發文: 5
積分: 0
於 2006-04-14 00:15 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
ajax wrote:
TO 雨末星塵
1
SELECT Acct_Id FROM CBF001D1 WHERE Isin='0035110000017'

照你的意思,應該是這樣吧???

ajax.chien


有點類似喔!!
但是我不是要針對某筆Isin去搜尋Acct_Id!!
而是要把Isin超過兩筆的資料都找出來
所以應該會出現類似下面這樣的這樣的資料
Acct_Id Isin
1111111 0035110000017
2222222 0035110000017
3333333 0035110000018
4444444 0035110000018
5555555 0035110000018
6666666 0035110000019
7777777 0035110000019


reply to postreply to post
作者 Re:請問如何篩選出重複性資料 [Re:akamikan]
ajax

Thiago Alves



發文: 675
積分: 3
於 2006-04-14 09:24 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 * FROM CBF001D1 WHERE Isin IN (SELECT Isin FROM CBF001D1 GROUP BY Isin HAVING COUNT(*) >1)

這個寫法很爛...不過可以用...Black Eye

ajax.chien


reply to postreply to post
作者 Re:請問如何篩選出重複性資料 [Re:akamikan]
james1225





發文: 107
積分: 0
於 2007-09-20 14:25 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
那如果要選 Isin 中相同group 裏,最大值呢??
ex :

3333333 0035110000018
4444444 0035110000018
5555555 0035110000018

中要選出 5555555 0035110000018 這一行呢??
一個SQL可以解決嗎?
謝謝


reply to postreply to post
作者 Re:請問如何篩選出重複性資料 [Re:james1225]
ajax

Thiago Alves



發文: 675
積分: 3
於 2007-09-20 17:32 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
james1225 wrote:
那如果要選 Isin 中相同group 裏,最大值呢??
ex :

3333333 0035110000018
4444444 0035110000018
5555555 0035110000018

中要選出 5555555 0035110000018 這一行呢??
一個SQL可以解決嗎?
謝謝


1
2
3
4
5
6
SELECT
   *
FROM
   QQ AS X
WHERE
   X.Acct_Id = (SELECT MAX(Z.Acct_id) FROM QQ AS Z WHERE X.Isin = Z.Isin)


reply to postreply to post
作者 Re:請問如何篩選出重複性資料 [Re:ajax]
james1225





發文: 107
積分: 0
於 2007-09-21 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
感謝,ajax大大,對我的幫助很大

reply to postreply to post
作者 Re:請問如何篩選出重複性資料 [Re:ajax]
james1225





發文: 107
積分: 0
於 2007-12-06 14:12 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
ajax wrote:
1
2
3
4
5
6
SELECT
   *
FROM
   QQ AS X
WHERE
   X.Acct_Id = (SELECT MAX(Z.Acct_id) FROM QQ AS Z WHERE X.Isin = Z.Isin)



感謝AJAX大大的幫助,這是我這次的問題,很類似,寫下來供大家參考,謝謝!!

ReqFormNo LicenseNo RegCheckItem
---------------------------------------------------------
20070916  AHOP096367 100000
20070923  AHOP096367 000010
20070924  AHOP096367 000010
20070926  AHOP096367 000001
20070927  AHOP096367 010000

SQL
--------------------------------------
SELECT *
FROM Req AS X
WHERE
X.ReqFNo = (SELECT MAX(Z.ReqFNo) FROM Req AS Z WHERE X.RegItem = Z.RegItem AND X.License = Z.License)
AND License = 'AHOP096367'

結果:
-------------------
20070916  AHOP09667 100000  
20070927  AHOP09667 010000  
20070929  AHOP09667 001000  
20070924  AHOP09667 000010  
20070926  AHOP09667 000001


reply to postreply to post
成就非來自專案結束,而是過程中尋找解決問題方式!
http://tw.myblog.yahoo.com/james-tsai
» 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