假定表T,字段为ID,C1,C2 select sum(decode(C1,'A',1)),sum(decode(C1,'A',C2)) ,sum(decode(C1,'B',1)) ,sum(decode(C1,'B',C2)) FROM T
A价格是取的最大,最小还是平均值,还是全一样? 上面写的是按总和算的,有点问题,下面是按最大值的算法 select sum(decode(C1,'A',1)) AS A, max(decode(C1,'A',C2)) as A价格, sum(decode(C1,'B',1)) AS B, max(decode(C1,'B',C2)) AS B价格 FROM T
with t as ( select 1 id,'A' nm,200 price from dual union all select 2 id,'B' nm,300 price from dual union all select 3 id,'A' nm,200 price from dual union all select 4 id,'B' nm,300 price from dual union all select 5 id,'A' nm,200 price from dual union all select 6 id,'B' nm,400 price from dual union all select 7 id,'A' nm,200 price from dual ) SELECT * FROM (SELECT COUNT(ID) A,PRICE FROM T WHERE NM='A' GROUP BY NM,PRICE) T1, (SELECT COUNT(ID) A,PRICE FROM T WHERE NM='B' GROUP BY NM,PRICE) T2 ;
CASE WHEN 写法,各个数据库通用 with t as ( select 1 id,'A' nm,200 price from dual union all select 2 id,'B' nm,300 price from dual union all select 3 id,'A' nm,200 price from dual union all select 4 id,'B' nm,300 price from dual union all select 5 id,'A' nm,200 price from dual union all select 6 id,'B' nm,400 price from dual union all select 7 id,'A' nm,200 price from dual ) select sum(case when nm='A' THEN 1 else 0 end) a, MAX(case when nm='A' THEN price else 0 end) price_a, sum(case when nm='B' THEN 1 else 0 end) B, MAX(case when nm='B' THEN price else 0 end) price_b from t;
select sum(case when name='A' then 1 else 0 end) A, max(case when name='A' then score else '0' end) A的价格, sum(case when name='B' then 1 else 0 end) B, max(case when name='B' then score else '0' end) B的价格 from ABtest 参考版主写的
select sum(decode(C1,'A',1)),sum(decode(C1,'A',C2)) ,sum(decode(C1,'B',1)) ,sum(decode(C1,'B',C2))
FROM T
上面写的是按总和算的,有点问题,下面是按最大值的算法
select sum(decode(C1,'A',1)) AS A,
max(decode(C1,'A',C2)) as A价格,
sum(decode(C1,'B',1)) AS B,
max(decode(C1,'B',C2)) AS B价格
FROM T
with t as
(
select 1 id,'A' nm,200 price from dual union all
select 2 id,'B' nm,300 price from dual union all
select 3 id,'A' nm,200 price from dual union all
select 4 id,'B' nm,300 price from dual union all
select 5 id,'A' nm,200 price from dual union all
select 6 id,'B' nm,400 price from dual union all
select 7 id,'A' nm,200 price from dual
)
SELECT * FROM
(SELECT COUNT(ID) A,PRICE FROM T WHERE NM='A' GROUP BY NM,PRICE) T1,
(SELECT COUNT(ID) A,PRICE FROM T WHERE NM='B' GROUP BY NM,PRICE) T2
;
with t as
(
select 1 id,'A' nm,200 price from dual union all
select 2 id,'B' nm,300 price from dual union all
select 3 id,'A' nm,200 price from dual union all
select 4 id,'B' nm,300 price from dual union all
select 5 id,'A' nm,200 price from dual union all
select 6 id,'B' nm,400 price from dual union all
select 7 id,'A' nm,200 price from dual
)
select sum(case when nm='A' THEN 1 else 0 end) a,
MAX(case when nm='A' THEN price else 0 end) price_a,
sum(case when nm='B' THEN 1 else 0 end) B,
MAX(case when nm='B' THEN price else 0 end) price_b from t;
sum(case when name='A' then 1 else 0 end) A,
max(case when name='A' then score else '0' end) A的价格,
sum(case when name='B' then 1 else 0 end) B,
max(case when name='B' then score else '0' end) B的价格
from ABtest
参考版主写的