我的表格 struct 如下:
category | user_id | value |
---|---|---|
A | 1 | 0.01 |
A | 2 | 0.05 |
A | 3 | 0.09 |
A | 4 | 0.12 |
B | 1 | 0.34 |
B | 2 | 0.27 |
B | 3 | 0.08 |
B | 4 | 0.12 |
实际表中还有更多行.这只是一个例子.
I want to make a table that keeps 'category', makes another column 'user_id_type' that labels even and odd, and another new column (value_sum) that sums all of the 'value' based on 'category' and 'user_id_type'.
因此,它将只有四行,包括'A'奇数'和总和,'A'偶数'和总和,'B'奇数'和总和,'B'偶数'和总和.
I want it to look like this:
category | user_id_type | value_sum |
---|---|---|
A | odd | 0.10 |
A | even | 0.17 |
B | odd | 0.42 |
B | even | 0.39 |
模式:
CREATE TABLE table_1 (
`category` VARCHAR(2),
`user_id` INT(2),
`value` DECIMAL(3,2)
);
INSERT INTO table_1
(`category`, `user_id`, `value`)
VALUES
('A', 1, 0.01),
('A', 2, 0.05),
('A', 3, 0.09),
('A', 4, 0.12),
('B', 1, 0.34),
('B', 2, 0.27),
('B', 3, 0.08),
('B', 4, 0.12)
;