我正在为我的公司编写一个关于股票监控的小型应用程序。使用mysql和codeigniter 2.1.0。
两张表：交货和库存卡
我很感谢您提供的有关构建查询以列出库存名称和剩余数量的帮助。
最终输出要求为：（请查看下表）
<tr>
<td>Product Name 1</td>
<td> 2 pcs </td>
</tr>
<tr>
<td>Product Name 2</td>
<td> 5 pcs </td>
</tr>
我想任务是：
使用id_stock连接表，
按id_stock分组
在每组下，得到"不知何故"的数量总和，其中direction=rcvd
在每个组下，获取"不知何故"的数量总和，其中direction=sended
"不知何故"为每个组创建一个新列，rcvd减去send=remaining_quantity
按stokcards.name ASC订购
我没能写出正确的小说，甚至是正确的语法。谢谢你的帮助。
交付：
+-------------+----------+-----------+----------+
| id_delivery | id_stock | direction | quantity |
+-------------+----------+-----------+----------+
| A.I. | 1 | rcvd | 15 |
| | 1 | sent | 13 |
| | 2 | rcvd | 50 |
| | 2 | sent | 45 |
+-------------+----------+-----------+----------+
库存卡：
+----------+-----------------+
| id_stock | name |
+----------+-----------------+
| 1 | Product Name 1 |
| 2 | Product Name 2 |
+----------+-----------------+
试试这个：
SELECT DISTINCT a.id_stock,
b.`name` as ProductName,
COALESCE(f.TotalReceived, 0) as TotalReceived,
COALESCE(g.TotalSent, 0) as TotalSent,
(COALESCE(f.TotalReceived, 0) - COALESCE(g.TotalSent, 0)) as RemainingStock
FROM deliveries a INNER JOIN stockcards b
ON a.id_stock = b.id_Stock
LEFT JOIN
(
SELECT c.id_stock, SUM(c.quantity) TotalReceived
FROM deliveries c
WHERE c.direction = 'rcvd'
GROUP BY c.id_stock
) f ON a.id_stock = f.id_stock
LEFT JOIN
(
SELECT d.id_stock, SUM(d.quantity) TotalSent
FROM deliveries d
WHERE d.direction = 'sent'
GROUP BY d.id_stock
) g ON a.id_stock = g.id_stock
ORDER BY ProductName ASC
首先，如果这是一个新系统，我会考虑将您发送的任何信息记录为负数，因为这将大大简化问题。然而，也有其他选择，如果你要接收或添加库存，那么简单地对接收和发送分别取正和负可能会很好地完成你的工作。
作为适合您当前模式的答案，以下内容将按照您的要求进行
SELECT
s.*,
SUM(COALESCE(d_in.quantity,0))-SUM(COALESCE(d_out.quantity,0)) AS quantiyInStock
FROM stockcards AS s
LEFT JOIN deliveries AS d_in
ON d_in.id_stock = s.id_stock
AND d_in.direction = 'rcvd'
LEFT JOIN deliveries AS d_out
ON d_out.id_stock = s.id_stock
AND d_out.direction = 'sent'
GROUP BY s.id_stock
为了使上面的工作尽可能高效，你应该确保你有以下关键
在表"deliverys"上，一个由列（"id_stock"、"id_direction"）组成的键