select A,B
from (select ID,A,B,Type from table) aselect ID,A,B,Type from table -- 有二条记录
ID,A,B,Type
1 1 1 1
2 2 2 2
select (CASE WHEN type='1' then A END) A1,(CASE WHEN type='1' then A END) B1,
(CASE WHEN type='1' then A END) A2,(CASE WHEN type='1' then B END) B2
from (select ID,A,B,Type from table) a
--结果
1 1 0 0
0 0 2 2
--想要的结果
1 1 2 2
(CASE WHEN type='2' then A END) A2,(CASE WHEN type='2' then B END) B2
max(CASE WHEN type='1' then A END) B1,
max(CASE WHEN type='1' then A END) A2,
max(CASE WHEN type='1' then B END) B2
from (select ID,A,B,Type from table) a
with tbl as (
select (CASE WHEN type='1' then A END) A1,(CASE WHEN type='1' then A END) B1
,(CASE WHEN type='2' then A END) A2,(CASE WHEN type='2' then B END) B2
from (select ID,A,B,Type from table ) a
)
select MAX(A1),MAX(B1),MAX(A2),MAX(B2) from tbl
select min((CASE WHEN type='1' then A END)) A1,min((CASE WHEN type='1' then A END)) B1,
max((CASE WHEN type='1' then A END)) A2,max((CASE WHEN type='1' then B END)) B2
from (select ID,A,B,Type from table) a
--前两个最小,后两个最大。
max(CASE WHEN type='1' then A END) B1,
max(CASE WHEN type='1' then A END) A2,
max(CASE WHEN type='1' then B END) B2
from (select ID,A,B,Type from table) a
select max(CASE WHEN type='1' then A END) A1,
max(CASE WHEN type='1' then B END) B1,
max(CASE WHEN type='2' then A END) A2,
max(CASE WHEN type='2' then B END) B2
from (select ID,A,B,Type from table) a