工作笔记——三表查询
来源:互联网 发布:c 编写unity3d脚本 编辑:程序博客网 时间:2024/06/05 00:46
最近在做ecshop二次开发,在给商品增加一个字段排序的时候用到三表查询更新。
(1)在SQLyog Community测试时候的SQL语句:
UPDATE `ecs_goods` AS d SET d.salesnum=
(SELECT c.salesnum
FROM
(SELECT a.goods_id,a.goods_name,b.salesnum
FROM
(SELECT goods_id, goods_name, goods_type, goods_sn, shop_price, is_on_sale, is_best, is_new, is_hot, sort_order,recommend_status,
recommend_point,themeid,marcket_lprice,shop_lprice, goods_number, integral,
(promote_price > 0 AND promote_start_date <= '1353686399' AND promote_end_date >= '1353686399') AS is_promote
FROM `eshop`.`ecs_goods` AS g WHERE is_delete='0' AND is_real='1')
AS a,
(SELECT goods_id,goods_name,COUNT(*) AS salesnum
FROM `ecs_order_goods` WHERE order_id IN ('20','21','22','25','26','27','28','29','30','31','32','33','34','35','36','37','38',
'39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55',
'56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72',
'73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89',
'90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','105',
'106','107','108','109','110','111','112','113','114','115','116','117','118','119','120',
'121','122','123','124','125','126','127','128','129','130','131','132','133','134','135',
'136','139','140','141','142','143','144','145','146','147','148','149','150','151','152',
'153','157','158','159','161','162','163','164') GROUP BY goods_id,goods_name)
AS b
WHERE a.goods_id=b.goods_id)
AS c
WHERE c.goods_id=d.goods_id
);
(2)在ecshop代码里面的SQL语句:
$sql = "UPDATE " . $GLOBALS['ecs']->table('ecs_goods') . " AS d SET d.salesnum=
(SELECT c.salesnum FROM
(SELECT a.goods_id,a.goods_name,b.salesnum
FROM
(SELECT goods_id, goods_name, goods_type, goods_sn, shop_price, is_on_sale, is_best, is_new, is_hot, sort_order,recommend_status,
recommend_point,themeid,marcket_lprice,shop_lprice, goods_number, integral,
(promote_price > 0 AND promote_start_date <= '$today' AND promote_end_date >= '$today') AS is_promote
FROM " . $GLOBALS['ecs']->table('goods') . " AS g WHERE is_delete='$is_delete' $where)
AS a,
(SELECT goods_id,goods_name,COUNT(*) AS salesnum
FROM `ecs_order_goods` WHERE order_id ".db_create_in($order_id_arr)." GROUP BY goods_id,goods_name)
AS b
WHERE a.goods_id=b.goods_id) AS c WHERE c.goods_id=d.goods_id
)";
(3)最后打印出来的SQL语句:
UPDATE `eshop`.`ecs_ecs_goods` AS d SET d.salesnum=
(SELECT c.salesnum
FROM
(SELECT a.goods_id,a.goods_name,b.salesnum
FROM
(SELECT goods_id, goods_name, goods_type, goods_sn, shop_price, is_on_sale, is_best, is_new, is_hot, sort_order,recommend_status,
recommend_point,themeid,marcket_lprice,shop_lprice, goods_number, integral,
(promote_price > 0 AND promote_start_date <= '1353686399' AND promote_end_date >= '1353686399') AS is_promote
FROM `eshop`.`ecs_goods` AS g WHERE is_delete='0' AND is_real='1')
AS a,
(SELECT goods_id,goods_name,COUNT(*) AS salesnum
FROM `ecs_order_goods` WHERE order_id IN ('20','21','22','25','26','27','28','29','30','31','32','33','34','35','36','37','38',
'39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55',
'56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72',
'73','74','75','76','77','78','79','80','81','82','83','84','85','86','87','88','89',
'90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','105',
'106','107','108','109','110','111','112','113','114','115','116','117','118','119','120',
'121','122','123','124','125','126','127','128','129','130','131','132','133','134','135',
'136','139','140','141','142','143','144','145','146','147','148','149','150','151','152',
'153','157','158','159','161','162','163','164') GROUP BY goods_id,goods_name)
AS b
WHERE a.goods_id=b.goods_id)
AS c
WHERE c.goods_id=d.goods_id
)
测试成功。
- 工作笔记——三表查询
- 工作笔记(三)
- 工作笔记(三)
- 工作笔记(三)
- 数据库SQL Server2012笔记(三)——表的复杂查询
- oracle笔记三-查询
- Linux环境工作笔记(三)
- 《Linux内核设计与实现》笔记——CPU的三种工作状态
- 【工作笔记】从零开始学ExtJs6(三)—— 首页搭建
- 【工作笔记】ElasticSearch从零开始学(三)—— 入门(聚合)
- 【工作笔记】从零开始学ExtJs6(三)—— 首页搭建
- Anker—工作学习笔记
- Anker—工作学习笔记
- Anker—工作学习笔记
- 工作笔记—文件损坏
- Hibernate学习笔记总结(三)——Hibernate 3.x——HQL查询语言(重点)
- Oracle学习笔记(三)多表查询(一)
- 经典SQL学习笔记 (三)-多表查询
- 如何检查Linux版本号
- IPC 总结
- ASIHTTPRequest的使用(摘录)
- 关于淘宝与阿里巴巴
- 成员变量和方法变量
- 工作笔记——三表查询
- 不能频繁执行DBCC SHRINKDATABASE的原因
- iOS的init、loadView、 viewDidLoad、viewDidUnload的关系
- 使用函数与触发器 【数据库高效编程 - 学习笔记 第十章】
- iOS零零碎碎
- 基于SIFT+Kmeans+LDA的图片分类器的实现
- 黑马程序员:JAVA集合框架知识点一
- Using ArcGIS Server with a 64-bit processor
- linux驱动简单实例