mysql - MYSQL:仅根据 ID 分组在第一行添加一些数据
问题描述
我有这组样本数据:
表invoice
:
--------------------------------------------------
| ID | date | invoice_number | total |
--------------------------------------------------
| 81 | 2017-03-24 | 0000000173 | 190.00 |
--------------------------------------------------
表invoice_addon
:
----------------------------------------------------------
| ID | invoice_id | description | amount |
----------------------------------------------------------
| 46 | 81 | Price Adjust. - Jumbo | -12.00 |
| 47 | 81 | Price Adjust. - Regular | -12.00 |
----------------------------------------------------------
表orders
:
----------------------------------------------------------------------------------
| ID | invoice_id | box_name | size | price | tax | box_number |
----------------------------------------------------------------------------------
| 177 | 81 | Jumbo Box | 23x25x17 | 97.00 | 15.00 | FCI107056 |
| 178 | 81 | Regular Box | 20x23x17 | 87.00 | 15.00 | FCI107057 |
----------------------------------------------------------------------------------
我想要达到的目标:
----------------------------------------------------------------------------------------------------------------------------------
| trans_date | inv_number | box_name | size | gross | box_number | others | description | net |
----------------------------------------------------------------------------------------------------------------------------------
| 2017-03-24 | 0000000173 | Jumbo Box | 23x25x17 | 112.00 | FCI107056 | -24.00 | Price Adjust. - Jumbo -12.00 | 88.00 |
| | | | | | | | Price Adjust. - Regular -12.00 | |
----------------------------------------------------------------------------------------------------------------------------------
| 2017-03-24 | 0000000173 | Regular Box | 20x23x17 | 102.00 | FCI107057 | 0 | NULL | 102.00|
----------------------------------------------------------------------------------------------------------------------------------
我目前的查询:
SELECT DATE(i.date) AS trans_date, i.invoice_number AS inv_number, o.box_name as box_name,
o.size AS size, (SELECT Price + Tax FROM orders WHERE ID = o.ID) AS gross,
o.box_number AS box_number,
SUM(a.amount) AS others,
(SELECT GROUP_CONCAT(CONCAT(description, ' ', amount) SEPARATOR '<br />') FROM invoice_addon WHERE invoice_id = i.ID) AS description,
(SUM(o.price + o.tax) + SUM(a.amount)) AS net
FROM `invoice` i
INNER JOIN orders o ON i.ID = o.invoice_id
LEFT JOIN invoice_addon a ON i.ID = a.invoice_id
WHERE i.ID = 81
GROUP BY o.ID
我的查询结果中的问题是,others
列description
加倍。它应该只显示在第一行。无论发票上捆绑了多少个箱子,都应该只在第一行添加。网络也依赖于这些列。
我得到什么:
----------------------------------------------------------------------------------------------------------------------------------
| trans_date | inv_number | box_name | size | gross | box_number | others | description | net |
----------------------------------------------------------------------------------------------------------------------------------
| 2017-03-24 | 0000000173 | Jumbo Box | 23x25x17 | 112.00 | FCI107056 | -24.00 | Price Adjust. - Jumbo -12.00 | 200.00 |
| | | | | | | | Price Adjust. - Regular -12.00 | |
----------------------------------------------------------------------------------------------------------------------------------
| 2017-03-24 | 0000000173 | Regular Box | 20x23x17 | 102.00 | FCI107057 | -24.00 | Price Adjust. - Jumbo -12.00 | 180.00 |
| | | | | | | | Price Adjust. - Regular -12.00 | |
----------------------------------------------------------------------------------------------------------------------------------
可能吗?我怎样才能做到这一点?(我在 MySQL 中使用 CodeIgniter 和 DataTable 执行此操作)
解决方案
好吧,您需要一些var
可以存储当前行号并检查它是否等于1
. 像这样的东西:
SET @row_number = 0;
SELECT DATE(i.date) AS trans_date, i.invoice_number AS inv_number, o.box_name as box_name,
o.size AS size, (SELECT Price + Tax FROM orders WHERE ID = o.ID) AS gross,
o.box_number AS box_number,
if((@row_number:=@row_number + 1)=1,SUM(a.amount), null) AS others,
if(@row_number=1,(SELECT GROUP_CONCAT(CONCAT(description, ' ', amount)
SEPARATOR '<br />') FROM invoice_addon WHERE invoice_id = i.ID), null) AS description,
(SUM(o.price + o.tax) + SUM(a.amount)) AS net
FROM `invoice` i
INNER JOIN orders o ON i.ID = o.invoice_id
LEFT JOIN invoice_addon a ON i.ID = a.invoice_id
WHERE i.ID = 81
GROUP BY o.ID
推荐阅读
- typescript - 打字稿 | 使用参数将类型参数传递给另一个类型
- php - 用于在自定义文件夹中上传 pdf 文件的 WordPress 插件
- android - Android 布局在预览时显示 GreyBoxes
- python - 将值写入 CSV 文件后无法获得逐行输出,因为其中一个值包含逗号
- android - 从 ArrayList 中删除一个值
不运行循环 - python - 如何使用数据库中的新数据更新 plotly dash 应用程序
- python - 绘制 PCA 的某些维度时出现 3D 绘图和图例问题
- c# - 如何获取类列表中的属性列表?
- sql - sql查询结果中的英镑符号(£)
- graphql - 基于 GraphQL Param 更改后端服务