add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
version()
8.0.30
CREATE TABLE category (
category_id INT NOT NULL,
product_id INT NOT NULL
);
CREATE TABLE product (
product_id INT NOT NULL,
product_price NUMERIC NULL,
product_old_price NUMERIC NULL
);
INSERT INTO product VALUES (1, 100, 90),(2, 1099.99, 990.99),(3, 1500.50, 1390.99),(2779, 2779.99, 2699.99),(100500, 100500.50, 100000.50);
INSERT INTO category VALUES (32, 1), (37, 2), (56, 3), (100500, 2779), (100500, 100500);
Records: 5  Duplicates: 0  Warnings: 8

Records: 5  Duplicates: 0  Warnings: 0
CREATE TABLE product_attr (
product_attr_id INT NOT NULL AUTO_INCREMENT,
product_id INT NOT NULL,
product_price NUMERIC NULL,
product_old_price NUMERIC NULL,
attr_7 INT NOT NULL,
attr_29 INT NOT NULL,
PRIMARY KEY (product_attr_id)
);
INSERT INTO product_attr (product_id, product_price, product_old_price, attr_7, attr_29)
WITH RECURSIVE
param AS
(
SELECT
461 AS attr_7_start, -- начальное значение для attr_7
14 AS attr_7_num, -- количество итераций в attr_7
1097 AS attr_29_start, -- начальное значение для attr_29
3 AS attr_29_num -- количество итераций в attr_29
),
attr29_iter (n) AS
(
SELECT 0
UNION ALL
SELECT n + 1 FROM attr29_iter, param WHERE n < attr_29_num -1
),
attr_7_iter (n) AS
(
SELECT 0
UNION ALL
SELECT n + 1 FROM attr_7_iter, param WHERE n < attr_7_num -1
),
gen_inc AS
(
SELECT i7.n as inc7, i29.n as inc29
FROM attr_7_iter i7, attr29_iter i29
)
SELECT p.product_id, p.product_price, p.product_old_price, attr_7_start + inc7 AS attr_7, attr_29_start + inc29 AS attr_29
FROM gen_inc, param, product p, category c
WHERE p.product_id = c.product_id
AND c.category_id NOT IN (214, 221, 220, 217, 216, 215, 48, 42, 40, 45, 46, 44, 50, 41, 43, 31, 178, 89, 47, 179, 177, 120, 121, 59, 58, 32, 37, 56, 53, 54, 55, 29, 28, 30, 176, 237, 113, 116, 175, 114, 196, 195, 197, 218, 219, 33, 93, 92, 91, 81, 90)
ORDER BY 1, 4, 5
;
Records: 84  Duplicates: 0  Warnings: 0
SELECT * FROM product_attr;
product_attr_id product_id product_price product_old_price attr_7 attr_29
1 2779 2780 2700 461 1097
2 2779 2780 2700 461 1098
3 2779 2780 2700 461 1099
4 2779 2780 2700 462 1097
5 2779 2780 2700 462 1098
6 2779 2780 2700 462 1099
7 2779 2780 2700 463 1097
8 2779 2780 2700 463 1098
9 2779 2780 2700 463 1099
10 2779 2780 2700 464 1097
11 2779 2780 2700 464 1098
12 2779 2780 2700 464 1099
13 2779 2780 2700 465 1097
14 2779 2780 2700 465 1098
15 2779 2780 2700 465 1099
16 2779 2780 2700 466 1097
17 2779 2780 2700 466 1098
18 2779 2780 2700 466 1099
19 2779 2780 2700 467 1097
20 2779 2780 2700 467 1098
21 2779 2780 2700 467 1099
22 2779 2780 2700 468 1097
23 2779 2780 2700 468 1098
24 2779 2780 2700 468 1099
25 2779 2780 2700 469 1097
26 2779 2780 2700 469 1098
27 2779 2780 2700 469 1099
28 2779 2780 2700 470 1097
29 2779 2780 2700 470 1098
30 2779 2780 2700 470 1099
31 2779 2780 2700 471 1097
32 2779 2780 2700 471 1098
33 2779 2780 2700 471 1099
34 2779 2780 2700 472 1097
35 2779 2780 2700 472 1098
36 2779 2780 2700 472 1099
37 2779 2780 2700 473 1097
38 2779 2780 2700 473 1098
39 2779 2780 2700 473 1099
40 2779 2780 2700 474 1097
41 2779 2780 2700 474 1098
42 2779 2780 2700 474 1099
43 100500 100501 100001 461 1097
44 100500 100501 100001 461 1098
45 100500 100501 100001 461 1099
46 100500 100501 100001 462 1097
47 100500 100501 100001 462 1098
48 100500 100501 100001 462 1099
49 100500 100501 100001 463 1097
50 100500 100501 100001 463 1098
51 100500 100501 100001 463 1099
52 100500 100501 100001 464 1097
53 100500 100501 100001 464 1098
54 100500 100501 100001 464 1099
55 100500 100501 100001 465 1097
56 100500 100501 100001 465 1098
57 100500 100501 100001 465 1099
58 100500 100501 100001 466 1097
59 100500 100501 100001 466 1098
60 100500 100501 100001 466 1099
61 100500 100501 100001 467 1097
62 100500 100501 100001 467 1098
63 100500 100501 100001 467 1099
64 100500 100501 100001 468 1097
65 100500 100501 100001 468 1098
66 100500 100501 100001 468 1099
67 100500 100501 100001 469 1097
68 100500 100501 100001 469 1098
69 100500 100501 100001 469 1099
70 100500 100501 100001 470 1097
71 100500 100501 100001 470 1098
72 100500 100501 100001 470 1099
73 100500 100501 100001 471 1097
74 100500 100501 100001 471 1098
75 100500 100501 100001 471 1099
76 100500 100501 100001 472 1097
77 100500 100501 100001 472 1098
78 100500 100501 100001 472 1099
79 100500 100501 100001 473 1097
80 100500 100501 100001 473 1098
81 100500 100501 100001 473 1099
82 100500 100501 100001 474 1097
83 100500 100501 100001 474 1098
84 100500 100501 100001 474 1099