SQL 实例 - LEFT JOIN 关键字

 

 

 1 Date: 2020-08-24 16:07:35
 2 */
 3 
 4 SET FOREIGN_KEY_CHECKS=0;
 5 
 6 -- ----------------------------
 7 -- Table structure for websites
 8 -- ----------------------------
 9 DROP TABLE IF EXISTS `websites`;
10 CREATE TABLE `websites` (
11   `id` int(255) NOT NULL AUTO_INCREMENT,
12   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '网名',
13   `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '网址',
14   `alexa` int(255) DEFAULT NULL COMMENT '排名',
15   `country` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '城市',
16   PRIMARY KEY (`id`)
17 ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
18 
19 -- ----------------------------
20 -- Records of websites
21 -- ----------------------------
22 INSERT INTO `websites` VALUES ('1', 'Google', 'https://www.google.cm/', '1', 'USA');
23 INSERT INTO `websites` VALUES ('2', '淘宝', 'https://www.taobao.com/', '13', 'CN');
24 INSERT INTO `websites` VALUES ('3', '菜鸟教程', 'http://www.runoob.com/', '4689', 'CN');
25 INSERT INTO `websites` VALUES ('4', '微博 ', 'http://weibo.com/', '20', 'CN');
26 INSERT INTO `websites` VALUES ('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA');
27 INSERT INTO `websites` VALUES ('7', 'stackoverflow', 'http://stackoverflow.com/', '0', 'IND');

 

 

 

 

 1 Date: 2020-08-24 15:58:02
 2 */
 3 
 4 SET FOREIGN_KEY_CHECKS=0;
 5 
 6 -- ----------------------------
 7 -- Table structure for access_log
 8 -- ----------------------------
 9 DROP TABLE IF EXISTS `access_log`;
10 CREATE TABLE `access_log` (
11   `aid` varchar(255) NOT NULL,
12   `site_id` int(255) DEFAULT NULL,
13   `count` int(255) DEFAULT NULL,
14   `date` date DEFAULT NULL,
15   PRIMARY KEY (`aid`)
16 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
17 
18 -- ----------------------------
19 -- Records of access_log
20 -- ----------------------------
21 INSERT INTO `access_log` VALUES ('1', '1', '45', '2016-05-10');
22 INSERT INTO `access_log` VALUES ('2', '3', '100', '2016-05-13');
23 INSERT INTO `access_log` VALUES ('3', '1', '230', '2016-05-14');
24 INSERT INTO `access_log` VALUES ('4', '2', '10', '2016-05-14');
25 INSERT INTO `access_log` VALUES ('5', '5', '205', '2016-05-14');
26 INSERT INTO `access_log` VALUES ('6', '4', '13', '2016-05-15');
27 INSERT INTO `access_log` VALUES ('7', '3', '220', '2016-05-15');
28 INSERT INTO `access_log` VALUES ('8', '5', '545', '2016-05-16');
29 INSERT INTO `access_log` VALUES ('9', '3', '201', '2016-05-17');

 

 

 

 

1 SELECT
2     w.`name`,
3     al.count,
4     al.date
5 FROM
6     websites AS w
7 LEFT JOIN access_log AS al ON w.id = al.site_id
8 ORDER BY
9     al.count DESC;

 

 

 

By:LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

 原文链接转自：https://www.runoob.com/sql/sql-join-left.html

 


                  故屿γ                   

 

 

posted @ 2020-08-26 13:38  故屿γ  阅读(303)  评论(0)  编辑  收藏  举报