MariaDB 10.4

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

select version();


version()


10.4.26-MariaDB-1:10.4.26+maria~deb10-log


CREATE TABLE `m_lank` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `low_limit` int(11) DEFAULT NULL,
  `up_limit` int(11) DEFAULT NULL
)
INSERT INTO `m_lank` (`id`, `name`, `low_limit`, `up_limit`) VALUES
(1, '残念', 0, 0),
(2, 'ブロンズ', 1, 4999),
(3, 'シルバー', 5000, 9999),
(4, 'ゴールド', 10000, 999999999);

Records: 4  Duplicates: 0  Warnings: 0


CREATE TABLE `m_member` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL
)
INSERT INTO `m_member` (`id`, `name`) VALUES
(1, '川島智弘'),
(2, '河田英毅'),
(3, '山田忠明'),
(4, '永峰弘万'),
(5, '永山智広');

Records: 5  Duplicates: 0  Warnings: 0


CREATE TABLE `m_product` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `price` int(11) DEFAULT NULL
)
INSERT INTO `m_product` (`id`, `name`, `price`) VALUES
(1, 'みかん', 100),
(2, 'すいか', 1500),
(3, 'バナナ', 60),
(4, 'ぶどう', 180),
(5, 'メロン', 500);

Records: 5  Duplicates: 0  Warnings: 0


CREATE TABLE `t_sales` (
  `id` int(11) NOT NULL,
  `m_id` int(11) DEFAULT NULL,
  `sales_date` date DEFAULT NULL
)
INSERT INTO `t_sales` (`id`, `m_id`, `sales_date`) VALUES
(1, 1, '2013-12-30'),
(2, 3, '2013-12-30'),
(3, 4, '2013-12-31'),
(4, 3, '2014-01-01'),
(5, 1, '2014-01-03'),
(6, 5, '2014-01-03'),
(7, 5, '2014-01-03'),
(8, 3, '2014-01-04'),
(9, 2, '2014-01-04');

Records: 9  Duplicates: 0  Warnings: 0


CREATE TABLE `t_sales_detail` (
  `id` int(11) NOT NULL,
  `line` int(11) NOT NULL,
  `p_id` int(11) DEFAULT NULL,
  `num` int(11) DEFAULT NULL
)
INSERT INTO `t_sales_detail` (`id`, `line`, `p_id`, `num`) VALUES
(1, 1, 2, 1),
(1, 2, 3, 2),
(1, 3, 5, 1),
(2, 1, 2, 3),
(3, 1, 1, 5),
(4, 1, 2, 1),
(4, 2, 3, 2),
(4, 3, 4, 1),
(4, 4, 5, 2),
(5, 1, 5, 1),
(6, 1, 4, 1),
(7, 1, 1, 2),
(8, 1, 1, 2),
(8, 2, 2, 5),
(8, 3, 3, 1),
(8, 4, 4, 2),
(9, 1, 1, 5);

Records: 17  Duplicates: 0  Warnings: 0


CREATE VIEW v_sales AS SELECT 
	ifnull(t.id, "NULL") AS "s_id",
    m.id AS "m_id",
    m.name AS "m_name",
    ifnull(p.id, "NULL") AS "p_id",
    ifnull(p.name, "NULL") AS "p_name",
    ifnull(d.num, "NULL") AS "num",
    ifnull(p.price, "NULL") AS "price"
FROM 
	m_member AS m
LEFT JOIN 
	t_sales AS t ON m.id = t.m_id
LEFT JOIN
	t_sales_detail AS d ON t.id = d.id
LEFT JOIN
	m_product AS p ON d.p_id = p.id
ORDER BY m_id, s_id;
SELECT * FROM v_sales;


s_id
m_id
m_name
p_id
p_name
num
price


1
1
川島智弘
2
すいか
1
1500

1
1
川島智弘
3
バナナ
2
60

1
1
川島智弘
5
メロン
1
500

5
1
川島智弘
5
メロン
1
500

9
2
河田英毅
1
みかん
5
100

2
3
山田忠明
2
すいか
3
1500

4
3
山田忠明
2
すいか
1
1500

4
3
山田忠明
3
バナナ
2
60

4
3
山田忠明
4
ぶどう
1
180

4
3
山田忠明
5
メロン
2
500

8
3
山田忠明
3
バナナ
1
60

8
3
山田忠明
2
すいか
5
1500

8
3
山田忠明
4
ぶどう
2
180

8
3
山田忠明
1
みかん
2
100

3
4
永峰弘万
1
みかん
5
100

6
5
永山智広
4
ぶどう
1
180

7
5
永山智広
1
みかん
2
100


SELECT m_name, SUM(num * price) AS total FROM
v_sales
GROUP BY m_id;


m_name
total


川島智弘
2620

河田英毅
500

山田忠明
15420

永峰弘万
500

永山智広
380


SELECT u.m_name, SUM(u.num * u.price) AS total,
(SELECT COUNT(*)
FROM v_sales AS x
WHERE x.m_id = u.m_id
AND (SELECT SUM(s1.num * s1.price) FROM v_sales AS s1 WHERE s1.m_id = x.m_id)
>
(SELECT SUM(s2.num * s2.price) FROM v_sales AS s2 WHERE s2.m_id = x.m_id)
) + 1 AS Rank
FROM v_sales AS u
GROUP BY u.m_id;


m_name
total
Rank


川島智弘
2620
1

河田英毅
500
1

山田忠明
15420
1

永峰弘万
500
1

永山智広
380
1


SELECT m_name, SUM(num * price) AS total, RANK() OVER(ORDER BY SUM(num * price) DESC) FROM
v_sales
GROUP BY m_id;


m_name
total
RANK() OVER(ORDER BY SUM(num * price) DESC)


川島智弘
2620
2

河田英毅
500
3

山田忠明
15420
1

永峰弘万
500
3

永山智広
380
5


SELECT t1.m_name,MAX(t1.total),COUNT(t2.m_name)+1 as RANK
FROM
(SELECT m_name, SUM(num * price) AS total FROM
v_sales
GROUP BY m_id) t1
LEFT JOIN
(SELECT m_name, SUM(num * price) AS total FROM
v_sales
GROUP BY m_id) t2
ON t1.total<t2.total
GROUP BY t1.m_name
ORDER BY 3
;


m_name
MAX(t1.total)
RANK


山田忠明
15420
1

川島智弘
2620
2

河田英毅
500
3

永峰弘万
500
3

永山智広
380
5

s_id	m_id	m_name	p_id	p_name	num	price
1	1	川島智弘	2	すいか	1	1500
1	1	川島智弘	3	バナナ	2	60
1	1	川島智弘	5	メロン	1	500
5	1	川島智弘	5	メロン	1	500
9	2	河田英毅	1	みかん	5	100
2	3	山田忠明	2	すいか	3	1500
4	3	山田忠明	2	すいか	1	1500
4	3	山田忠明	3	バナナ	2	60
4	3	山田忠明	4	ぶどう	1	180
4	3	山田忠明	5	メロン	2	500
8	3	山田忠明	3	バナナ	1	60
8	3	山田忠明	2	すいか	5	1500
8	3	山田忠明	4	ぶどう	2	180
8	3	山田忠明	1	みかん	2	100
3	4	永峰弘万	1	みかん	5	100
6	5	永山智広	4	ぶどう	1	180
7	5	永山智広	1	みかん	2	100

m_name	total
川島智弘	2620
河田英毅	500
山田忠明	15420
永峰弘万	500
永山智広	380