想成为一个有逼格的资深程序员,写每一行代码时都应该去思考,sql也是,要想写出一条效率高的sql,就需要熟练掌握sql语句的执行顺序,流程、执行计划。
准备测试实验
创建table1、table2,插入测试数据,如下:
CREATE TABLE table1
(
user_id VARCHAR(10) NOT NULL,
city VARCHAR(10) NOT NULL,
PRIMARY KEY(user_id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
CREATE TABLE table2
(
order_id INT NOT NULL AUTO_INCREMENT,
user_id VARCHAR(10),
PRIMARY KEY(order_id)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;
INSERT INTO table1(user_id,city) VALUES('lisi','xian');
INSERT INTO table1(user_id,city) VALUES('zhangsan','xian');
INSERT INTO table1(user_id,city) VALUES('brayden','xian');
INSERT INTO table1(user_id,city) VALUES('red','xian');
INSERT INTO table2(user_id) VALUES('lisi');
INSERT INTO table2(user_id) VALUES('lisi');
INSERT INTO table2(user_id) VALUES('zhangsan');
INSERT INTO table2(user_id) VALUES('zhangsan');
INSERT INTO table2(user_id) VALUES('zhangsan');
INSERT INTO table2(user_id) VALUES('brayden');
INSERT INTO table2(user_id) VALUES(NULL);
测试sql,如下:
SELECT a.user_id, COUNT(b.order_id) AS total_orders
FROM table1 AS a
LEFT JOIN table2 AS b
ON a.user_id = b.user_id
WHERE a.city = 'xian'
GROUP BY a.user_id
HAVING COUNT(b.order_id) < 2
ORDER BY total_orders DESC;
SQ