This topic has been archived. It cannot be replied.
-
工作学习 / 学科技术讨论 / 请教SQL高手, thanks a lot in advance!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?
-michaellucad(michaellucad);
2010-7-19
{328}
(#6185125@0)
-
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)
-
什么数据库？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
{409}
(#6185317@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
-newkid(newkid);
2010-7-19
{237}
(#6185335@0)
-
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)
-
user defined aggregation function
-kenchow27(凯包子);
2011-1-30
(#6484870@0)