mysql - GROUP_CONCAT() 和 GROUP BY 中的 SUM()
问题描述
我正在尝试SUM()
使用我的股票表中的列,GROUP_CONCAT()
但似乎无法获得我正在寻找的输出。
MySQL 表:
CREATE TABLE 'suppliers' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'supplier_code' varchar(10) NOT NULL,
'name' varchar(64) NOT NULL,
'vat_registration_number' varchar(20) NOT NULL,
'contact_name' varchar(64) DEFAULT NULL,
'email_address' varchar(255) DEFAULT NULL,
'phone_number' varchar(13) DEFAULT NULL,
'address' varchar(64) DEFAULT NULL,
'suburb' varchar(64) DEFAULT NULL,
'city' varchar(64) DEFAULT NULL,
'zip' varchar(10) DEFAULT NULL,
'deleted' enum('0','1') NOT NULL DEFAULT '0',
PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1
CREATE TABLE 'warehouses' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'name' varchar(65) NOT NULL,
'disabled' enum('0','1') NOT NULL DEFAULT '0',
'deleted' enum('0','1') NOT NULL DEFAULT '0',
PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
CREATE TABLE 'stock_items' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'supplier_id' int(11) NOT NULL,
'stock_code' varchar(10) NOT NULL,
'description' varchar(255) NOT NULL,
'type' enum('product','service') NOT NULL,
'selling_price' decimal(19,2) NOT NULL DEFAULT '0.00',
'inclusive_vat' enum('0','1') NOT NULL,
'barcode' varchar(15) DEFAULT NULL,
'deleted' enum('0','1') NOT NULL DEFAULT '0',
PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
CREATE TABLE 'stock' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'stock_item_id' int(11) NOT NULL,
'warehouse_id' int(11) NOT NULL,
'supplier_invoice_id' int(11) NOT NULL,
'quantity' int(11) NOT NULL,
'cost_price' decimal(19,2) NOT NULL DEFAULT '0.00',
'deleted' enum('0','1') NOT NULL DEFAULT '0',
PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1
我目前的查询:
SELECT
stock.id,
stock_items.stock_code,
GROUP_CONCAT(warehouses.name ORDER BY warehouses.id) as warehouse,
GROUP_CONCAT(stock.quantity ORDER BY warehouses.id) as stock_in_warehouse
FROM stock
LEFT JOIN stock_items ON stock_items.id = stock.stock_item_id
LEFT JOIN warehouses ON warehouses.id = stock.warehouse_id
WHERE stock_items.deleted='0'
GROUP BY stock.stock_item_id
给我输出
id | stock_code | warehouse | stock_in_warehouse
1 | MIKHAPLITE | One,One,Two | 4,5,2
我需要GROUP_CONCAT() as warehouse
toGROUP BY(warehouses.id)
和GROUP_CONCAT() as stock_in_warehouse
toSUM(stock.quantity)
使输出看起来像:
id | stock_code | warehouse | stock_in_warehouse
1 | MIKHAPLITE | One,Two | 9,2
我曾尝试使用其他帖子/论坛中提到的 CAST() 以及此处提到的其他方法,但似乎无法得到我想要做的事情。
任何帮助将不胜感激。
解决方案
似乎我找到了我正在寻找的查询:
SELECT
der.stock_code,
der.description,
der.type,
der.selling_price,
der.inclusive_vat,
der.barcode,
GROUP_CONCAT(der.warehouse) warehouse,
GROUP_CONCAT(der.stock_in_warehouse) stock_in_warehouse
FROM (
SELECT
der.stock_code,
der.description,
der.type,
der.selling_price,
der.inclusive_vat,
der.barcode,
der.warehouse,
SUM(der.stock_in_warehouse) stock_in_warehouse
FROM (
SELECT
stock_items.stock_code,
stock_items.description,
stock_items.type,
stock_items.selling_price,
stock_items.inclusive_vat,
stock_items.barcode,
stock.quantity as stock_in_warehouse,
warehouses.name as warehouse
FROM stock
LEFT JOIN stock_items ON stock_items.id = stock.stock_item_id
LEFT JOIN warehouses ON warehouses.id = stock.warehouse_id
WHERE stock_items.deleted='0'
ORDER BY
stock.id,
warehouses.id
) der
GROUP BY
der.stock_code,
der.warehouse
ORDER BY
der.warehouse
) der
GROUP BY
der.stock_code
ORDER BY
der.warehouse
推荐阅读
- angular5 - 使用代理时,Angular 5 或 6 订阅不起作用
- python-3.x - 即使模块可用,python 模块未找到错误
- python - 如何在python中访问嵌套字典
- algorithm - 根据与输入字符串匹配的单词对字符串数组进行排序
- oracle - docker weblogic 12.2.1 中不显示新服务器
- ruby - 控制器的 CanCanCan 别名方法
- sonarqube - SSO 身份验证作为 HTTP 标头的 Sonarqube 注销问题
- java - 无法在 IE 11 浏览器中的对话框上单击取消
- asp.net-core - 如何在 ASP.Net Boilerplate 中本地化数据注释中的错误消息?
- string - 如何在python列表中搜索特定的unicode标记