作者: hardcore 時間: 2013-9-3 22:45 標題: SQL 求救
有以下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
作者: 神秘二代 時間: 2013-9-4 00:29
hints: distinct
作者: garrodran99 時間: 2013-9-4 09:04
你未講排序規則
作者: lmyraymond 時間: 2013-9-6 21:53
希望可以幫到你
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 時間: 2013-9-10 11:42
如果 Country 唔同但係 Citizen_LastName 一樣, 你想點 handle?
作者: lmyraymond 時間: 2013-9-11 16:59
回復 5# henrywho
我條 sql, 可以出晒所有 Country Code by same Name
作者: 來來回回 時間: 2013-9-11 20:33
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 時間: 2013-9-11 23:09
回復 7# 來來回回
can you really use DISTINCT in this way?
作者: 來來回回 時間: 2013-9-12 06:47
回復 來來回回
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 時間: 2013-9-12 08:44
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.
作者: 來來回回 時間: 2013-9-12 11:08
Don't take it so serious.
But I do not think it works with Oracle.
henrywho 發表於 2013-9-12 08:44
I have just tested it in office
distinct can only be used at the first column of query
move citzen_firstname to the first column should be OK
SELECT "COUNTRY", Citizen_FirstName, Citizen_LastName
FROM(
SELECT DISTINCT Citizen_firstName, Country_Name "COUNTRY", Citizen_LastName
FROM Country_Table, Citizen_Table
WHERE Country_Table.Country_ID = Citizen_Table.Country_ID
AND Citizen_FirstName='Siu Ming')
it seems so stupid
作者: henrywho 時間: 2013-9-13 10:44
"DISTINCT" must follow "select"
and it works over all columns selected.