[技術討論] SQL 求救

hardcore

有以下2個TABLE

Country_Table
-----------------
Country_ID
Citizen_ID
Country_Name

Citizen_Table
---------------------
Citizen_ID
Country_ID
Citizen_FirstName
Citizen_LastName
----------------------
如果我想extract 所有Citizen_FirstName = "Siu Ming" , 但要每個"Siu Ming"的Citizen_LastName都唔同, 應點寫?


expected result
-------------------
Country    Citizen_FirstName     Citizen_LastName     
HK              Siu Ming                          CHAN
HK              Siu Ming                          WONG
US               Siu Ming                          LAU
JP                Siu Ming                           CHANG

神秘二代

hints: distinct

garrodran99

你未講排序規則

lmyraymond

希望可以幫到你

select Country_Name [Country], 'Siu Ming' [Citizen_FirstName], Citizen_LastName
from Citizen_Table
left outer join Country_Table on Citizen_Table.Country_ID=Country_Table.Country_ID
where Citizen_FirstName='Siu Ming'
group by Citizen_LastName,Country_Name
order by Country_Name

henrywho

如果 Country 唔同但係 Citizen_LastName 一樣, 你想點 handle?

lmyraymond

回復 5# henrywho


    我條 sql, 可以出晒所有 Country Code by same Name

來來回回

SELECT Country_Name "COUNTRY", DISTINCT Citizen_FirstName, Citizen_LastName
FROM Country_Table, Citizen_Table
WHERE Country_Table.Country_ID = Citizen_Table.Country_ID
AND Citizen_FirstName='Siu Ming'

咁樣得唔得？

henrywho

回復 7# 來來回回


can you really use DISTINCT in this way?

來來回回

回復  來來回回


can you really use DISTINCT in this way?
henrywho 發表於 2013-9-11 23:09

I am a new comer of sql, I am really not sure
I know the syntax of different sql clients is different, but it may work with Tord with Oracle
Please forgive me if it is not working

henrywho

I know the syntax of different sql clients is different, but it may work with Tord with Oracle
Please forgive me if it is not working
來來回回 發表於 2013-9-12 06:47

Don't take it so serious.
But I do not think it works with Oracle.