select a.transdate, -- 交易时间 c.operatorname,-- 操作员 d.goodsname, -- 商品名称 b.quantity, -- 销售数量 b.price, -- 价格 b.salesvalue -- 销售金额 from demo.transactionhead as a join demo.transactiondetails as b on (a.transactionid = b.transactionid) join demo.operator as c on (a.operatorid = c.operatorid) join demo.goodsmaster as d on (b.itemnumber = d.itemnumber);
查看每天的销售数量和销售金额,按照一个字段transdate对数据进行分组和统计
1 2 3 4 5 6 7 8 9
select a.transdate sum(b.quantity), sum(b.salesvalue) from demo.transactionhead as a join demo.transactiondetails as b on (b.transactionid = b.transactionid) group by a.transdate;
查看每天每个收营员的销售数量和金额,按照transdate和operatorname分组和统计
1 2 3 4 5 6 7 8 9 10 11 12
select a.transdate, c.opeatorname, sum(b.quantity), sum(b.salesvalue) from demo.transactionhead as a join demo.transactiondetails as b on (a.transactionid = b.transactionid) join demo.operator as c on (a.operatorid = c.operatorid) group by a.transdate,c.operatorname;
使用having,查询单笔金额超过50元的商品。
1 2 3 4 5 6
select b.goodsname from demo.transactiondetails as a join demo.goodsmaster as b on (a.itemnumber = b.itemnumber) group by b.goodsname having max(a.salesvalue) > 50;
第一步:将流水明细表和商品信息表通过公共字段itemnumber连接起来
1 2 3 4 5
select a.* , b.* from demo.transactiondetails a join demo.goodsmaster b on (a.itemnumber = b.itemnumber);
select a.transdate,c.operatorname from demo.transactionhead as join demo.transactiondetails as b on (a.transactionid = b.transactionid) join demo.operator as c on (a.operatorid = c.operatorid) group by a.transdate,c.operatorname having count(*)=2;
where和having的各自有缺点
where和having并不互相排斥,我们可以在一个查询内同时使用。
1 2 3 4 5 6 7 8 9 10 11 12 13 14
select a.transdate, c.operatorname, d.goodsname, b.price, b.salesvalue from demo.transactionhead as a join demo.transactiondetails as b on (a.transactionid = b.transactionid) join demo.operator as c on (a.operatorid = c.operatorid) join demo.goodsmaster as d on (b.itemnumber = d.itemnumber);
select a.transdate, c.operatorname, sum(b.quantity), sum(b.salesvalue) from demo.transactionhead as a join demo.transactiondetails as b on (a.transactionid = b.transactionid) join demo.operator as c on (a.operatorid = c.operatorid) group by a.transdate,c.operatorname haing a.transdate in ('2020-12-10','2020-12-11') and sum(b.salesvalue) > 100;
a.transdate in ('2020-12-10','2020-12-11')可以用where来限定
1 2 3 4 5 6 7 8 9 10 11 12 13 14
select a.transdate, c.operatorname, sum(b.quantity), sum(b.salesvalue) from demo.transactionhead as a join demo.transactiondetails as b on (a.transactionid = b.transactionid) join demo.operator as c on (a.operatorid = c.operatorid) where a.transdate in ('2020-12-12','2020-12-11') group by a.transdate,c.opertorname having sum(b.salesvalue) > 100;