Max+Decode的妙用

Max+Decode的妙用.(记录分组).

条件
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

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/Bogues/archive/2004/10/26/152708.aspx