php - 在一行mysql中为一个用户选择所有行

Question

我有一个具有以下结构的 mysql 表

id,username,attribute,value,realname,birthdate,phone 

我在表中有这些记录

1,john,Cleartext-Password,ss,johnsmith,1/1/1990,9786865754
2,john,Expiration,20/1/2018,null,null,null

我想写一个显示下面结果的查询

username|password|expir     |realname|birthdate|phone 
john    |ss      |20/1/2018 |johnsmith|1/1/1990|9786865754

我尝试使用下面提到的查询。但我似乎没有得到想要的结果。

select c1.username ,c1.value as 'password',c1.adsoyad,c1.telefon,c1.email,c1.dtarih,c1.tcno,c2.value as 'expired' from radcheck c1 INNER join radcheck c2 on c1.username=c2.username and c1.attribute='Cleartext-Password' and c2.attribute='Expiration' and c1.username='john' and c2.username='john'

Answer 1

条件聚合是否符合您的要求？

select c.username,
       max(case when c.attribute = 'password' then c.value end) as password,
       max(case when c.attribute = 'expiration' then c.value end) as expiration,
       max(c.realname) as realname, min(c.birthdate) as birthdate, max(c.phone) as phone
from radcheck c
group by username