Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

! asked in 電腦與網際網路程式設計 · 2 decades ago

關於SQL語法查詢distinct的問題…

我的問題敘述如下,現在我表單的資料有二個column

===================================================

column_a column_b

VIP1 virE2

VIP1 virE2

VIP1 virF

virE2 VIP1

=====================================================

我的SQL語法是:

SELECT DISTINCT column_a, column_b

FROM table_name;

則結果為:

===========================================

column_a column_b

VIP1 virE2*******

VIP1 virF

virE2 VIP1*******

===========================================

distinct的功用是「顯示出不重覆的資料欄位」,所以資料相同但排列不同的資料一樣會被抓出(注意打*的那兩筆),如果今天我只想抓出

==================================================

column_a column_b

VIP1 virE2

VIP1 virF

===================================================

我的SQL語法要如何下呢??感謝網路的朋友幫忙哦,十點相送^^||||

Update:

你的意思是因為

SELECT DISTINCT column_a, column_b

FROM table_name;

就會出現兩筆,但乾脆限定column_a=''VIP1'就只會抓出一筆囉!

Update 2:

column_a column_b

VIP1 virE2

VIP1 virE2

VIP1 virF

AT34 AT1G

virE2 VIP1

VIP1 pei4

可是如果現在我的寫法想要再更一般化,想要查詢結果

為下:

===============================================

VIP1 virE2

VIP1 virF

AT34 AT1G

VIP1 pei4

=================================================

Update 3:

並且計對所有出現在這個表單內容的symbol(不管在column_a或是column_b)作出現頻率的統計,例如

================================================

VIP1 3

virE2 1

virF 1

AT34 1

AT1G 1

pei4 1

================================================

又該如何下SQL語言呢?

Update 4:

你的補充解答不行內?^^|||

Update 5:

請問「周」,能對你所寫的sql語作作一下解說嗎?還有你第二個case when的條件,跟第一個case when是相同的,其中大於「>」是不是要改為小於「

2 Answers

Rating
  • ?
    Lv 6
    2 decades ago
    Favorite Answer

    SELECT DISTINCT column_a, column_bFROM table_name where  column_a ='vip1'

    2006-02-18 12:22:16 補充:

    請改寫成SELECT COLUMN_A ,COUNT(*) FROM TABLE_NAME GROUP BY COLUMN_A

    2006-02-18 12:24:40 補充:

    請改寫成SELECT COLUMN_A ,COUNT(*) FROM TABLE_NAME GROUP BY COLUMN_A

  • 2 decades ago

    SELECT DISTINCT CASE WHEN column_a>column_b then column_a else column_b end as vcolumn_a,CASE WHEN column_a>column_b then column_b else column_a end as vcolumn_b

    FROM table_name;

    2006-02-27 08:51:23 補充:

    做case when 主要用意是對這2各欄位作排序,而至於你所問的">","<"是不用改的因為條件成立後所帶的欄位是不一樣的(一各式column_a,另一各為column_b),如果"<",">"要改為不一樣,那條件成立後所帶的欄位就要一樣

Still have questions? Get your answers by asking now.