##查詢到店會(huì)員人數(shù)
SELECT '0-7天' AS day, COUNT(*) AS count FROM (
SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成') AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id ) AS B UNION ALL
SELECT '7-15天' AS day, COUNT(*) AS count FROM (
SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成') AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id ) AS C UNION ALL
SELECT '15-30天' AS day, COUNT(*) AS count FROM (
SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成') AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id ) AS D UNION ALL
SELECT '30-60天' AS day, COUNT(*) AS count FROM (
SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成') AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id ) AS E UNION ALL
SELECT '60天以上' AS day, COUNT(*) AS count FROM (
SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成') AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id ) AS D;
##查詢到店游客人數(shù)
SELECT '0-7天' AS day, COUNT(*) AS count FROM (
SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成') AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM ( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id ) AS B UNION ALL
SELECT '7-15天' AS day, COUNT(*) AS count FROM (
SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成') AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM ( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id ) AS B UNION ALL
SELECT '15-30天' AS day, COUNT(*) AS count FROM (
SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成') AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM ( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id ) AS B UNION ALL
SELECT '30-60天' AS day, COUNT(*) AS count FROM (
SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成') AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM ( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id ) AS B UNION ALL
SELECT '60天以上' AS day, COUNT(*) AS count FROM (
SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成') AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM ( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id ) AS B
##會(huì)員人數(shù)占比
SELECT day,CONCAT(count,'%') as bili FROM(
SELECT '0-7天' AS day, ROUND(COUNT(*)/(SELECT COUNT(*) FROM (
SELECT COUNT(*)
FROM mmc_order AS ord,mmc_member as mb
WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT
cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是'
GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A)*100,2) AS count
FROM ( SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成')
AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A ) GROUP BY ord.mb_id ) AS B ) as A UNION ALL
SELECT day,CONCAT(count,'%') as bili FROM(
SELECT '7-15天' AS day, ROUND(COUNT(*)/(SELECT COUNT(*) FROM (
SELECT COUNT(*) FROM mmc_order AS ord,mmc_member as mb
WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id
FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id ) GROUP BY ord.mb_id) as A)*100,2) AS count
FROM ( SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成')
AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A ) GROUP BY ord.mb_id ) AS B ) as A UNION ALL
SELECT day,CONCAT(count,'%') as bili FROM(
SELECT '15-30天' AS day, ROUND(COUNT(*)/(SELECT COUNT(*) FROM (
SELECT COUNT(*) FROM mmc_order AS ord,mmc_member as mb
WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN (
SELECT cd.mb_id
FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A)*100,2) AS count
FROM ( SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成')
AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A ) GROUP BY ord.mb_id ) AS B ) as A UNION ALL
SELECT day,CONCAT(count,'%') as bili FROM(
SELECT '30-60天' AS day, ROUND(COUNT(*)/(SELECT COUNT(*) FROM (
SELECT COUNT(*) FROM mmc_order AS ord,mmc_member as mb
WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id
)
GROUP BY ord.mb_id) as A)*100,2) AS count
FROM ( SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成')
AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A ) GROUP BY ord.mb_id ) AS B ) as A UNION ALL
SELECT day,CONCAT(count,'%') as bili FROM(
SELECT '60天以上' AS day, ROUND(COUNT(*)/(SELECT COUNT(*) FROM (
SELECT COUNT(*)
FROM mmc_order AS ord,mmc_member as mb
WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN (
SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id
)
GROUP BY ord.mb_id) as A)*100,2) AS count
FROM ( SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成')
AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A ) GROUP BY ord.mb_id ) AS B ) as A
##游客人數(shù)占比
SELECT day,CONCAT(count,'%') as bili FROM(
SELECT '0-7天' AS day, ROUND(COUNT(*)/(SELECT COUNT(*) FROM (
SELECT COUNT(*)
FROM mmc_order AS ord,mmc_member as mb
WHERE
ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN (
SELECT cd.mb_id
FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 37 AND cd.state = '是'
GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A)*100,2) AS count
FROM ( SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-08-25'
AND '2017-08-25' AND ord.st_id = 37 AND ord.ord_state in ('待評(píng)價(jià)','已完成')
AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 35 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT mb_id FROM ( SELECT ord.mb_id
FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37
AND cd.state = '是' GROUP BY mb.mb_id) GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id) as A ) GROUP BY ord.mb_id ) AS B ) as A UNION ALL
SELECT day,CONCAT(count,'%') as bili FROM(
SELECT '7-15天' AS day, ROUND(COUNT(*)/(SELECT COUNT(*) FROM (
SELECT COUNT(*) FROM mmc_order AS ord,mmc_member as mb
WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id
FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A)*100,2) AS count
FROM ( SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成')
AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 35 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT mb_id FROM ( SELECT ord.mb_id
FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 35
AND cd.state = '是' GROUP BY mb.mb_id) GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id) as A ) GROUP BY ord.mb_id ) AS B ) as A UNION ALL
SELECT day,CONCAT(count,'%') as bili FROM(
SELECT '15-30天' AS day, ROUND(COUNT(*)/(SELECT COUNT(*) FROM (
SELECT COUNT(*)
FROM mmc_order AS ord,mmc_member as mb
WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id
FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A)*100,2) AS count
FROM ( SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成')
AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 35 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT mb_id FROM ( SELECT ord.mb_id
FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 35
AND cd.state = '是' GROUP BY mb.mb_id) GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id) as A ) GROUP BY ord.mb_id ) AS B ) as A UNION ALL
SELECT day,CONCAT(count,'%') as bili FROM(
SELECT '30-60天' AS day, ROUND(COUNT(*)/(SELECT COUNT(*) FROM (
SELECT COUNT(*)
FROM mmc_order AS ord,mmc_member as mb
WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN (
SELECT cd.mb_id
FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A)*100,2) AS count
FROM ( SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成')
AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 35 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT mb_id FROM ( SELECT ord.mb_id
FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 35
AND cd.state = '是' GROUP BY mb.mb_id) GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id) as A ) GROUP BY ord.mb_id ) AS B ) as A UNION ALL
SELECT day,CONCAT(count,'%') as bili FROM(
SELECT '60天以上' AS day, ROUND(COUNT(*)/(SELECT COUNT(*) FROM (
SELECT COUNT(*) FROM mmc_order AS ord,mmc_member as mb
WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN (
SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id )
GROUP BY ord.mb_id) as A)*100,2) AS count
FROM ( SELECT COUNT(mb_id) FROM mmc_order AS ord WHERE substring(ord.ord_create_time, 1, 10) BETWEEN '2017-09-11'
AND '2017-09-11' AND ord.st_id = 35 AND ord.ord_state in ('待評(píng)價(jià)','已完成')
AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 35 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT mb_id FROM ( SELECT ord.mb_id
FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 35
AND cd.state = '是' GROUP BY mb.mb_id) GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id) as A ) GROUP BY ord.mb_id ) AS B ) as A ;
##會(huì)員消費(fèi)總額
SELECT '0-7天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A ) UNION ALL
SELECT '7-15天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A ) UNION ALL
SELECT '15-30天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A ) UNION ALL
SELECT '30-60天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A ) UNION ALL
SELECT '60天以上' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A ) ;
##游客消費(fèi)總額
SELECT '0-7天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM
( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 35 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A ) UNION ALL
SELECT '7-15天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM
( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 35 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A ) UNION ALL
SELECT '15-30天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM
( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 35 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A ) UNION ALL
SELECT '30-60天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM
( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 35 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A ) UNION ALL
SELECT '60天以上' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM
( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 35 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A );
#會(huì)員消費(fèi)占比
SELECT day,CONCAT(ROUND(bl*100,2),'%') as bili FROM (
SELECT day,money/(SELECT ROUND(IFNULL(SUM(ord.ord_pay_money),0),2) as total FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')) as bl FROM (
SELECT '0-7天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM
( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 35 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A )) as A) as B UNION ALL
SELECT day,CONCAT(ROUND(bl*100,2),'%') as bili FROM (
SELECT day,money/(SELECT ROUND(IFNULL(SUM(ord.ord_pay_money),0),2) as total FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')) as bl FROM (
SELECT '7-15天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM
( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 35 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A )) as A) as B UNION ALL
SELECT day,CONCAT(ROUND(bl*100,2),'%') as bili FROM (
SELECT day,money/(SELECT ROUND(IFNULL(SUM(ord.ord_pay_money),0),2) as total FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')) as bl FROM (
SELECT '15-30天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM
( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 35 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A )) as A) as B UNION ALL
SELECT day,CONCAT(ROUND(bl*100,2),'%') as bili FROM (
SELECT day,money/(SELECT ROUND(IFNULL(SUM(ord.ord_pay_money),0),2) as total FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')) as bl FROM (
SELECT '30-60天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM
( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 35 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A )) as A) as B UNION ALL
SELECT day,CONCAT(ROUND(bl*100,2),'%') as bili FROM (
SELECT day,money/(SELECT ROUND(IFNULL(SUM(ord.ord_pay_money),0),2) as total FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')) as bl FROM (
SELECT '60天以上' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT A.mb_id FROM
( SELECT mb.mb_id FROM mmc_member_card AS cd, mmc_member AS mb
WHERE mb.mb_id = cd.mb_id AND cd.card_type = '會(huì)員充值卡'
AND cd.st_id = 35 AND cd.state = '是' GROUP BY mb.mb_id ) AS A)
GROUP BY ord.mb_id) as A )) as A) as B ;
##游客消費(fèi)占比
SELECT day,CONCAT(ROUND(bl*100,2),'%') as bili FROM (
SELECT day,money/(SELECT ROUND(IFNULL(SUM(ord.ord_pay_money),0),2) as total FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')) as bl FROM (
SELECT '0-7天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN (SELECT mb_id FROM ( SELECT ord.mb_id
FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id
FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id) as A )) as A) as B UNION ALL
SELECT day,CONCAT(ROUND(bl*100,2),'%') as bili FROM (
SELECT day,money/(SELECT ROUND(IFNULL(SUM(ord.ord_pay_money),0),2) as total FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')) as bl FROM (
SELECT '7-15天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN (SELECT mb_id FROM ( SELECT ord.mb_id
FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id
FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id) as A )) as A) as B UNION ALL
SELECT day,CONCAT(ROUND(bl*100,2),'%') as bili FROM (
SELECT day,money/(SELECT ROUND(IFNULL(SUM(ord.ord_pay_money),0),2) as total FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')) as bl FROM (
SELECT '15-30天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN (SELECT mb_id FROM ( SELECT ord.mb_id
FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id
FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id) as A )) as A) as B UNION ALL
SELECT day,CONCAT(ROUND(bl*100,2),'%') as bili FROM (
SELECT day,money/(SELECT ROUND(IFNULL(SUM(ord.ord_pay_money),0),2) as total FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')) as bl FROM (
SELECT '30-60天' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN (SELECT mb_id FROM ( SELECT ord.mb_id
FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id
FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id) as A )) as A) as B UNION ALL
SELECT day,CONCAT(ROUND(bl*100,2),'%') as bili FROM (
SELECT day,money/(SELECT ROUND(IFNULL(SUM(ord.ord_pay_money),0),2) as total FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')) as bl FROM (
SELECT '60天以上' as day, ROUND(IFNULL(SUM(ord_pay_money),0),2) as money FROM mmc_order as ord where ord.ord_state in ('已完成','待評(píng)價(jià)') AND ord.ord_type in ('門店下單','購買套餐')
AND substring(ord.ord_create_time,1,10) BETWEEN '2017-09-11' AND '2017-09-11' AND ord.st_id=35 AND ord.mb_id NOT in (SELECT mb_id FROM (
SELECT ord.mb_id FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN (SELECT mb_id FROM ( SELECT ord.mb_id
FROM mmc_order AS ord,mmc_member as mb WHERE ord.st_id = 37 AND mb.mb_id=ord.mb_id
AND ord.mb_id NOT IN ( SELECT cd.mb_id
FROM mmc_member_card AS cd, mmc_member AS mb WHERE mb.mb_id = cd.mb_id
AND cd.card_type = '會(huì)員充值卡' AND cd.st_id = 37 AND cd.state = '是' GROUP BY mb.mb_id)
GROUP BY ord.mb_id) as A) GROUP BY ord.mb_id) as A )) as A) as B ;
SQL稿
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。
相關(guān)閱讀更多精彩內(nèi)容
- SQLiteDatabase這個(gè)類，大家都不陌生。其中： [int delete(String table, St...
- 什么是我的大學(xué) 感謝各位，我的年總結(jié)，是一個(gè)問題的答案，這個(gè)問題是：什么是我的大學(xué)？ 去年高考后，我問了自己一個(gè)問...
- 環(huán)境：Windows下，已有VC++6.0的編譯器。 1.新建c:\esqlc目錄。 將SQL SEVER安裝盤上...
- 在實(shí)驗(yàn)室處理數(shù)據(jù)時(shí)，遇到一張20w記錄的表需要恢復(fù)，初以為直接在dbms中直接導(dǎo)入sql即可，然而顯示內(nèi)存不夠，無...
- 很多人對(duì)策劃的理解就是“文案策劃”，寫文字的，于是一些沒有追求的偽文藝青年以為這工作是安分守己呆在辦公位上每天寫好...
