sql中行列互換

发表于 | 分类于 | 阅读次数

准备

1
2
3
4
5
CREATE TABLE tb (
	name VARCHAR ( 10 ),
	course VARCHAR ( 10 ),
	score INT
);

1
2
3
4
5
6
insert into tb values('张三' , '语文' , 74);
insert into tb values('张三' , '数学' , 83); 
insert into tb values('张三' , '物理' , 93);
insert into tb values('李四' , '语文' , 74);
insert into tb values('李四' , '数学' , 84);
insert into tb values('李四' , '物理' , 94);

行->列

simple

1
2
3
4
5
6
7
8
9
SELECT
	name AS name,
	max( CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
	max( CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
	max( CASE course WHEN '物理' THEN score ELSE 0 END ) 物理 
FROM
	tb
GROUP BY
	name

1
2
3
4
5
max( CASE course WHEN '语文' THEN score ELSE 0 END )

SUM(IF(course="语文",score,0))

CASE  WHEN signal_type='macd_15_dea_up_0' and op_type='buy' THEN 1 WHEN  signal_type='macd_15_dea_up_0' and op_type='sell' THEN -1  ELSE 0 END as macd_15_dea_up_0

1
IF(expr1,expr2,expr3)

if expr1 then expr2 else expr3

1
IFNULL(expr1,expr2)

if expr1 not NULL then expr1 else expr2

1
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END