使用CASE WHEN进行字符串替换处理
来源:互联网 发布:公司大数据 编辑:程序博客网 时间:2024/06/03 17:47
/*
02
03
mysql>
select
*
from
sales;
04
+
-----+------------+--------+--------+--------+------+------------+
05
| num |
name
| winter | spring | summer | fall | category |
06
+
-----+------------+--------+--------+--------+------+------------+
07
| 1 | Java | 1067 | 200 | 150 | 267 | Holiday |
08
| 2 | C | 970 | 770 | 531 | 486 | Profession |
09
| 3 | JavaScript | 53 | 13 | 21 | 856 | Literary |
10
| 4 | SQL | 782 | 357 | 168 | 250 | Profession |
11
| 5 | Oracle | 589 | 795 | 367 | 284 | Holiday |
12
| 6 | MySQL | 953 | 582 | 336 | 489 | Literary |
13
| 7 | Cplus | 752 | 657 | 259 | 478 | Literary |
14
| 8 | Python | 67 | 23 | 83 | 543 | Holiday |
15
| 9 | PHP | 673 | 48 | 625 | 52 | Profession |
16
+
-----+------------+--------+--------+--------+------+------------+
17
9
rows
in
set
(0.01 sec)
18
19
mysql>
SELECT
name
AS
Name
,
20
->
CASE
category
21
->
WHEN
"Holiday"
THEN
"Seasonal"
22
->
WHEN
"Profession"
THEN
"Bi_annual"
23
->
WHEN
"Literary"
THEN
"Random"
END
AS
"Pattern"
24
->
FROM
sales;
25
+
------------+-----------+
26
|
Name
| Pattern |
27
+
------------+-----------+
28
| Java | Seasonal |
29
| C | Bi_annual |
30
| JavaScript | Random |
31
| SQL | Bi_annual |
32
| Oracle | Seasonal |
33
| MySQL | Random |
34
| Cplus | Random |
35
| Python | Seasonal |
36
| PHP | Bi_annual |
37
+
------------+-----------+
38
9
rows
in
set
(0.00 sec)
39
40
41
*/
42
Drop
table
sales;
43
44
CREATE
TABLE
sales(
45
num MEDIUMINT
NOT
NULL
AUTO_INCREMENT,
46
name
CHAR
(20),
47
winter
INT
,
48
spring
INT
,
49
summer
INT
,
50
fall
INT
,
51
category
CHAR
(13),
52
primary
key
(num)
53
)type=MyISAM;
54
55
56
insert
into
sales value(1,
'Java'
, 1067 , 200, 150, 267,
'Holiday'
);
57
insert
into
sales value(2,
'C'
,970,770,531,486,
'Profession'
);
58
insert
into
sales value(3,
'JavaScript'
,53,13,21,856,
'Literary'
);
59
insert
into
sales value(4,
'SQL'
,782,357,168,250,
'Profession'
);
60
insert
into
sales value(5,
'Oracle'
,589,795,367,284,
'Holiday'
);
61
insert
into
sales value(6,
'MySQL'
,953,582,336,489,
'Literary'
);
62
insert
into
sales value(7,
'Cplus'
,752,657,259,478,
'Literary'
);
63
insert
into
sales value(8,
'Python'
,67,23,83,543,
'Holiday'
);
64
insert
into
sales value(9,
'PHP'
,673,48,625,52,
'Profession'
);
65
66
select
*
from
sales;
67
68
69
SELECT
name
AS
Name
,
70
CASE
category
71
WHEN
"Holiday"
THEN
"Seasonal"
72
WHEN
"Profession"
THEN
"Bi_annual"
73
WHEN
"Literary"
THEN
"Random"
END
AS
"Pattern"
74
FROM
sales;
[代码] 简单语句
1
SELECT
CASE
WHEN
10*2=30
THEN
'30 correct'
2
WHEN
10*2=40
THEN
'40 correct'
3
ELSE
'Should be 10*2=20'
4
END
;
[代码] 多重表达式
1
SELECT
CASE
10*2
2
WHEN
20
THEN
'20 correct'
3
WHEN
30
THEN
'30 correct'
4
WHEN
40
THEN
'40 correct'
5
END
;
[代码] 在SELECT查询中使用CASE WHEN
01
/*
02
mysql>
SELECT
Name
, RatingID
AS
Rating,
03
->
CASE
RatingID
04
->
WHEN
'R'
THEN
'Under 17 requires an adult.'
05
->
WHEN
'X'
THEN
'No one 17 and under.'
06
->
WHEN
'NR'
THEN
'Use discretion when renting.'
07
->
ELSE
'OK to rent to minors.'
08
->
END
AS
Policy
09
->
FROM
DVDs
10
->
ORDER
BY
Name
;
11
+
-----------+--------+------------------------------+
12
|
Name
| Rating | Policy |
13
+
-----------+--------+------------------------------+
14
| Africa | PG | OK
to
rent
to
minors. |
15
| Amadeus | PG | OK
to
rent
to
minors. |
16
| Christmas | NR | Use discretion
when
renting. |
17
| Doc | G | OK
to
rent
to
minors. |
18
| Falcon | NR | Use discretion
when
renting. |
19
| Mash | R | Under 17 requires an adult. |
20
| Show | NR | Use discretion
when
renting. |
21
|
View
| NR | Use discretion
when
renting. |
22
+
-----------+--------+------------------------------+
23
8
rows
in
set
(0.01 sec)
24
25
26
*/
27
28
Drop
table
DVDs;
29
30
CREATE
TABLE
DVDs (
31
ID
SMALLINT
NOT
NULL
AUTO_INCREMENT
PRIMARY
KEY
,
32
Name
VARCHAR
(60)
NOT
NULL
,
33
NumDisks TINYINT
NOT
NULL
DEFAULT
1,
34
RatingID
VARCHAR
(4)
NOT
NULL
,
35
StatID
CHAR
(3)
NOT
NULL
36
)
37
ENGINE=INNODB;
38
39
INSERT
INTO
DVDs (
Name
, NumDisks, RatingID, StatID)
40
VALUES
(
'Christmas'
, 1,
'NR'
,
's1'
),
41
(
'Doc'
, 1,
'G'
,
's2'
),
42
(
'Africa'
, 1,
'PG'
,
's1'
),
43
(
'Falcon'
, 1,
'NR'
,
's2'
),
44
(
'Amadeus'
, 1,
'PG'
,
's2'
),
45
(
'Show'
, 2,
'NR'
,
's2'
),
46
(
'View'
, 1,
'NR'
,
's1'
),
47
(
'Mash'
, 2,
'R'
,
's2'
);
48
49
50
SELECT
Name
, RatingID
AS
Rating,
51
CASE
RatingID
52
WHEN
'R'
THEN
'Under 17 requires an adult.'
53
WHEN
'X'
THEN
'No one 17 and under.'
54
WHEN
'NR'
THEN
'Use discretion when renting.'
55
ELSE
'OK to rent to minors.'
56
END
AS
Policy
57
FROM
DVDs
58
ORDER
BY
Name
;
- 使用CASE WHEN进行字符串替换处理
- MySQL 的 CASE WHEN 语句 进行字符串替换处理
- 使用case when进行行转列
- oracle substr、instr以及case when 的连用截取字符串以及替换(repace)的使用
- 使用 case when进行行列转换
- 使用 case when进行行列转换
- 使用 case when进行行列转换
- sqlserver使用order by case when进行优先级排序
- sqlserver使用order by case when进行优先级排序
- excel 中如何使用函数替代case when 进行转换
- case ..when..的使用
- CASE WHEN使用
- SQL CASE WHEN使用
- SQL CASE WHEN使用
- sql case when 使用
- Oracle-CASE WHEN使用
- case when使用
- case...when语句使用
- awk正则匹配列中指定的字符
- java 计算PMT公式
- Oracle to_date
- POJ 1836(双向LIS)
- 【C++】拷贝构造函数与赋值操作符的本质性区别分析
- 使用CASE WHEN进行字符串替换处理
- windows下搭建cocos2dx Android开发环境
- 画椭圆 - HTML5 Canvas 作图
- 拷贝构造函数的疑问
- 设计模式之简单工厂模式
- WSAEventSelect
- jquery中ajax方法使用json需注意的小问题
- QTP录制过程中关于浏览器设定的问题
- 【Div+Css笔记】0x04 进阶准备