mysql - 根据列名计算总和

Question

是否可以根据列名将一个值链接到另一个值？例如在下面的非标准化表中，如果 state_cd_1 是“TX”，我需要考虑另一个表中 state_amt_1 列的值，如果 state_cd_2 也是“TX”，则将该值添加到 state_amt_2。

create table states (state_id int not null auto_increment, state_cd_1 varchar(100), state_cd_2 varchar(100), state_cd_3 varchar(100), state_cd_4 varchar(100), state_cd_5 varchar(100), primary key (state_id));
insert into states values (NULL, 'TX', NULL, NULL, NULL , 'OR');
insert into states values (NULL, 'OR', 'UT', NULL, NULL , NULL);
insert into states values (NULL, 'AZ', 'OR', 'UT', NULL , 'AZ');

create table amount (amt_id int not null auto_increment, state_amt_1 varchar(100), state_amt_2 varchar(100), state_amt_3 varchar(100), state_amt_4 varchar(100), state_amt_5 varchar(100), primary key (amt_id));
insert into amount  values (NULL, '100', 900, NULL, NULL , '200');
insert into amount values (NULL, '250', '300', NULL, NULL , NULL);
insert into amount values (NULL, '400', '290', '350', NULL , '450');

(11:34) mysql>select * from states;
+----------+------------+------------+------------+------------+------------+
| state_id | state_cd_1 | state_cd_2 | state_cd_3 | state_cd_4 | state_cd_5 |
+----------+------------+------------+------------+------------+------------+
|        1 | TX         | NULL       | NULL       | NULL       | OR         | 
|        2 | OR         | UT         | NULL       | NULL       | NULL       | 
|        3 | AZ         | OR         | UT         | NULL       | AZ         | 
+----------+------------+------------+------------+------------+------------+

(11:34) mysql>select * from amount;
+--------+-------------+-------------+-------------+-------------+-------------+
| amt_id | state_amt_1 | state_amt_2 | state_amt_3 | state_amt_4 | state_amt_5 |
+--------+-------------+-------------+-------------+-------------+-------------+
|      1 | 100         | 900         | NULL        | NULL        | 200         | 
|      2 | 250         | 300         | NULL        | NULL        | NULL        | 
|      3 | 400         | 290         | 350         | NULL        | 450         | 
+--------+-------------+-------------+-------------+-------------+-------------+

Expected result:

TX 100
OR 740
UT 650
AZ 850

OR

TX OR UT AZ
100 740 650 850

How to calculate:

TX: 100
OR: 200+250+290=740
UT: 300+350=650
AZ: 400+450=850

注意:州名可能会改变。

Answer 1

这很丑陋，但你可以用这样的联合来做到这一点:

SELECT st, sum(amt) FROM (
(
 SELECT s.state_cd_1 AS st, a.state_amt_1 AS amt
 FROM states AS s JOIN amount AS a ON s.state_id = a.amt_id
 WHERE s.state_cd_1 IS NOT NULL AND a.state_amt_1 IS NOT NULL
) UNION ALL (
 SELECT s.state_cd_2 AS st, a.state_amt_2 AS amt
 FROM states AS s JOIN amount AS a ON s.state_id = a.amt_id
 WHERE s.state_cd_2 IS NOT NULL AND a.state_amt_2 IS NOT NULL
) UNION ALL (
 SELECT s.state_cd_3 AS st, a.state_amt_3 AS amt
 FROM states AS s JOIN amount AS a ON s.state_id = a.amt_id
 WHERE s.state_cd_3 IS NOT NULL AND a.state_amt_3 IS NOT NULL
) UNION ALL (
 SELECT s.state_cd_4 AS st, a.state_amt_4 AS amt
 FROM states AS s JOIN amount AS a ON s.state_id = a.amt_id 
 WHERE s.state_cd_4 IS NOT NULL AND a.state_amt_4 IS NOT NULL
) UNION ALL (
 SELECT s.state_cd_5 AS st, a.state_amt_5 AS amt 
 FROM states AS s JOIN amount AS a ON s.state_id = a.amt_id 
 WHERE s.state_cd_5 IS NOT NULL AND a.state_amt_5 IS NOT NULL
)) AS joined GROUP BY st;