mysql - 简单的查询设计

Question

简单的查询设计帮助。这是我的查询:

SELECT tkt.ticket_id, tkt.`number`, val.field_id, FF.name,    val.value 
FROM os_TICKET os, os_form_entry_values val, os_form_field FF 
where tkt.ticket_id = val.entry_id 
and val.field_id = FF.id 

输出

ticket_id   number  field_id    name        value   
5       592593         4        SSN         11111111111
5       592593         87       title       Mr
5       592593         117      dob         05/01/2011
5       592593         3        mobile      0091981123232

我想要这样的输出:

ticket_id  Number    SSN          Title   dob            mobile
5          592593    1111111111   Mr.    05/012011  009198182838485

Answer 1

如果名称总是有四个值，你可以尝试这个

select ticket_id , Number, 
max(case when Name='SSN' then value end) as SSN ,
max(case when Name='Title' then value end) as Title ,
max(case when Name='dob' then value end) as dob ,
max(case when Name='mobile' then value end) as mobile 
from
( 
SELECT tkt.ticket_id, tkt.`number`, val.field_id, FF.name,    val.value 
FROM os_TICKET os, os_form_entry_values val, os_form_field FF 
where tkt.ticket_id = val.entry_id 
and val.field_id = FF.id 
) as t
group by ticket_id , Number