A. 使用带有简单 CASE 函数的 SELECT 语句 在 SELECT 语句中,简单 CASE 函数仅检查是否相等,而不进行其它比较。
例子,使用 CASE 函数更改图书分类显示。
复制代码 代码如下:
USE pubs GO SELECT Category = CASE type WHEN 'popular_comp' THEN 'Popular Computing' WHEN 'mod_cook' THEN 'Modern Cooking' WHEN 'business' THEN 'Business' WHEN 'psychology' THEN 'Psychology' WHEN 'trad_cook' THEN 'Traditional Cooking' ELSE 'Not yet categorized' END, CAST(title AS varchar(25)) AS 'Shortened Title', price AS Price FROM titles WHERE price IS NOT NULL ORDER BY type, price COMPUTE AVG(price) BY type GO
注释,后来我试了一下不让用category=。
我使用的代码为:
复制代码 代码如下:
SELECT case gender WHEN 1 THEN 'NAN' WHEN 0 THEN 'NV' end as gender FROM t_swidy_day_nutrient
结果集:
Category Shortened Title Price ------------------- ------------------------- -------------------------- Business You Can Combat Computer S 2.99 Business Cooking with Computers: S 11.95 Business The Busy Executive's Data 19.99 Business Straight Talk About Compu 19.99
avg ========================== 13.73
Category Shortened Title Price ------------------- ------------------------- -------------------------- Modern Cooking The Gourmet Microwave 2.99 Modern Cooking Silicon Valley Gastronomi 19.99
avg ========================== 11.49
Category Shortened Title Price ------------------- ------------------------- -------------------------- Popular Computing Secrets of Silicon Valley 20.00 Popular Computing But Is It User Friendly? 22.95
avg ========================== 21.48
Category Shortened Title Price ------------------- ------------------------- -------------------------- Psychology Life Without Fear 7.00 Psychology Emotional Security: A New 7.99 Psychology Is Anger the Enemy? 10.95 Psychology Prolonged Data Deprivatio 19.99 Psychology Computer Phobic AND Non-P 21.59
avg ========================== 13.50
Category Shortened Title Price ------------------- ------------------------- -------------------------- Traditional Cooking Fifty Years in Buckingham 11.95 Traditional Cooking Sushi, Anyone? 14.99 Traditional Cooking Onions, Leeks, and Garlic 20.95
USE pubs GO SELECT 'Price Category' = CASE WHEN price IS NULL THEN 'Not yet priced' WHEN price < 10 THEN 'Very Reasonable Title' WHEN price >= 10 and price < 20 THEN 'Coffee Table Title' ELSE 'Expensive book!' END, CAST(title AS varchar(20)) AS 'Shortened Title' FROM titles ORDER BY price GO
结果集:
Price Category Shortened Title --------------------- -------------------- Not yet priced Net Etiquette Not yet priced The Psychology of Co Very Reasonable Title The Gourmet Microwav Very Reasonable Title You Can Combat Compu Very Reasonable Title Life Without Fear Very Reasonable Title Emotional Security: Coffee Table Title Is Anger the Enemy? Coffee Table Title Cooking with Compute Coffee Table Title Fifty Years in Bucki Coffee Table Title Sushi, Anyone? Coffee Table Title Prolonged Data Depri Coffee Table Title Silicon Valley Gastr Coffee Table Title Straight Talk About Coffee Table Title The Busy Executive's Expensive book! Secrets of Silicon V Expensive book! Onions, Leeks, and G Expensive book! Computer Phobic And Expensive book! But Is It User Frien
(18 row(s) affected)
C. 使用带有 SUBSTRING 和 SELECT 的 CASE 函数
例子,使用 CASE 和 THEN 生成一个有关作者、图书标识号和每个作者所著图书类型的列表。
首先,来看下 CASE 的语法。在一般的 SELECT 中,其语法如下:
复制代码 代码如下:
SELECT <myColumnSpec> = CASE WHEN <A> THEN <somethingA> WHEN <B> THEN <somethingB> ELSE <somethingE> END
以上代码,需要用具体的参数代替尖括号中的内容。
甚至还可以组合这些选项,添加一个 ORDER BY 子句,例如:
复制代码 代码如下:
USE pubs GO SELECT CASE WHEN price IS NULL THEN 'Unpriced' WHEN price < 10 THEN 'Bargain' WHEN price BETWEEN 10 and 20 THEN 'Average' ELSE 'Gift to impress relatives' END AS Range, Title FROM titles GROUP BY CASE WHEN price IS NULL THEN 'Unpriced' WHEN price < 10 THEN 'Bargain' WHEN price BETWEEN 10 and 20 THEN 'Average' ELSE 'Gift to impress relatives' END, Title ORDER BY CASE WHEN price IS NULL THEN 'Unpriced' WHEN price < 10 THEN 'Bargain' WHEN price BETWEEN 10 and 20 THEN 'Average' ELSE 'Gift to impress relatives' END, Title GO
除了选择自定义字段之外,在很多情况下 CASE 都非常有用。
稍加深入,还可以得到以前认为不可能得到的分组排序结果集。 使用CASE WHEN进行字符串替换处理
在SELECT查询中使用CASE WHEN
复制代码 代码如下:
/* mysql> SELECT Name, RatingID AS Rating, -> CASE RatingID -> WHEN 'R' THEN 'Under 17 requires an adult.' -> WHEN 'X' THEN 'No one 17 and under.' -> WHEN 'NR' THEN 'Use discretion when renting.' -> ELSE 'OK to rent to minors.' -> END AS Policy -> FROM DVDs -> ORDER BY Name; +-----------+--------+------------------------------+ | Name | Rating | Policy | +-----------+--------+------------------------------+ | Africa | PG | OK to rent to minors. | | Amadeus | PG | OK to rent to minors. | | Christmas | NR | Use discretion when renting. | | Doc | G | OK to rent to minors. | | Falcon | NR | Use discretion when renting. | | Mash | R | Under 17 requires an adult. | | Show | NR | Use discretion when renting. | | View | NR | Use discretion when renting. | +-----------+--------+------------------------------+ 8 rows in set (0.01 sec) */ Drop table DVDs; CREATE TABLE DVDs ( ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(60) NOT NULL, NumDisks TINYINT NOT NULL DEFAULT 1, RatingID VARCHAR(4) NOT NULL, StatID CHAR(3) NOT NULL ) ENGINE=INNODB; INSERT INTO DVDs (Name, NumDisks, RatingID, StatID) VALUES ('Christmas', 1, 'NR', 's1'), ('Doc', 1, 'G', 's2'), ('Africa', 1, 'PG', 's1'), ('Falcon', 1, 'NR', 's2'), ('Amadeus', 1, 'PG', 's2'), ('Show', 2, 'NR', 's2'), ('View', 1, 'NR', 's1'), ('Mash', 2, 'R', 's2'); SELECT Name, RatingID AS Rating, CASE RatingID WHEN 'R' THEN 'Under 17 requires an adult.' WHEN 'X' THEN 'No one 17 and under.' WHEN 'NR' THEN 'Use discretion when renting.' ELSE 'OK to rent to minors.' END AS Policy FROM DVDs ORDER BY Name;
山水闲人
2024-05-01 05:32:45
oracel数据库中,
sql语句
中没有if else
可以使用 decode() 或者 case when then else end
select decode('1','1','相等','不相等') from dual; select case when '1'='2' then '相等' else '不相等' end from dual;
逗逼太愉快
2024-05-01 00:28:45
SQL里的判断用case when。 例如: select product_id,product_type_id, (case when product_type_id=1 then 'Book' when product_type_id=2 then 'Video' when product_type_id=3 then 'DVD' when product_type_id=4 then 'CD' else 'Magazine' end) as type from products ;
虐爆托儿所
2024-05-01 06:00:53
oracle 的sql里面是没有直接的if else 语句的,可以用decode函数或者case when简单的代替。 比如 selcect decode(表1的列1,等于a,结果a,等于b,结果b,其他结果c) from 表1。
case when也可以。
SELECT SEX, ( CASE SEX WHEN 'F' THEN '男' WHEN 'M' THEN '女' ELSE ' ' END ) 性别 FROM table
真正的if else 在oracle的pl/sql里面,就和其它的程序判断一样,也有变量什么的
如一
2024-05-01 08:58:15
没有if else的用法。 一般来说,要实现if else的功能,可以使用下面3种方式实现: 1、可以使用case when 条件 then 结果1 else 结果2 end; 2、使用Decode()函数。 3、可以使用function来返回值,在function和procedure中可以使用if ..... end if;