MySQL派生表联表查询的示例分析-mysql教程-PHP中文网

前情提要：

查询过程：

首页

数据库

mysql教程

MySQL派生表联表查询的示例分析

PHPz

Apr 17, 2023 pm 06:40 PM

mysql

前情提要：

公司运营的一个商城系统，忽然发现订单提现功能有问题，有大量的商户体现金额和订单金额不一致。于是产生了需求，需要把提现表和供应商表作为一个结果集，连接上订单表中的订单金额，通过计算订单表的金额和体现表商户提现的金额进行比对，查看商户是多提现了还是少提现了。

下面记录我的查询过程。

查询过程：

刚开始，第一步我以提现表为主表，查询出来相关结果。MySQL语句如下

SELECT  count(ysw.supply_id) AS &#39;提现次数&#39;,ysw.user_id AS &#39;供应商对应的用户ID&#39;, ysw.supply_id  AS &#39;供应商ID&#39; ,SUM(ysw.money)  AS &#39;供应商提现总金额&#39;,
case ysw.pay_type when 10 then &#39;微信&#39; when 20 then &#39;支付宝&#39; else &#39;银行卡&#39; end as &#39;支付方式&#39; ,
ys.supply_name AS &#39;供应商名称&#39;,ys.money AS &#39;供应商余额&#39;,ys.freez_money AS &#39;供应商冻结金额（已提现金额）&#39;
FROM yoshop_supply_withdraw AS ysw LEFT JOIN yoshop_supply AS ys ON ysw.supply_id = ys.supply_id
WHERE ysw.create_time < 1647446400 AND ysw.apply_status IN (10,20,40) GROUP BY ysw.supply_id
ORDER BY SUM(ysw.money) DESC ;

登录后复制

查询结果如图是正常的：

MySQL派生表联表查询的示例分析

接下来，我在左链接上订单表的数据，又添加一个了left join，金额相关数据发生了变化严重不一致,而且查询时间明显延长，MySQL语句如下

SELECT  count(ysw.supply_id) AS &#39;提现次数&#39;,ysw.user_id AS &#39;供应商对应的用户ID&#39;, ysw.supply_id  AS &#39;供应商ID&#39; ,SUM(ysw.money)  AS &#39;供应商提现总金额&#39;,
case ysw.pay_type when 10 then &#39;微信&#39; when 20 then &#39;支付宝&#39; else &#39;银行卡&#39; end as &#39;支付方式&#39; ,
ys.supply_name AS &#39;供应商名称&#39;,ys.money AS &#39;供应商余额&#39;,ys.freez_money AS &#39;供应商冻结金额（已提现金额）&#39;,SUM(yo.pay_price)

FROM yoshop_supply_withdraw AS ysw LEFT JOIN yoshop_supply AS ys ON ysw.supply_id = ys.supply_id
LEFT JOIN yoshop_order AS yo ON yo.supply_ids =ysw.supply_id 

WHERE ysw.create_time < 1647446400 AND ysw.apply_status IN (10,20,40) GROUP BY ysw.supply_id
ORDER BY SUM(ysw.money) DESC ;

登录后复制

查询结果对比图如下：

MySQL派生表联表查询的示例分析

经过实践，我想直接通过左连接查询到提现表金额和订单表金额是行不通的。通过网上查资料，以及在技术群里请教，

优化了思路：把提现的统计好,把订单的统计好，最后两个结果集再根据供应商id做个链接

接下来就是，三步走了，第一步：把提现的统计好，上面第一次尝试的第一步就是了，第二步：把订单表的数据统计好。由于使用系统的原因，我直接使用的订单商品表计算的订单总金额，这一步也是分三步走的，我直接上代码：

1.查询yoshop_order所有进行中，已完成的 订单id(order_id);
	SELECT order_id FROM yoshop_order WHERE order_status IN (10,30);
	
2.查询没有退款的订单ID
	SELECT order_id FROM yoshop_order WHERE order_status IN (10,30) AND order_id NOT IN ( SELECT order_id FROM yoshop_order_refund);
	
3.查询订单商品表中 所有的订单金额

SELECT  supply_id  AS &#39;供应商ID&#39; , SUM(total_pay_price)  AS &#39;供应商订单总金额&#39; FROM yoshop_order_goods WHERE  create_time < 1647446400 AND order_pay_status = 0  AND  order_id IN(SELECT order_id FROM yoshop_order WHERE order_status IN (10,30) AND order_id NOT IN ( SELECT order_id FROM yoshop_order_refund)	 )  GROUP BY supply_id 
ORDER BY SUM(total_pay_price) DESC ;

登录后复制

接下来就是进行把第一步和第二步的查询结果当作派生表，进行左连接查询。我在这一步耗费的时间和精力最多。如果你能认真看完，相信一定会有收货。我在这里把我错误的过程也进行了记录第一次错误拼接：

SELECT * FROM  (
	SELECT  count(ysw.supply_id) AS &#39;提现次数&#39;,ysw.user_id AS &#39;供应商对应的用户ID&#39;, ysw.supply_id  AS &#39;supply_id&#39; ,SUM(ysw.money)  AS &#39;供应商提现总金额&#39;,
	case ysw.pay_type when 10 then &#39;微信&#39; when 20 then &#39;支付宝&#39; else &#39;银行卡&#39; end as &#39;支付方式&#39; ,
	ys.supply_name AS &#39;供应商名称&#39;,ys.money AS &#39;供应商余额&#39;,ys.freez_money AS &#39;供应商冻结金额（已提现金额）&#39;
	FROM yoshop_supply_withdraw AS ysw LEFT JOIN yoshop_supply AS ys ON ysw.supply_id = ys.supply_id
	WHERE ysw.create_time < 1647446400 AND ysw.apply_status IN (10,20,40) GROUP BY ysw.supply_id
	ORDER BY SUM(ysw.money) DESC ) AS t1 
union all    // left join ,这里是注释记得删除

SELECT * FROM   --  这里是错误的不应该在查询
		(SELECT  supply_id  AS &#39;supply_id&#39; , SUM(total_pay_price)  AS total_pay_price FROM yoshop_order_goods WHERE  create_time < 1647446400 AND order_pay_status = 0  AND  order_id IN(
	SELECT order_id FROM yoshop_order WHERE order_status IN (10,30) AND order_id NOT IN (
	SELECT order_id FROM yoshop_order_refund)	 )  GROUP BY supply_id 
ORDER BY SUM(total_pay_price) DESC ) AS t2
								
ON t1.suppply_id = t2.suppply_id

登录后复制

通过这一次试错，明显看出我把left join 和 union all 的含义记错了,并且在拼接的时候重复使用了select * from 。虽然是试错了，但也是有收货的，接下来进行了第二次错误的拼接：

SELECT t1.提现次数 ,t1.供应商对应的用户ID ,t1.supply_id, t1.支付方式 ,t1.供应商名称,t1.供应商余额, t1.供应商冻结金额（已提现金额）, t2.total_pay_price FROM  (
SELECT  count(ysw.supply_id) AS &#39;提现次数&#39;,ysw.user_id AS &#39;供应商对应的用户ID&#39;, ysw.supply_id  AS supply_id ,SUM(ysw.money)  AS &#39;供应商提现总金额&#39;,
case ysw.pay_type when 10 then &#39;微信&#39; when 20 then &#39;支付宝&#39; else &#39;银行卡&#39; end as &#39;支付方式&#39; ,
	ys.supply_name AS &#39;供应商名称&#39;,ys.money AS &#39;供应商余额&#39;,ys.freez_money AS &#39;供应商冻结金额（已提现金额）&#39;
	FROM yoshop_supply_withdraw AS ysw LEFT JOIN yoshop_supply AS ys ON ysw.supply_id = ys.supply_id
	WHERE ysw.create_time < 1647446400 AND ysw.apply_status IN (10,20,40) GROUP BY ysw.supply_id
	ORDER BY SUM(ysw.money) DESC ) AS t1 
        
 LEFT JOIN
		
(SELECT  supply_id  AS supply_id , SUM(total_pay_price)  AS total_pay_price FROM 
yoshop_order_goods WHERE  create_time < 1647446400 AND order_pay_status = 0  
AND  order_id IN(
SELECT order_id FROM yoshop_order WHERE order_status IN (10,30) 
AND order_id NOT IN (
SELECT order_id FROM yoshop_order_refund)	 )  
GROUP BY supply_id 
ORDER BY SUM(total_pay_price) DESC ) AS t2						
ON t1.suppply_id = t2.suppply_id

登录后复制

通过这两次错误的尝试，以及根据尝试过程中MySQL给出的错误提示，知道自己是在左连接上使用错误了，应该在开始查询出来所有的字段，left join 后不能在使用select * 最后，回想了一遍自己所学的left join的语法，写出了最后的正确的查询结果

SELECT t1.supply_id &#39;供应商ID&#39;,t1.supply_name &#39;供应商名称&#39;,t1.user_id &#39;供应商绑定的用户ID&#39;,t1.withdrawtime &#39;供应商提现次数&#39; ,t1.supplyallmoney &#39;供应商提现金额&#39;,t1.payway &#39;供应商提现方式&#39;,t1.supply_money &#39;供应商账户余额&#39;,t1.supply_free_money &#39;供应商冻结余额（已提现金额）&#39;,
t2.total_pay_price &#39;供应商订单总金额&#39;,t2.order_id &#39;供应商订单数量&#39;
FROM  (											
SELECT  count(ysw.supply_id) AS withdrawtime,  ysw.user_id AS user_id,   ysw.supply_id  AS supply_id ,  SUM(ysw.money)  AS supplyallmoney,   ysw.alipay_name AS alipay_name ,ysw.alipay_account AS alipay_account,  ysw.audit_time as audit_time ,  ysw.bank_account AS bank_account,   ysw.bank_card AS bank_card,   ysw.bank_name AS bank_name,
case ysw.pay_type when 10 then &#39;微信&#39; when 20 then &#39;支付宝&#39; else &#39;银行卡&#39; end as payway ,
ys.supply_name AS supply_name,  ys.money AS supply_money,  ys.freez_money AS supply_free_money
FROM yoshop_supply_withdraw AS ysw LEFT JOIN yoshop_supply AS ys ON ysw.supply_id = ys.supply_id
WHERE ysw.create_time < 1647446400 AND ysw.apply_status IN (10,20,40) GROUP BY ysw.supply_id
ORDER BY SUM(ysw.money) DESC ) AS t1 
	
 LEFT JOIN

    (SELECT  supply_id  AS &#39;supply_id&#39; , COUNT(order_id) AS order_id,   SUM(total_pay_price)    AS total_pay_price 
    FROM 	yoshop_order_goods WHERE  create_time < 1647446400 AND order_pay_status = 0  
    AND  order_id IN(
        SELECT order_id FROM yoshop_order WHERE order_status IN (10,30) 
    AND order_id NOT IN (
        SELECT order_id FROM yoshop_order_refund)	 ) 
    GROUP BY supply_id 
    ORDER BY SUM(total_pay_price) DESC ) AS t2
								
ON t1.supply_id = t2.supply_id

登录后复制

正确的结果截图：

MySQL派生表联表查询的示例分析

以上是MySQL派生表联表查询的示例分析的详细内容。更多信息请关注PHP中文网其他相关文章！

本站声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

热AI工具

热工具

热门话题

gmail邮箱登陆入口在哪里

7720

Java教程

1642

CakePHP 教程

1396

Laravel 教程

1289

PHP教程

1233

显示更多

Related knowledge

MySQL：世界上最受欢迎的数据库的简介 Apr 12, 2025 am 12:18 AM

MySQL是一种开源的关系型数据库管理系统，主要用于快速、可靠地存储和检索数据。其工作原理包括客户端请求、查询解析、执行查询和返回结果。使用示例包括创建表、插入和查询数据，以及高级功能如JOIN操作。常见错误涉及SQL语法、数据类型和权限问题，优化建议包括使用索引、优化查询和分表分区。

MySQL的位置：数据库和编程 Apr 13, 2025 am 12:18 AM

MySQL在数据库和编程中的地位非常重要，它是一个开源的关系型数据库管理系统，广泛应用于各种应用场景。1）MySQL提供高效的数据存储、组织和检索功能，支持Web、移动和企业级系统。2）它使用客户端-服务器架构，支持多种存储引擎和索引优化。3）基本用法包括创建表和插入数据，高级用法涉及多表JOIN和复杂查询。4）常见问题如SQL语法错误和性能问题可以通过EXPLAIN命令和慢查询日志调试。5）性能优化方法包括合理使用索引、优化查询和使用缓存，最佳实践包括使用事务和PreparedStatemen

为什么要使用mysql？利益和优势 Apr 12, 2025 am 12:17 AM

选择MySQL的原因是其性能、可靠性、易用性和社区支持。1.MySQL提供高效的数据存储和检索功能，支持多种数据类型和高级查询操作。2.采用客户端-服务器架构和多种存储引擎，支持事务和查询优化。3.易于使用，支持多种操作系统和编程语言。4.拥有强大的社区支持，提供丰富的资源和解决方案。

apache怎么连接数据库 Apr 13, 2025 pm 01:03 PM

Apache 连接数据库需要以下步骤：安装数据库驱动程序。配置 web.xml 文件以创建连接池。创建 JDBC 数据源，指定连接设置。从 Java 代码中使用 JDBC API 访问数据库，包括获取连接、创建语句、绑定参数、执行查询或更新以及处理结果。

docker怎么启动mysql Apr 15, 2025 pm 12:09 PM

在 Docker 中启动 MySQL 的过程包含以下步骤：拉取 MySQL 镜像创建并启动容器，设置根用户密码并映射端口验证连接创建数据库和用户授予对数据库的所有权限

MySQL的角色：Web应用程序中的数据库 Apr 17, 2025 am 12:23 AM

MySQL在Web应用中的主要作用是存储和管理数据。1.MySQL高效处理用户信息、产品目录和交易记录等数据。2.通过SQL查询，开发者能从数据库提取信息生成动态内容。3.MySQL基于客户端-服务器模型工作，确保查询速度可接受。

centos7如何安装mysql Apr 14, 2025 pm 08:30 PM

优雅安装 MySQL 的关键在于添加 MySQL 官方仓库。具体步骤如下：下载 MySQL 官方 GPG 密钥，防止钓鱼攻击。添加 MySQL 仓库文件：rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm更新 yum 仓库缓存：yum update安装 MySQL：yum install mysql-server启动 MySQL 服务：systemctl start mysqld设置开机自启动

laravel入门实例 Apr 18, 2025 pm 12:45 PM

Laravel 是一款 PHP 框架，用于轻松构建 Web 应用程序。它提供一系列强大的功能，包括：安装：使用 Composer 全局安装 Laravel CLI，并在项目目录中创建应用程序。路由：在 routes/web.php 中定义 URL 和处理函数之间的关系。视图：在 resources/views 中创建视图以呈现应用程序的界面。数据库集成：提供与 MySQL 等数据库的开箱即用集成，并使用迁移来创建和修改表。模型和控制器：模型表示数据库实体，控制器处理 HTTP 请求。

See all articles

MySQL派生表联表查询的示例分析

前情提要：

查询过程：

热AI工具

Undresser.AI Undress

AI Clothes Remover

Undress AI Tool

Clothoff.io

Video Face Swap

热门文章

热工具

记事本++7.3.1

SublimeText3汉化版

禅工作室 13.0.1

Dreamweaver CS6

SublimeText3 Mac版

热门话题