This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

技多不压身，工到自然成：安省技工证书特训班，点击咨询报名！

Ad by

技多不压身，工到自然成：安省技工证书特训班，点击咨询报名！

If you need the first Phone Number (order by Phone ID):

deep_blue(BLUE)

Select ID, HomePhone, WorkPhone From(
Select ROW_NUMBER() over(PARTITION by U.ID Order by H.PHONE_ID, W.PHONE_ID) row, U.ID, H.PHONE_NO HomePhone, W.PHONE_NO WorkPhone FROM
[USER] U LEFT JOIN
(Select UP.USER_ID UID, P.PHONE_NO, P.ID PHONE_ID FROM USER_PHONE UP, PHONE P Where UP.PHONE_ID = P.ID AND UP.[TYPE] ='HOME') H
ON U.ID = H.UID LEFT JOIN
(Select UP.USER_ID UID, P.PHONE_NO, P.ID PHONE_ID FROM USER_PHONE UP, PHONE P Where UP.PHONE_ID = P.ID AND UP.[TYPE] ='WORK' ) W
ON U.ID = W.UID
) A Where row =1

(#6188290@0)
Last Updated: 2010-7-21
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / 学科技术讨论 / 请教SQL高手, thanks a lot in advance! -michaellucad(michaellucad); 2010-7-19 {328} (#6185125@0)
TABLE USER（
ID）

TABLE PHONE（
ID，
PHONE＿NO）

TABLE USER＿PHONE（
USER＿ID，
PHONE＿ID，
TYPE （HOME OR WORK OR ELSE））

I need to get user with home phone and work phone as 1 line. But user can have 0-many home phone or work phone; if multi-phone, get first one; else leave blank.
How to get a efficient one?

you're not clear enough. do you want first home phone +first work phone as the output? -ssy214(寸草); 2010-7-19 (#6185169@0)

yes. -michaellucad(michaellucad); 2010-7-19 (#6185498@0)

if this is for SQL Server 2005 or above, you can directly use pivot table function -ssy214(寸草); 2010-7-19 (#6185535@0)

I am using oracle... -michaellucad(michaellucad); 2010-7-20 (#6186044@0)

什么数据库？ -newkid(newkid); 2010-7-19 {409} (#6185317@0)
SELECT U.USER_ID
,H.PHONE_NO
,W.PHONE_NO
FROM (SELECT USER_ID
,MIN(CASE WHEN TYPE='HOME' THEN PHONE_ID END) AS HOME_PHONE_ID
,MIN(CASE WHEN TYPE='WORK' THEN PHONE_ID END) AS WORK_PHONE_ID
FROM USER_PHONE
GROUP BY USER_ID
) U
LEFT JOIN PHONE H ON U.HOME_PHONE_ID = H.ID
LEFT JOIN PHONE W ON U.WORK_PHONE_ID = W.ID

重写 -newkid(newkid); 2010-7-19 {237} (#6185335@0)
SELECT USER_ID
,MIN(CASE WHEN TYPE='HOME' THEN P.PHONE_NO END) AS HOME_PHONE_NO
,MIN(CASE WHEN TYPE='WORK' THEN P.PHONE_NO END) AS WORK_PHONE_NO
FROM USER_PHONE U LEFT JOIN PHONE P ON U.PHONE_ID = P.ID
GROUP BY USER_ID
does result return work phone and home phone as 2 rows? I need 1 row for both phone. thanks though -michaellucad(michaellucad); 2010-7-19 (#6185503@0)

it returns one row per user_id with two columns -newkid(newkid); 2010-7-20 (#6186133@0)

If you need the first Phone Number (order by Phone ID): -deep_blue(BLUE); 2010-7-21 {523} (#6188290@0)
Select ID, HomePhone, WorkPhone From(
Select ROW_NUMBER() over(PARTITION by U.ID Order by H.PHONE_ID, W.PHONE_ID) row, U.ID, H.PHONE_NO HomePhone, W.PHONE_NO WorkPhone FROM
[USER] U LEFT JOIN
(Select UP.USER_ID UID, P.PHONE_NO, P.ID PHONE_ID FROM USER_PHONE UP, PHONE P Where UP.PHONE_ID = P.ID AND UP.[TYPE] ='HOME') H
ON U.ID = H.UID LEFT JOIN
(Select UP.USER_ID UID, P.PHONE_NO, P.ID PHONE_ID FROM USER_PHONE UP, PHONE P Where UP.PHONE_ID = P.ID AND UP.[TYPE] ='WORK' ) W
ON U.ID = W.UID
) A Where row =1
user defined aggregation function -kenchow27(凯包子); 2011-1-30 (#6484870@0)

@Las Vegas

If you need the first Phone Number (order by Phone ID):

Replies, comments and Discussions:

More Topics