Max+Decode的妙用.(紀錄分組).

转自：http://blog.csdn.net/bogues/article/details/152708

==========================================
條件:
TableName: material

Field Name : serial_number,group_name,lot_id

Date Example:
1    10001,  MB_MD,             MBID000001
2    10001,  TVE_MD,            TVEID000001
3    10001,  MODEM_MD,       MODEMID000001
4    10002,  MB_MD,             MBID000002
5    10002,  TVE_MD,            TVEID000002
6    10002,  MODEM_MD,       MODEMID000002
7    10003,  MB_MD,             MBID000003
8    10003,  TVE_MD,            TVEID000003
9    10003,  MODEM_MD,       MODEMID000003
==========================================
SQL:
select  serial_number ,max(decode(group_name,'MB_MD',lot_id,'')) MB_MD,

           max(decode(group_name,'TVE_MD',lot_id,'')) TVE_MD,

           max(decode(group_name,'MODEM_MD',lot_id,'')) MODEM_MD

from material

group by serial_number
==========================================
結果:
serial_number       MB_MD           TVE_MD           MODEM_MD
10001,             MBID000001,   TVEID000001,  MODEMID000001
10002,             MBID000002,   TVEID000002,  MODEMID000002
10003,             MBID000003,   TVEID000003,  MODEMID000003