需要查詢的資料來源于兩張資料表：OrderList（訂單表）、ShopInfo（門店表）
訂單表涉及到的查詢欄位如下：
order_id(訂單號，主關鍵字)、order_status(訂單狀態)、order_date(下單時間)、shop_id(門店號)、money(訂單金額)。
門店表涉及到的查詢欄位如下：
shop_id（主關鍵字，訂單表的外鍵）、shop_sheng（門店所在省）、shop_shi（門店所在市）、shop_xian（門店所在縣）、shop_address（門店詳細地址）、shop_boss（門店負責人）、shop_phone（門店識訓手機號碼）
每個門店每天會有若干個訂單，總公司每天通過快遞或郵政的方式發貨到門店。當天訂單總金額高于指定金額（假如是100）的門店，走快遞發貨，低于指定金額的門店，走郵政發貨，對于偏遠地區（新疆、西藏、內蒙、青海），一律走郵政發貨。
發快遞的SQL很快寫好了：
SELECT a.shop_id, a.shop_boss, a.shop_phone, a.shop_sheng, shop_shi, shop_xian, a.shop_address, SUM(b.money) as sum_money,COUNT(b.order_id) as order_cnt FROM shopInfo a inner join OrderList b on a.shop_id = b.shop_id
WHERE b.order_status = 5 AND NOT a.shop_sheng IN ('新疆','西藏','內蒙','青海') AND order_date BETWEEN @date1 AND @date2
GROUP BY a.shop_id, a.shop_boss, a.shop_phone, a.shop_sheng, a.shop_shi, a.shop_xian, a.shop_address
HAVING SUM(b.money)>=100
能正確查詢到資料
但是，發郵政的SQL按下面寫出來后，查詢到的資料不對：
SELECT a.shop_id, a.shop_boss, a.shop_phone, a.shop_sheng, shop_shi, shop_xian, a.shop_address, SUM(b.money) as sum_money,COUNT(b.order_id) as order_cnt FROM shopInfo a inner join OrderList b on a.shop_id = b.shop_id
WHERE b.order_status = 5 AND order_date BETWEEN @date1 AND @date2 OR a.shop_sheng IN ('新疆','西藏','內蒙','青海')
GROUP BY a.shop_id, a.shop_boss, a.shop_phone, a.shop_sheng, a.shop_shi, a.shop_xian, a.shop_address
HAVING SUM(b.money)<100
分析發現，是WHERE條件里面的 OR 部分對識訓地區的限定不對，但這個條件該怎么寫呢？卡了好久，寫不出來，只好求助大神了。
uj5u.com熱心網友回復：
自己搞定了，把 OR部分寫到 HAVING子句里面
SELECT a.shop_id, a.shop_boss, a.shop_phone, a.shop_sheng, shop_shi, shop_xian, a.shop_address, SUM(b.money) as sum_money,COUNT(b.order_id) as order_cnt FROM shopInfo a inner join OrderList b on a.shop_id = b.shop_id
WHERE b.order_status = 5 AND order_date BETWEEN @date1 AND @date2
GROUP BY a.shop_id, a.shop_boss, a.shop_phone, a.shop_sheng, a.shop_shi, a.shop_xian, a.shop_address
HAVING SUM(b.money)<100 OR a.shop_sheng IN ('新疆','西藏','內蒙','青海')
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/49562.html
標籤：應用實例
上一篇：pscad變壓器空載仿真求助