1.华北大区的 微信交易的总比数
SELECT COUNT(*)
FROM itcast_payments
JOIN itcast_orders ON itcast_orders.payType=itcast_payments.id
JOIN itcast_order_goods ON itcast_orders.orderId=itcast_order_goods.orderId
JOIN itcast_goods ON itcast_order_goods.goodsid=itcast_goods.goodsid
JOIN itcast_shops ON itcast_goods.shopid=itcast_shops.shopid
JOIN itcast_org ON itcast_shops.areaid=itcast_org.orgid
WHERE
itcast_orders.payType=(SELECT id FROM itcast_payments WHERE itcast_payments.payname=‘微信支付’) AND
itcast_shops.areaid IN (SELECT orgid FROM itcast_org WHERE itcast_org.orgName =‘华北大区’ OR parentID =‘100006’)
2.石家庄市分公司 微信交易的总比数
SELECT COUNT(*)
FROM itcast_payments
JOIN itcast_orders ON itcast_orders.payType=itcast_payments.id
JOIN itcast_order_goods ON itcast_orders.orderId=itcast_order_goods.orderId
JOIN itcast_goods ON itcast_order_goods.goodsid=itcast_goods.goodsid
JOIN itcast_shops ON itcast_goods.shopid=itcast_shops.shopid
JOIN itcast_org ON itcast_shops.areaid=itcast_org.orgid
WHERE
itcast_orders.payType=(SELECT id FROM itcast_payments WHERE itcast_payments.payname=‘微信支付’) AND
itcast_shops.areaid IN (SELECT orgid FROM itcast_org WHERE itcast_org.orgName =‘石家庄市分公司’ )
题目
创建数据库
create database itcast_czxy;
使用数据库
use itcast_czxy;
数据量太大了
123.txt