MariaDB 10.9

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


10.9.2-MariaDB-1:10.9.2+maria~deb11


/* テーブルを作成する */

/* 商品 */
CREATE TABLE `products` (
	`id` INT(10) UNSIGNED NOT NULL,
	`name` VARCHAR(32) NOT NULL COLLATE 'utf8mb4_general_ci',
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

/* 商品販売ログ */
CREATE TABLE `sell_logs` (
	`product_id` INT(11) UNSIGNED NOT NULL,
	`date` DATE NOT NULL,
	`num` INT(11) NOT NULL,
	INDEX `product_id` (`product_id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

/* 商品返品ログ */
CREATE TABLE `return_logs` (
	`product_id` INT(11) NULL DEFAULT NULL,
	`date` DATE NULL DEFAULT NULL,
	`num` INT(11) NULL DEFAULT NULL,
	INDEX `product_id` (`product_id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

/* テストデータを挿入する */
INSERT INTO `products` (`id`, `name`) VALUES
	(1, 'りんご'),
	(2, 'いちご'),
	(3, 'バナナ');

INSERT INTO `sell_logs` (`product_id`, `date`, `num`) VALUES
	(1, '2023-01-01', 1),
	(1, '2023-01-02', 2),
	(2, '2023-01-01', 3),
	(2, '2023-01-02', 3),
	(3, '2023-01-01', 3),
	(3, '2023-01-02', 4);

INSERT INTO `return_logs` (`product_id`, `date`, `num`) VALUES
	(3, '2023-01-04', 1);

Records: 3  Duplicates: 0  Warnings: 0



Records: 6  Duplicates: 0  Warnings: 0


/* 商品、日付　別に集計する */
select
	p.`id`,
	p.`name`,
	sl.`date`,
	sl.`num` sell_num
from sell_logs sl
inner join products p on sl.`product_id` = p.`id`

group by
	p.`id`,
	sl.`date`

order by
	p.`id` asc,
	sl.`date` asc


id
name
date
sell_num


1
りんご
2023-01-01
1

1
りんご
2023-01-02
2

2
いちご
2023-01-01
3

2
いちご
2023-01-02
3

3
バナナ
2023-01-01
3

3
バナナ
2023-01-02
4


/* return_num を追加して、集計する*/
select
	p.`id`,
	p.`name`,
	sl.`date`,
	sl.`num` sell_num,
	(select rl.`num` from return_logs rl where rl.`product_id` = p.`id` and rl.`date` = sl.`date`) return_num
from sell_logs sl
inner join products p on sl.`product_id` = p.`id`

group by
	p.`id`,
	sl.`date`

order by
	p.`id` asc,
	sl.`date` asc


id
name
date
sell_num
return_num


1
りんご
2023-01-01
1
null

1
りんご
2023-01-02
2
null

2
いちご
2023-01-01
3
null

2
いちご
2023-01-02
3
null

3
バナナ
2023-01-01
3
null

3
バナナ
2023-01-02
4
null


/* 一時テーブルを作成して集計する */
create temporary table `tmp_dates`
  select
		tmp.`date`
	from (
    select `date` from sell_logs
    union all
    select `date` from return_logs
  ) tmp
;

select
	p.id,
	p.`name`,
	td.`date`,
	sl.`num`,
	rl.`num`
from `tmp_dates` td
left outer join sell_logs sl on sl.`date` = td.`date`
left outer join return_logs rl on rl.`date` = td.`date`
inner join products p on p.id = sl.product_id

group by
	p.`id`,
	td.`date`;

Records: 7  Duplicates: 0  Warnings: 0




id
name
date
num
num


1
りんご
2023-01-01
1
null

1
りんご
2023-01-02
2
null

2
いちご
2023-01-01
3
null

2
いちご
2023-01-02
3
null

3
バナナ
2023-01-01
3
null

3
バナナ
2023-01-02
4
null

id	name	date	sell_num
1	りんご	2023-01-01	1
1	りんご	2023-01-02	2
2	いちご	2023-01-01	3
2	いちご	2023-01-02	3
3	バナナ	2023-01-01	3
3	バナナ	2023-01-02	4

id	name	date	sell_num	return_num
1	りんご	2023-01-01	1	null
1	りんご	2023-01-02	2	null
2	いちご	2023-01-01	3	null
2	いちご	2023-01-02	3	null
3	バナナ	2023-01-01	3	null
3	バナナ	2023-01-02	4	null