Order form involves the query fields is as follows:
Order_id (order number, the primary key), order_status (order status), order_date, order time, shop_id (stores), money (orders),
Stores table involves the query fields are as follows:
Shop_id (primary key, the order of the foreign key), shop_sheng (province) store, shop_shi (stores in city), shop_xian county (stores), shop_address detailed address (stores), shop_boss head (stores), shop_phone (stores receiving phone number)
Each store will have a number of orders every day, head office by Courier or postal delivery to store every day, on the day of the order total amount is higher than the specified amount (in this case, 100) stores, express delivery, lower than the specified amount of stores, go postal delivery, for remote areas (xinjiang, Tibet, Inner Mongolia, qinghai), shall go postal delivery,
Send express SQL soon wrote:
The SELECT a.s hop_id, a.s hop_boss, a.s hop_phone, a.s hop_sheng, shop_shi, shop_xian, a.s hop_address, SUM (b.m oney) as sum_money, COUNT (b.o rder_id) as order_cnt FROM shopInfo a inner join OrderList b on a.s hop_id=b.s hop_id
WHERE b.o rder_status=5 AND NOT a.s hop_sheng IN (' xinjiang, Tibet, Inner Mongolia, qinghai ') AND order_date BETWEEN @ date1 AND @ date2
GROUP BY a.s hop_id, a.s hop_boss, a.s hop_phone, a.s hop_sheng, a.s hop_shi, a.s hop_xian, a.s hop_address
HAVING the SUM (b.m oney) & gt;=100
Query to the data correctly
But, hair post according to the following written SQL, the query to the wrong data:
The SELECT a.s hop_id, a.s hop_boss, a.s hop_phone, a.s hop_sheng, shop_shi, shop_xian, a.s hop_address, SUM (b.m oney) as sum_money, COUNT (b.o rder_id) as order_cnt FROM shopInfo a inner join OrderList b on a.s hop_id=b.s hop_id
WHERE b.o rder_status=5 AND order_date BETWEEN @ date1 AND @ date2 OR a.s hop_sheng IN (' xinjiang ', 'Tibet', 'Inner Mongolia', 'qinghai')
GROUP BY a.s hop_id, a.s hop_boss, a.s hop_phone, a.s hop_sheng, a.s hop_shi, a.s hop_xian, a.s hop_address
HAVING the SUM (b.m oney) & lt; 100
Analysis found that the part is the WHERE condition inside the OR limit on a receiving area is wrong, but this condition how to write? Card for a long time, write not to come out, had to turn to the great god,
CodePudding user response:
Yourself, write part OR HAVING clause inside
The SELECT a.s hop_id, a.s hop_boss, a.s hop_phone, a.s hop_sheng, shop_shi, shop_xian, a.s hop_address, SUM (b.m oney) as sum_money, COUNT (b.o rder_id) as order_cnt FROM shopInfo a inner join OrderList b on a.s hop_id=b.s hop_id
WHERE b.o rder_status=5 AND order_date BETWEEN @ date1 AND @ date2
GROUP BY a.s hop_id, a.s hop_boss, a.s hop_phone, a.s hop_sheng, a.s hop_shi, a.s hop_xian, a.s hop_address
HAVING the SUM (b.m oney) & lt; 100 the OR a.s hop_sheng (IN xinjiang, Tibet, Inner Mongolia, qinghai ' ')