在MySQL查詢中按選定日期和倉庫顯示庫存數量的INWARD和OUTWARD

在查詢中需要幫助以獲得所需的輸出。這是我的表結構：

1. 產品

   這qty是一個股票

   ID	產品名稱	數量
   1	測驗	5

2. 購買

   從采購表中，我們必須通過 ID 選擇日期和倉庫

   ID	倉庫編號	日期
   1	1	04-03-2022

3. 采購專案

   應從該表中顯示內向庫存

   ID	product_id	數量	購買編號
   1	1	5	1

4. 命令

   從訂單表中，我們必須通過 ID 選擇日期和倉庫 這里詳細資訊是倉庫 ID

   ID	細節	日期
   1	1	04-03-2022

5. 訂單專案

   應從該表中顯示出庫。

   ID	product_id	數量	order_id
   1	1	5	1

期望的輸出應該是這樣的：

WhereP表示采購 &O表示訂單

查詢以生成相應的表：

db<>fiddle here

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `product_name` varchar(150) NOT NULL,
  `qty` int(10) NOT NULL,
  `price` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
;


CREATE TABLE `purchase` (
  `id` int(11) NOT NULL,
  `warehouse_id` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

CREATE TABLE `purchase_item` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `purchase_id` int(11) NOT NULL,
  `qty` varchar(255) NOT NULL,
  `rate` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

CREATE TABLE `order` (
  `id` int(11) NOT NULL,
  `warehouse_id` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

CREATE TABLE `order_item` (
  `id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `qty` varchar(255) NOT NULL,
  `rate` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;

INSERT INTO `products` (`id`, `product_name`, `qty`, `price`) 
VALUES
(1,  'TEST', 1, '50'),
(2, 'TEST 2', 0, '50'),
(3, 'TEST 3', 0, '50'),
(4,  'TEST 4', 0, '50')
;

INSERT INTO `purchase` (`id`, `warehouse_id`, `date`) VALUES
(1, 1, '2022-03-02');

INSERT INTO `purchase_item` (`id`, `product_id`, `purchase_id`, `qty`, `rate`) VALUES
(1, 1, 1, '2', '50');


INSERT INTO `order` (`id`, `warehouse_id`, `date`) VALUES
(1, 1, '2022-03-02');

INSERT INTO `order_item` (`id`, `product_id`, `order_id`, `qty`, `rate`) VALUES
(1, 1, 1, '2', '50');

uj5u.com熱心網友回復：

這是一個回傳您正在尋找的格式的查詢。
我已將其更新為在子句中使用date和warehouse_id作為引數。WHERE

with stock_movements as
(
select 
  'P' PA,
  pu.warehouse_id ,
  pu.`date` mvtdate,
  p.id,
  concat(p.product_name,'(P)') Pname,
  pi.qty pqty,
  0 as oqty,
  sum(coalesce(pi.qty,0)) Total
from products p
left join purchase_item pi
on p.id = pi.product_id
left join purchase pu
on pu.id = purchase_id
group by 
  pu.warehouse_id,
  p.id,
  p.product_name,
  pu.`date`,
  pi.qty
union all
select
  'A',
  ord.`warehouse_id`,
  ord.`date` mvtDate,
  p.id,
  concat(p.product_name,'(O)'),
  0,
  sum(coalesce(o.qty,0)) oqty,
  sum(coalesce(pi.qty,0))
     -sum(coalesce(o.qty,0))
from products p
left join order_items o
on p.id = o.product_id
left join purchase_item pi
on p.id = pi.product_id
left join `order` ord
on ord.id = order_id
group by
  ord.`warehouse_id`,  
  ord.`date`,
  p.id,
  p.product_name
order by 
  id
)
select
PA,
warehouse_id,
mvtdate "date",
id "ID",
pname "Product Name",
sum(pqty) "Input",
sum(oqty) "Output",
sum(Total) "Total"
from stock_movements
where mvtDate = '2022-03-02'
and warehouse_Id = '1'
group by
id, pname,pa,warehouse_id
order by 
id,pa desc;

賓夕法尼亞州 | 倉庫id | 日期 | 身份證 | 產品名稱 | 輸入 | 輸出 | 全部的
:- | ------------: | :--------- | -: | :----------- | ----: | -----: | ----:
磷 | 1 | 2022-03-02 | 1 | 測驗(P) | 2 | 0 | 2
一個 | 1 | 2022-03-02 | 1 | 測驗(O) | 0 | 1 | 1
磷 | 1 | 2022-03-02 | 2 | 測驗 2(P) | 3 | 0 | 3
一個 | 1 | 2022-03-02 | 2 | 測驗 2(O) | 0 | 1 | 2

db<>在這里擺弄

第一個 DB FIDDLE：*db<>fiddle [這里]( https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a6d7e94af44035d838b2be5bf863a476

標籤：php mysql sql database join

上一篇：OracleSQL-回傳修改的記錄，加入時排序問題

下一篇：SQL-通過聯結表連接表時如何避免重復行？