SQL创建新的输出字段

1、准备数据

CREATE TABLE `t_stock_trans_dtl` (
  `trans_id` varchar(100) NOT NULL COMMENT '交易流水号',
  `stock_name` varchar(20) NOT NULL DEFAULT '' COMMENT '股票名称',
  `stock_code` varchar(10) NOT NULL DEFAULT '' COMMENT '股票代码',
  `opt_tm` datetime NOT NULL DEFAULT '1900-01-01 00:00:00' COMMENT '操作时间',
  `opt_typ` varchar(10) NOT NULL DEFAULT '' COMMENT '操作类型',
  `price` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '单价',
  `volume` int(11) NOT NULL DEFAULT '0' COMMENT '数量',
  `fee` decimal(18,5) NOT NULL DEFAULT '0.00000' COMMENT '手续费',
  PRIMARY KEY (`trans_id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk COMMENT='股票交易明细';

INSERT INTO `t_stock_trans_dtl` VALUES ('20010406000023','洪都航空','sh600316','2001-04-06 10:42:34','买入',22.40,600,3.36000),('20050826000303','洪都航空','sh600316','2005-08-26 13:40:31','卖出',6.98,-300,5.00000),('20130301000933','比亚迪','sz002594','2013-03-01 09:33:36','买入',25.25,600,3.78750),('20140307000393','比亚迪','sz002594','2014-03-07 13:39:30','买入',56.91,600,8.53650),('20150508000075','洪都航空','sh600316','2015-05-08 10:47:54','卖出',36.66,-300,2.74950),('20150724000395','比亚迪','sz002594','2015-07-24 13:19:55','买入',55.55,1200,16.66500),('20150902000941','大禹节水','sz300021','2015-09-02 09:44:18','买入',11.41,6100,17.40025),('20160318000314','大禹节水','sz300021','2016-03-18 13:01:44','卖出',12.62,-3000,9.46500),('20160930000303','比亚迪','sz002594','2016-09-30 13:40:31','卖出',56.98,-2400,34.18800),('20170310000425','比亚迪','sz002594','2017-03-10 14:22:54','买入',51.59,2000,25.79500),('20170630000132','大禹节水','sz300021','2017-06-30 11:53:20','买入',7.87,1300,2.55775),('20171110000142','大禹节水','sz300021','2017-11-10 11:44:24','买入',7.25,5000,9.06250),('20171229000410','比亚迪','sz002594','2017-12-29 14:51:04','买入',61.88,3600,55.69200);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

表如下：

2、对单个字段或者多个字段进行数值计算

SELECT stock_name, price, volume, volume / 100
FROM t_stock_trans_dtl;

SELECT stock_name, price, volume, volume * price
FROM t_stock_trans_dtl
1
2
3
4
5

如图所示：

3、数值计算

下方距离如图：

4、字段拼接

比如：如何按“洪都航空（sh600316）”展示股票名称和股票代码？

SELECT CONCAT(stock_name,'(',stock_code,')') 
FROM t_stock_trans_dtl
1
2

如图所示：

5、字段使用别名

SELECT CONCAT(stock_name,'(',stock_code,')') AS stock
FROM t_stock_trans_dtl
1
2

如图所示：

6、 CASE WHEN逻辑转换

case when 有两种 语法，如下：

下面通过这两种语法实现下方需求：

如何根据买入数量，区分大单、中单、小单？

case when 语法一

SELECT volume,
CASE 
	WHEN volume >= 3000 THEN
		'大单'
	WHEN volume > 600 AND volume < 3000 THEN
		'中单'
	ELSE
		'小单'
END 
FROM t_stock_trans_dtl
WHERE opt_typ = '买入';
1
2
3
4
5
6
7
8
9
10
11

操作及 输出如下图：

case when 语法二

SELECT volume,
CASE volume
	WHEN 600 THEN
		'你好'
	ELSE
		'大家好'
END 
FROM t_stock_trans_dtl WHERE opt_typ = '买入';
1
2
3
4
5
6
7
8

操作及 输出如下图：

case when 注意点

查询的值可以为任何值（例如可以： select *）

SELECT *,
CASE WHEN volume >= 300 THEN
		'大单'
	ELSE
		'小单'
END
FROM t_stock_trans_dtl
WHERE opt_typ = '买入';
1
2
3
4
5
6
7
8

操作及 输出如下图：

可以重命名

SELECT *,
CASE WHEN volume >= 300 THEN
		'大单'
	ELSE
		'小单'
END AS '单子'
FROM t_stock_trans_dtl
WHERE opt_typ = '买入';
1
2
3
4
5
6
7
8

操作及 输出如下图：

没有ELSE部分时，如果所有条件表达式都不满足，则返回NULL

# 没有ELSE部分时，如果所有条件表达式都不满足，则返回NULL。
# 每一个CASE WHEN都应该有ELSE分支！！！
SELECT volume,
CASE 
	WHEN volume >= 3000 THEN
		'大单'
	WHEN volume > 600 AND volume < 3000 THEN
		'中单'
END 
FROM t_stock_trans_dtl
WHERE opt_typ = '买入';
1
2
3
4
5
6
7
8
9
10
11

如图所示：

实操

对于买入的交易，交易数量为正数，而卖出的交易，交易数量为负
数。这就导致交易金额（交易价格*交易数量）的返回值有正有负。如何根据
交易类型做转换，交易金额全部返回正数？

SELECT volume, price,
CASE WHEN opt_typ = '买入' THEN price*volume
WHEN opt_typ = '卖出' THEN 0-price*volume
ELSE 0
END AS 交易
FROM t_stock_trans_dtl;
1
2
3
4
5
6

另一种写法

SELECT volume, price,
CASE opt_typ
WHEN '买入' THEN price*volume
WHEN '卖出' THEN 0-price*volume
ELSE 0
END AS 交易
FROM t_stock_trans_dtl;
1
2
3
4
5
6
7

case when 逻辑嵌套

两种语法格式，都可以多层嵌套，但不推荐多层嵌套。
建议最多嵌套两层并使用括号格式化SQL语句。