我有两张table,一张是零售商的table,另一张是参观主桌。在retailer表中存储所有商店,在visit master中提到用户访问过的商店。所以我想把他最近十天都没去过的那些零售商的店拿出来,不管他们的店是不是都在两张table上,把他去过的那些店拿出来,还有一些他到现在都没去过的店,我们从零售商的table上拿出来。
created date列来自retailer表,visit\u datetime列来自visit master表。
SELECT tbl_retailer.sr_id,
tbl_retailer.retailer_shop_name,
tbl_retailer.retailer_id,
tbl_retailer.tsm_id,
tbl_retailer.created_date,
max(tbl_visit_master.visit_DateTime) as last_visit_date,
max(tbl_feedback.visit_date) as feedback_given_date,
max(tbl_stock.taken_date) as stock_date,
max(tbl_promise_order.promise_date) as order_date,
max(tbl_payment_collection.payment_date) as payment_date
from tbl_retailer
left join tbl_member on tbl_member.employee_id=tbl_retailer.sr_id
left join tbl_feedback on tbl_retailer.retailer_id=tbl_feedback.retailer_id
left join tbl_stock on tbl_retailer.retailer_id=tbl_stock.retailer_id
left join tbl_promise_order on tbl_retailer.retailer_id=tbl_promise_order.retailer_id
left join tbl_payment_collection on tbl_retailer.retailer_id=tbl_payment_collection.retailer_id
left join tbl_visit_master on tbl_retailer.retailer_id=tbl_visit_master.retailer_id
where tbl_retailer.sr_id=189
group by retailer_id
having max(tbl_visit_master.visit_DateTime) and tbl_retailer.created_date <= CURDATE() - INTERVAL 10 DAY;
暂无答案!
目前还没有任何答案,快来回答吧!