MySQL能否向已有CSV文件写入数据并新增周末库存列？

场景1：导出CSV前直接生成含周末库存的查询结果 #

如果还未执行导出操作，直接修改数据库查询语句，关联库存数据获取周末库存即可。假设库存表为product_inventory，包含product_id、inventory_date、stock_quantity字段，周初取周一、周末取周日：

MySQL示例 #

SELECT
  ws.product_id,
  ws.stock_quantity AS 周初库存,
  we.stock_quantity AS 周末库存
FROM
  (SELECT product_id, stock_quantity
   FROM product_inventory
   WHERE inventory_date = DATE_SUB(CURRENT_DATE(), INTERVAL WEEKDAY(CURRENT_DATE()) DAY)) ws
JOIN
  (SELECT product_id, stock_quantity
   FROM product_inventory
   WHERE inventory_date = DATE_ADD(CURRENT_DATE(), INTERVAL (6 - WEEKDAY(CURRENT_DATE())) DAY)) we
ON ws.product_id = we.product_id;

PostgreSQL示例 #

SELECT
  ws.product_id,
  ws.stock_quantity AS 周初库存,
  we.stock_quantity AS 周末库存
FROM
  (SELECT product_id, stock_quantity
   FROM product_inventory
   WHERE inventory_date = CURRENT_DATE - INTERVAL '1 day' * EXTRACT(DOW FROM CURRENT_DATE)) ws
JOIN
  (SELECT product_id, stock_quantity
   FROM product_inventory
   WHERE inventory_date = CURRENT_DATE + INTERVAL '1 day' * (6 - EXTRACT(DOW FROM CURRENT_DATE))) we
ON ws.product_id = we.product_id;

执行上述查询后直接导出CSV，就能得到包含三列的结果。

场景2：已导出CSV，用SQL工具补全周末库存 #

如果已经有了仅含product_id和周初库存的CSV，可以将其导入临时数据库表，再关联库存数据生成新的结果：

以SQLite为例（轻量无需额外服务） #

1. 创建临时表并导入CSV

CREATE TEMP TABLE temp_weekly_stock (
  product_id INT,
  周初库存 INT
);
.mode csv
.import /your/csv/path/exported.csv temp_weekly_stock

2. 关联库存表查询补全周末库存

SELECT
  t.product_id,
  t.周初库存,
  pi.stock_quantity AS 周末库存
FROM temp_weekly_stock t
JOIN product_inventory pi
ON t.product_id = pi.product_id
WHERE pi.inventory_date = DATE('now', 'weekday 6'); -- 取本周日的库存数据

3. 将查询结果导出为新CSV即可。

若需通过出入库计算周末库存 #

如果没有直接的周末库存记录，可通过周初库存加减本周出入库数据计算：

SELECT
  t.product_id,
  t.周初库存,
  t.周初库存 + COALESCE(SUM(io.in_qty), 0) - COALESCE(SUM(io.out_qty), 0) AS 周末库存
FROM temp_weekly_stock t
LEFT JOIN inventory_io io
ON t.product_id = io.product_id
AND io.io_date BETWEEN DATE('now', 'weekday 0') AND DATE('now', 'weekday 6')
GROUP BY t.product_id, t.周初库存;

内容的提问来源于stack exchange，提问作者gupta35