sql - 同一行的数据

Question

我有一张 table 。

ID  Contact_Details   Pref
1   1234567890         A 
1   test@test.com      A
2   1234567891         A
2   test1@test.com     B

简单的选择查询不能解决这个问题

select contact_details as mobile,contact_details as email 
from table 
where pref in (A,B);

我想要这样的结果:

ID Mobile      Pref  Email           Pref
1  1234567890   A    test@test.com    A
2  1234567891   A    test@test.com    B

请注意，我对电子邮件和移动设备有不同的偏好，我希望在输出中出现。

Answer 1

你可以这样写:

select t1.ID, t1.Contact_Details as Mobile, t1.Pref as PrefMobile,
       t2.Contact_Details as Email, t2.Pref as PrefEmail
from Table t1
inner join Table t2
on t1.ID = t2.ID
where t1.Contact_Details not like '%@%'
and t2.Contact_Details like '%@%'