SQL语句-- LEFT JOIN 关联表查询

浏览： ℃

字体：大中小

发布时间：2013-12-09 23:23:16

来源：

由于项目需要完成先从support支持表根据project id 查处用户购买的商品号再到product商品表中算出总价格

想了好多办法，最终决定用 LEFT JOIN先把两个表链接成一个表，然后查询的方法

在此，分步讲解一下

表1：product表

CREATE TABLE `products` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(50) NOT NULL COMMENT '商品名称',  `code` varchar(20) NOT NULL COMMENT '商品编号',  `price` int(11) NOT NULL COMMENT '商品价格',  `project_id` int(11) DEFAULT NULL COMMENT '项目',  PRIMARY KEY (`id`),  UNIQUE KEY `id_UNIQUE` (`id`,`name`,`code`),  KEY `idx_products_project_id` (`project_id`),  CONSTRAINT `fk_products_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE NO ACTION ON UPDATE SET NULL) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

表2：support表

CREATE TABLE `supports` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `project_id` int(11) NOT NULL COMMENT '项目',  `user_id` int(11) NOT NULL COMMENT '支持人',  `product_id` int(11) NOT NULL COMMENT '商品',  PRIMARY KEY (`id`,`project_id`),  KEY `idx_project_id` (`product_id`),  KEY `fk_project_suports_project_id` (`project_id`),  KEY `fk_project_suports_user_id` (`user_id`),  CONSTRAINT `fk_project_suports_product_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),  CONSTRAINT `fk_project_suports_project_id` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`),  CONSTRAINT `fk_project_suports_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;