环境描述:
假设有两张表一张是商品表(product),一张定单表(order)
商品表(product)结构如下:
proId proName
1 商品1
2 商品2
3 商品3
proId表示商品id, proName表示商品名称
定单表(order)结构如下:
id orderName isDeal proId
1 定单1 Y 1
2 定单2 N 2
3 定单3 N 2
4 定单4 Y 3
5 定单5 Y 1
id表示定单的id,orderName表示定单名称,isDeal表示是否已成交(Y表示已成交,N表示未成交),proId是商品id是一个外键。 DDL如下:
-- product
CREATE TABLE TEST . product
( proId DECIMAL(10, 0),
proName VARCHAR(50)
)
DATA CAPTURE NONE;
#SYNC 10;
ALTER TABLE TEST . product
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
#SYNC 20;
RUNSTATS ON TABLE TEST . product
AND INDEXES ALL
SHRLEVEL REFERENCE;
#SYNC 30;
-- order
CREATE TABLE TEST . order
( id DECIMAL(10, 0),
orderName VARCHAR(50),
isDeal VARCHAR(10),
proId DECIMAL(10, 0)
)
DATA CAPTURE NONE;
#SYNC 10;
ALTER TABLE TEST . order
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
#SYNC 20;
RUNSTATS ON TABLE TEST . order
AND INDEXES ALL
SHRLEVEL REFERENCE;
#SYNC 30;
--insert
insert into PRODUCT values(1,'商品1');
insert into PRODUCT values(2,'商品2');
insert into PRODUCT values(3,'商品3');
insert into ORDER values(1,'定单1','Y',1);
insert into ORDER values(2,'定单2','N',2);
insert into ORDER values(3,'定单3','N',3);
insert into ORDER values(4,'定单4','Y',2);
insert into ORDER values(5,'定单5','Y',1);
现在需要统计每种商品有多少个定单,每种商品的定单成交和未成交的数量。 解决方案:
select count(proId) as count,proName,sum(case when isDeal='Y' then 1 else 0 end) as isDeal,sum(case when isDeal='N' then 1 else 0 end) isNotDeal
from (
select pro.proName,ord.orderName,ord.isDeal,ord.proId from product pro,order ord where pro.proId=ord.proId
) as a1 where 1=1 group by proId,proName; 得到的结果为:
count proName isDeal isNotDeal