MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.30


CREATE TABLE `stock_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `stock_id` int DEFAULT NULL,
  `stock_type` enum('in','in_return','out','out_return','transfer','adjust','reserve') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  `amount` double DEFAULT NULL,
  `price` decimal(10,0) DEFAULT NULL,
  `total` decimal(10,0) DEFAULT NULL,
  `note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `stock_items` (`id`, `stock_id`, `stock_type`, `product_id`, `amount`, `price`, `total`, `note`, `created_at`, `updated_at`, `deleted_at`)
VALUES
	(1, 16, 'in', 1, 200, 300, 60000, NULL, '2020-05-20 20:13:38', '2020-06-04 19:09:57', NULL),
	(2, 16, 'in', 1, 25.52, 25, 638, NULL, '2020-05-20 20:13:38', '2020-06-04 10:15:30', NULL),
	(3, 16, 'in', 6, 22, 21, 462, NULL, '2020-05-20 20:13:38', '2020-06-04 10:15:30', NULL),
	(4, 7, 'in', 6, 12, 21, 252, NULL, '2020-05-20 20:13:58', '2020-06-04 19:05:54', NULL),
	(5, 6, 'in', 5, 112, 12, 1344, NULL, '2020-05-20 20:14:18', '2020-06-04 10:14:58', NULL),
	(12, 17, 'in', 7, 100, 22, 2200, NULL, '2020-05-22 09:12:51', '2020-06-04 10:06:44', NULL),
	(13, 17, 'in', 19, 20, 80, 1600, NULL, '2020-06-05 09:31:25', '2020-06-05 09:31:25', NULL),
	(14, 18, 'in', 19, 398, 80, 31840, NULL, '2020-06-05 09:32:22', '2020-06-09 08:10:15', NULL),
	(20, 18, 'in', 13, 20, 9000, 180000, NULL, '2020-06-07 16:20:39', '2020-06-09 08:10:21', NULL),
	(27, 7, 'in_return', 19, 1, 80, 80, NULL, '2020-06-08 08:52:34', '2020-06-08 08:52:34', NULL),
	(28, 7, 'in_return', 13, 1, 9000, 9000, NULL, '2020-06-08 08:52:34', '2020-06-08 08:52:34', NULL),
	(29, 2, 'out', 19, 20, 80, 1600, NULL, '2020-06-08 09:38:03', '2020-06-08 09:38:03', NULL),
	(30, 2, 'out', 7, 20, 80, 1600, NULL, '2020-06-08 09:38:03', '2020-06-08 09:38:03', NULL),
	(31, 2, 'out_return', 19, 5, 80, 400, NULL, '2020-06-09 06:48:30', '2020-06-09 09:40:37', NULL),
	(32, 2, 'out_return', 7, 5, 80, 400, NULL, '2020-06-09 06:48:30', '2020-06-09 09:40:37', NULL),
	(35, 21, 'in', 5, 12, 9000, 108000, NULL, '2020-06-09 08:29:24', '2020-06-22 21:47:05', NULL),
	(47, 3, 'out', 1, 20, 500, 10000, NULL, '2020-06-11 09:32:18', '2020-06-16 15:09:56', NULL);

Records: 17  Duplicates: 0  Warnings: 0


select
product_id,
sum( IF(stock_type = 'out_return', -1 * amount, amount)) as out_amount_total,
sum( IF(stock_type = 'out_return', -1 * total, total)) as out_price_total 
from stock_items
where stock_type in ('out', 'out_return') 
group by product_id 
order by product_id


product_id
out_amount_total
out_price_total


1
20
10000

7
15
1200

19
15
1200


with cte AS (select  si.product_id,
sum(IF(stock_type='in',amount,-1*amount)) as in_price_amount ,
sum(IF(stock_type='in',total,-1*total)) as in_price_total ,
b.out_amount_total,
b.out_price_total
from stock_items as si
inner join
(select
product_id,
sum( IF(stock_type = 'out_return', -1 * amount, amount)) as out_amount_total,
sum( IF(stock_type = 'out_return', -1 * total, total)) as out_price_total 
from stock_items 
where stock_type in ('out', 'out_return') 
group by product_id ) b
on si.product_id=b.product_id
where stock_type in ('in','in_return')
group by  si.product_id
)
select * from cte order by product_id


product_id
in_price_amount
in_price_total
out_amount_total
out_price_total


1
225.52
60638
20
10000

7
100
2200
15
1200

19
417
33360
15
1200


with cte AS (select  si.product_id,
sum(IF(stock_type='in',amount,-1*amount)) as in_price_amount ,
sum(IF(stock_type='in',total,-1*total)) as in_price_total ,
b.out_amount_total,
b.out_price_total
from stock_items as si
inner join
(select
product_id,
sum( IF(stock_type = 'out_return', -1 * amount, amount)) as out_amount_total,
sum( IF(stock_type = 'out_return', -1 * total, total)) as out_price_total 
from stock_items 
where stock_type in ('out', 'out_return') 
group by product_id ) b
on si.product_id=b.product_id
where stock_type in ('in','in_return')
group by  si.product_id
)
select 
c.product_id,
convert(c.out_price_total-((c.in_price_total/c.in_price_amount)*c.out_amount_total),decimal(16,2)) as '毛利',
convert((c.out_price_total-((c.in_price_total/c.in_price_amount)*c.out_amount_total))/c.out_price_total*100,decimal(16,2)) as '毛利率%'
from cte c 


product_id
毛利
毛利率%


1
4622.38
46.22

7
870.00
72.50

19
0.00
0.00