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

由于项目需要完成先从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;

< http://www.2cto.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KCjxwPs/Cw+bAtLfWsr3Rp8+w0rvPwqO6PC9wPgoKPHA+ytfPyL2rwb249rHtwazG8MC0PC9wPgoKPHA+PC9wPgo8cHJlIGNsYXNzPQ=="brush:sql;">SELECT * FROM supports s LEFT JOIN (products p) ON s.product_id = p.id

然后我们加入限定语句 WHERE project_id = 1就可一筛选出project_id为1的所有数据

然后用SUM（p.price）就可算出support中project_id为1的商品价格总和

SELECT SUM(p.price) as sum FROM supports  s  LEFT JOIN (me_products p) ON s.product_id =p.id  WHERE s.project_id =1

代码是从项目中直接摘取的

好多代码为了大家好理解去掉了一些不必要的干扰，有可能导致代码无法直接使用。

再次表示抱歉，主要还是做一下学习笔记，大家以理解为主。

参考文献：MySQL参考手册5.1

Zemo手打，转载标明出处：http://blog.csdn.net/zemochen/article/details/17190133