mysql - 基于选择值的 SQL 列

Question

希望最好通过一次查询来获取数据，但列是根据所选值动态变化的。

所以我的表看起来像这样(使用 MySql):

CREATE TABLE 'users' (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL
)

CREATE TABLE `income` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user` int(11) NOT NULL,
  `day` int(11) NOT NULL DEFAULT '0',
  `action_a` decimal(10,2) NOT NULL DEFAULT '0.00',
  `action_b` decimal(10,2) NOT NULL DEFAULT '0.00',
  `action_c` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`)
)

CREATE TABLE `given` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `day` int(11) DEFAULT NULL,
  `to_user` int(11) NOT NULL,
  `from_user` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`)
)

想要进入一个如下所示的查询表:

+------------+--------------+----------+----------+----------+
| day | action_a | action_b | action_c | user_B   | user_C   |
+------------+--------------+----------+----------+----------+
| 123 | 123.01   | 123.01   | 123.01   |  123.01  | -123.01  |
| 122 | 324.02   | 234.01   | 123.01   | -123.01  | -123.01  |
| 121 | 987.00   | 345.01   | 123.01   |  123.01  | -123.01  |
| 120 | 9393.01  | 456.01   | 123.01   | -123.01  | -123.01  |
| 119 | 0.00     | 567.01   | 123.01   | -123.01  | -123.01  |
 ...

用户列中的负值表示用户向其他用户捐赠

目前我正在执行 3 个单独的查询，然后合并数据，但我当前的查询如下所示:

获得基于行动的收入

Select action_a, action_b, action_c From income i Join users u on u.id=i.user Where u.name='%s' and i.day=%i Order by i.id desc

收到邮件

Select u2.name 'from', amount From given g Join users u on u.id = to_user Join users u2 on u2.id = from_user Where u.name='%s' and amount <> 0.0 Order by g.id 

得到给予

Select u2.name 'to', amount From given g Join users u on u.id = from_user Join users u2 on u2.id = to_user Where u.name='%s' and amount <> 0.0 Order by g.id desc

SQLFiddle link

Answer 1

标准化收入/收入行动表的示例如下:

CREATE TABLE income 
(income_id SERIAL PRIMARY KEY
,dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
,user INT NOT NULL
);

CREATE TABLE income_action 
(income_id INT NOT NULL
,action CHAR(1) NOT NULL
,value DECIMAL(10,2) NOT NULL DEFAULT '0.00',
,PRIMARY KEY(income_id,action)
);