使用sql语句的MyTABLE字段布局

Question

是否有如下格式的sql语句结果。。。。。。。。

TABLE1  
ID ticket_number  
1   2001  
2   2002  
Table2  
tn    name                 create_time  
2001  sms_to_customer      2013-05-05 10:10:19  
2001  sms_to_officer1      2013-05-05 11:17:01  
2001  sms_to_officer2      2013-05-05 12:14:05  
2002  sms_to_officer1      2013-05-05 09:17:01  
2002  sms_to_officer2      2013-05-05 09:30:05  

我想要这样的结果。。。。。。。。

ticket_number   sms_to_customer      sms_to_officer1        sms_to_officer2  
2001            2013-05-05 10:10:19  2013-05-05 11:17:01    2013-05-05 12:14:05  
2002            NA                   2013-05-05 09:17:01    2013-05-05 09:30:05 

Answer 1

如果这些只是Table2上列name的值，则可以使用此静态版本的查询

SELECT  tn,
        COALESCE(MAX(CASE WHEN Name = 'sms_to_customer' THEN Create_Time END), 'NA') `sms_to_customer`,
        COALESCE(MAX(CASE WHEN Name = 'sms_to_officer1' THEN Create_Time END), 'NA') `sms_to_officer1`,
        COALESCE(MAX(CASE WHEN Name = 'sms_to_officer2' THEN Create_Time END), 'NA') `sms_to_officer2`
FROM    Table2
GROUP   BY tn

• SQLFiddle演示

否则，如果您的值不是，请使用动态SQL

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COALESCE(MAX(case when Name = ''',
      Name,
      ''' then create_time end), ''NA'') AS ',
      CONCAT('`',Name,'`')
    )
  ) INTO @sql
FROM Table2;
SET @sql = CONCAT('SELECT  tn, ', @sql, ' 
                   FROM Table2
                   GROUP BY tn');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

• SQLFiddle演示

输出

╔══════╦═════════════════════╦═════════════════════╦═════════════════════╗
║  TN  ║   SMS_TO_CUSTOMER   ║   SMS_TO_OFFICER1   ║   SMS_TO_OFFICER2   ║
╠══════╬═════════════════════╬═════════════════════╬═════════════════════╣
║ 2001 ║ 2013-05-05 10:10:19 ║ 2013-05-05 11:17:01 ║ 2013-05-05 12:14:05 ║
║ 2002 ║ NA                  ║ 2013-05-05 09:17:01 ║ 2013-05-05 09:30:05 ║
╚══════╩═════════════════════╩═════════════════════╩═════════════════════╝

Answer 2

SELECT
   ticket_number, t2.create_time as `sms_to_customer`,t3.create_time as `sms_to_officer1`,t4.create_time as `sms_to_officer2` 
  from Table1 t1 
    left join Table2 t2 on (t1.ticket_number=t2.tn and t2.name='sms_to_customer') 
    left join Table2 t3 on (t1.ticket_number=t3.tn and t3.name='sms_to_officer1') 
    left join Table2 t4 on (t1.ticket_number=t4.tn and t4.name='sms_to_officer2');

• SQLFiddle演示