2005中的ISNULL函数的异议,CUBRID学习笔记

coalesce( expression [ ,…n ] )再次回到其参数中率先个非空表明式。

cubrid的中sql查询语法


SELECT [ ] <select_expressions> [{TO | INTO}
<variable_comma_list>] [FROM
<extended_table_specification_comma_list>] [WHERE
<search_condition>] [GROUP BY {col_name | expr} [ASC |
DESC], …[WITH ROLLUP]] [HAVING <search_condition> ]
[ORDER BY {col_name | expr} [ASC | DESC], … [997755.com澳门葡京,NULLS {FIRST |
LAST}] [LIMIT [offset,] row_count] [USING INDEX { index_name
[,index_name, …] | NONE }] [FOR UPDATE [OF
<spec_name_comma_list>]]

<qualifier> ::= ALL | DISTINCT | DISTINCTROW | UNIQUE

<select_expressions> ::= * | <expression_comma_list> | *, <expression_comma_list>

<variable_comma_list> ::= [:] identifier, [:] identifier, ...

<extended_table_specification_comma_list> ::=
    <table_specification>   [
                                {, <table_specification> } ... |
                                <join_table_specification> ... |
                                <join_table_specification2> ...
                            ]

<table_specification> ::= <single_table_spec> [] [WITH
(<lock_hint>)] | <metaclass_specification> [ ] | |
TABLE ( )

::= [AS] [(<identifier_comma_list>)]

<single_table_spec> ::= [ONLY] <table_name> | ALL
<table_name> [ EXCEPT <table_name> ]

<metaclass_specification> ::= CLASS <class_name>

<join_table_specification> ::= [INNER | {LEFT | RIGHT}
[OUTER]] JOIN <table_specification> ON
<search_condition>

<join_table_specification2> ::= CROSS JOIN
<table_specification>

<lock_hint> ::= READ UNCOMMITTED

  • 或者看例子吗

distinct 去重 SELECT DISTINCT host_nation FROM olympic;

分页 SELECT host_year as col1, host_nation as col2 FROM olympic ORDER
BY col2 LIMIT 5;

SELECT CONCAT(host_nation, ‘, ‘, host_city) AS host_place FROM
olympic ORDER BY host_place LIMIT 5;

还可以 SELECT 1+1 AS sum_value;

骨子里就类似子查询

SELECT SUM (n) FROM (SELECT gold FROM participant WHERE nation_code =
‘KOR’ UNION ALL SELECT silver FROM participant WHERE nation_code =
‘JPN’) AS t(n);

换个姿态 SELECT nation_code, host_year, gold FROM participant p WHERE
gold > (SELECT AVG(s) FROM (SELECT silver + bronze FROM participant
WHERE nation_code = p.nation_code AND silver > 0 AND bronze > 0)
AS t(s));

where条件

WHERE <search_condition>

<search_condition> ::=
    <comparison_predicate>
    <between_predicate>
    <exists_predicate>
    <in_predicate>
    <null_predicate>
    <like_predicate>
    <quantified_predicate>
    <set_predicate>

  比较运算

  = <> != > < >= <=

  成立是1 不成立是0 

  any  some  all运算

  any 任一个条件符合 some 是一部分(文档暂缺)

  SELECT * FROM condition_tbl WHERE dept_name = ANY{'devel','sales'};

  all  条件全部符合

  SELECT * FROM condition_tbl WHERE salary > ALL{3000000, 4000000, NULL};


  SELECT * FROM condition_tbl WHERE (

(0.9 * salary) < ALL (SELECT salary FROM condition_tbl WHERE
dept_name = ‘devel’) );

between 运算符

SELECT * FROM condition_tbl WHERE salary BETWEEN 3000000 AND 4000000;

SELECT * FROM condition_tbl WHERE (salary >= 3000000) AND (salary ⇐
4000000);

SELECT * FROM condition_tbl WHERE salary NOT BETWEEN 3000000 AND
4000000;

SELECT * FROM condition_tbl WHERE name BETWEEN ‘A’ AND ‘E’;

exists 运算符

SELECT ‘raise’ FROM db_root WHERE EXISTS( SELECT * FROM condition_tbl
WHERE salary < 2500000);

SELECT ‘raise’ FROM db_root WHERE NOT EXISTS( SELECT * FROM
condition_tbl WHERE salary < 2500000);

in 运算符

SELECT * FROM condition_tbl WHERE dept_name IN {‘devel’,’sales’};

SELECT * FROM condition_tbl WHERE dept_name = ANY{‘devel’,’sales’};

SELECT * FROM condition_tbl WHERE dept_name NOT IN {‘devel’,’sales’};

isnull

SELECT * FROM condition_tbl WHERE salary IS NULL;

SELECT * FROM condition_tbl WHERE salary IS NOT NULL;

2005中的ISNULL函数的异议,CUBRID学习笔记。SELECT * FROM condition_tbl WHERE salary = NULL;

like 运算符 转义用
SELECT * FROM condition_tbl WHERE name LIKE ‘%s%’;

SELECT * FROM condition_tbl WHERE UPPER(name) LIKE ‘_O%’;

SELECT * FROM condition_tbl WHERE name LIKE ‘___’;

REGEXP, RLIKE 运算符 那么些事例用的少,转码相比 SELECT (‘a’ collate
utf8_en_ci REGEXP BINARY ‘A’ collate utf8_en_ci); 0 SELECT (‘a’
collate utf8_en_cs REGEXP BINARY ‘A’ collate utf8_en_cs); 0 SELECT
(‘a’ COLLATE iso88591_bin REGEXP ‘A’ COLLATE iso88591_bin); 1 SELECT
(‘a’ COLLATE iso88591_bin REGEXP BINARY ‘A’ COLLATE iso88591_bin); 0
紧要看那几个 支持正则表达式喽 SELECT name FROM athlete where name REGEXP
‘^[a-d]’;

下面更像是演示正则表达式 匹配特殊的字符 SELECT (‘new\nline’ REGEXP ‘new
line’);

SELECT (‘cubrid dbms’ REGEXP ‘^cub’); SELECT (‘this is cubrid dbms’
REGEXP ‘dbms$’);

SELECT (‘cubrid dbms’ REGEXP ‘^c.*$’); SELECT (‘Aaaapricot’ REGEXP
‘^A+pricot’); SELECT (‘Apricot’ REGEXP ‘^Aa?pricot’); SELECT (‘Aapricot’
REGEXP ‘^Aa?pricot’); (‘Aapricot’ regexp ‘^Aa?pricot’)

1 代表建立 SELECT (‘Aaapricot’ REGEXP ‘^Aa?pricot’);

(‘Aaapricot’ regexp ‘^Aa?pricot’)

0 — (cub)* : match zero or more instances of the sequence abc.

SELECT (‘cubcub’ REGEXP ‘^(cub)*$’);

(‘cubcub’ regexp ‘^(cub)*$’)

1 — [a-dX], [^a-dX] : matches any character that is (or is not, if
^ is used) either a, b, c, d or X.

SELECT (‘aXbc’ REGEXP ‘^[a-dXYZ]+’);

(‘aXbc’ regexp ‘^[a-dXYZ]+’)

1 SELECT (‘strike’ REGEXP ‘^[^a-dXYZ]+$’);

(‘strike’ regexp ‘^[^a-dXYZ]+$’)

1

case 运算符

SELECT a, CASE WHEN a=1 THEN ‘one’ WHEN a=2 THEN ‘two’ ELSE ‘other’ END
FROM case_tbl;

结果 a case when a=1 then ‘one’ when a=2 then ‘two’ else ‘other’ end

        1  'one'
        2  'two'
        3  'other'
     NULL  'other'


     SELECT a,
   CASE a WHEN 1 THEN 'one'
          WHEN 2 THEN 'two'
          ELSE 'other'
   END

FROM case_tbl;

SELECT a, CASE WHEN a=1 THEN 1 WHEN a=2 THEN 1.2345 ELSE 1.234567890 END
FROM case_tbl;

SELECT a, CASE WHEN a=1 THEN ‘one’ WHEN a=2 THEN ‘two’ ELSE 1.2345 END
FROM case_tbl; //报错,类型转换错误

COALESCE 函数 对null处理的函数 类似 CASE WHEN a IS NOT NULL THEN a ELSE
b END

当a的值为null的时候 返回10.0000 SELECT a, COALESCE(a, 10.0000) FROM
case_tbl;

decode 函数 类似 CASE WHEN a = b THEN c WHEN a = d THEN e ELSE f END

参数有五个, 首个 可以是表达式要依旧相比的值 如下例的a 第四个 结果
如下例的1 ,2 第五个 默认值 如other a 列 1 2 3 NULL

执行
SELECT a, DECODE(a, 1, ‘one’, 2, ‘two’, ‘other’) FROM case_tbl; 结果 1
‘one’ 2 ‘two’ 3 ‘other’ NULL ‘other’

a为1 等于one a为2 相当于two a为3和null ,没有符合条件的项,使用默许值other

SELECT a, DECODE(a, 1, 1, 2, 1.2345, 1.234567890) FROM case_tbl;
注意以下的结果,数字都是单精度的.隐式转换了. 以默许结果的连串为准. 1
1.000000000 2 1.234500000 3 1.234567890 NULL 1.234567890
上面的那几个会破产,因为one无法转为 double SELECT a, DECODE(a, 1, ‘one’, 2,
‘two’, 1.2345) FROM case_tbl;

GREATEST 函数 获取最大的值

SELECT gold, silver , bronze, GREATEST (gold, silver, bronze) FROM
participant WHERE nation_code = ‘KOR’;

结果是几个参数列每行值中的的最大值 gold silver bronze greatest(gold,
silver, bronze)

        9           12            9                              12
        8           10           10                              10
        7           15            5                              15
       12            5           12                              12
       12           10           11                              12

     c#,net,cubrid,教程,学习,笔记欢迎转载 ,转载时请保留作者信息。本文版权归本人所有,如有任何问题,请与我联系wang2650@sohu.com 。 过错  

if函数 八个参数, 首个表明式重返真假 第一个象征确实时候回来的值
第五个代表假的时候回来的值

类似 CASE WHEN a IS TRUE THEN b ELSE c END

SELECT a, IF(a=1, ‘one’, ‘other’) FROM case_tbl;

IFNULL NVL函数
给null的列设默认值,注意再次来到的体系,取决于默许值的类型.ifnull和Nvl类似.不过Nvl匡助集合类型.
类似 CASE WHEN a IS NULL THEN b ELSE a END

例如SELECT a, IFNULL(a, ‘UNKNOWN’) FROM case_tbl; 结果 1 ‘1’ 2 ‘2’ 3
‘3’ NULL ‘UNKNOWN’

ISNULL函数 重回整数 1或者0

LEAST函数 参数可以是几个表明式,重回最小的值, 参数是null则赶回null

SELECT gold, silver , bronze, LEAST(gold, silver, bronze) FROM
participant WHERE nation_code = ‘KOR’; 结果 gold silver bronze
least(gold, silver, bronze)

        9           12            9                            9
        8           10           10                            8
        7           15            5                            5
       12            5           12                            5
       12           10           11                           10

NULLIF 函数 相等重回null 否则原样重临 类似CASE WHEN a = b THEN NULL ELSE
a END

      SELECT a, NULLIF(a, 1) FROM case_tbl; 
      结果 a是1的时候返回null
       a  nullif(a, 1)

        1          NULL
        2             2
        3             3
     NULL          NULL

NVL2(expr1, expr2, expr3) 函数
第二个表明式不对等null的是或不是重临第一个表明式,等于null再次来到第多个表明式
SELECT a, NVL2(a, a+1, 10.5678) FROM case_tbl;

     a  nvl2(a, a+1, 10.5678)

        1                      2
        2                      3
        3                      4
     NULL                     11

cubrid的中sql查询语法


SELECT [ ] <select_expressions> [{TO | INTO}
<variable_comma_list>] [FROM
<extended_table_specification_comma_list>] [WHERE
<search_condition>] [GROUP BY {col_name | expr} [ASC |
DESC], …[WITH ROLLUP]] [HAVING <search_condition> ]
[ORDER BY {col_name | expr} [ASC | DESC], … [NULLS {FIRST |
LAST}] [LIMIT [offset,] row_count] [USING INDEX { index_name
[,index_name, …] | NONE }] [FOR UPDATE [OF
<spec_name_comma_list>]]

<qualifier> ::= ALL | DISTINCT | DISTINCTROW | UNIQUE

<select_expressions> ::= * | <expression_comma_list> | *, <expression_comma_list>

<variable_comma_list> ::= [:] identifier, [:] identifier, ...

<extended_table_specification_comma_list> ::=
    <table_specification>   [
                                {, <table_specification> } ... |
                                <join_table_specification> ... |
                                <join_table_specification2> ...
                            ]

<table_specification> ::= <single_table_spec> [] [WITH
(<lock_hint>)] | <metaclass_specification> [ ] | |
TABLE ( )

::= [AS] [(<identifier_comma_list>)]

<single_table_spec> ::= [ONLY] <table_name> | ALL
<table_name> [ EXCEPT <table_name> ]

<metaclass_specification> ::= CLASS <class_name>

<join_table_specification> ::= [INNER | {LEFT | RIGHT}
[OUTER]] JOIN <table_specification> ON
<search_condition>

<join_table_specification2> ::= CROSS JOIN
<table_specification>

<lock_hint> ::= READ UNCOMMITTED

  • 抑或看例子吗

distinct 去重 SELECT DISTINCT host_nation FROM olympic;

分页 SELECT host_year as col1, host_nation as col2 FROM olympic ORDER
BY col2 LIMIT 5;

SELECT CONCAT(host_nation, ‘, ‘, host_city) AS host_place FROM
olympic ORDER BY host_place LIMIT 5;

还可以 SELECT 1+1 AS sum_value;

实际就类似子查询

SELECT SUM (n) FROM (SELECT gold FROM participant WHERE nation_code =
‘KOR’ UNION ALL SELECT silver FROM participant WHERE nation_code =
‘JPN’) AS t(n);

换个姿态 SELECT nation_code, host_year, gold FROM participant p WHERE
gold > (SELECT AVG(s) FROM (SELECT silver + bronze FROM participant
WHERE nation_code = p.nation_code AND silver > 0 AND bronze > 0)
AS t(s));

where条件

WHERE <search_condition>

<search_condition> ::=
    <comparison_predicate>
    <between_predicate>
    <exists_predicate>
    <in_predicate>
    <null_predicate>
    <like_predicate>
    <quantified_predicate>
    <set_predicate>

  比较运算

  = <> != > < >= <=

  成立是1 不成立是0 

  any  some  all运算

  any 任一个条件符合 some 是一部分(文档暂缺)

  SELECT * FROM condition_tbl WHERE dept_name = ANY{'devel','sales'};

  all  条件全部符合

  SELECT * FROM condition_tbl WHERE salary > ALL{3000000, 4000000, NULL};


  SELECT * FROM condition_tbl WHERE (

(0.9 * salary) < ALL (SELECT salary FROM condition_tbl WHERE
dept_name = ‘devel’) );

between 运算符

SELECT * FROM condition_tbl WHERE salary BETWEEN 3000000 AND 4000000;

SELECT * FROM condition_tbl WHERE (salary >= 3000000) AND (salary ⇐
4000000);

SELECT * FROM condition_tbl WHERE salary NOT BETWEEN 3000000 AND
4000000;

SELECT * FROM condition_tbl WHERE name BETWEEN ‘A’ AND ‘E’;

exists 运算符

SELECT ‘raise’ FROM db_root WHERE EXISTS( SELECT * FROM condition_tbl
WHERE salary < 2500000);

SELECT ‘raise’ FROM db_root WHERE NOT EXISTS( SELECT * FROM
condition_tbl WHERE salary < 2500000);

in 运算符

SELECT * FROM condition_tbl WHERE dept_name IN {‘devel’,’sales’};

SELECT * FROM condition_tbl WHERE dept_name = ANY{‘devel’,’sales’};

SELECT * FROM condition_tbl WHERE dept_name NOT IN {‘devel’,’sales’};

isnull

SELECT * FROM condition_tbl WHERE salary IS NULL;

SELECT * FROM condition_tbl WHERE salary IS NOT NULL;

SELECT * FROM condition_tbl WHERE salary = NULL;

like 运算符 转义用
SELECT * FROM condition_tbl WHERE name LIKE ‘%s%’;

SELECT * FROM condition_tbl WHERE UPPER(name) LIKE ‘_O%’;

SELECT * FROM condition_tbl WHERE name LIKE ‘___’;

REGEXP, RLIKE 运算符 这些事例用的少,转码相比 SELECT (‘a’ collate
utf8_en_ci REGEXP BINARY ‘A’ collate utf8_en_ci); 0 SELECT (‘a’
collate utf8_en_cs REGEXP BINARY ‘A’ collate utf8_en_cs); 0 SELECT
(‘a’ COLLATE iso88591_bin REGEXP ‘A’ COLLATE iso88591_bin); 1 SELECT
(‘a’ COLLATE iso88591_bin REGEXP BINARY ‘A’ COLLATE iso88591_bin); 0
主要看那几个 援助正则说明式喽 SELECT name FROM athlete where name REGEXP
‘^[a-d]’;

上边更像是演示正则表明式 匹配特殊的字符 SELECT (‘new\nline’ REGEXP ‘new
line’);

SELECT (‘cubrid dbms’ REGEXP ‘^cub’); SELECT (‘this is cubrid dbms’
REGEXP ‘dbms$’);

SELECT (‘cubrid dbms’ REGEXP ‘^c.*$’); SELECT (‘Aaaapricot’ REGEXP
‘^A+pricot’); SELECT (‘Apricot’ REGEXP ‘^Aa?pricot’); SELECT (‘Aapricot’
REGEXP ‘^Aa?pricot’); (‘Aapricot’ regexp ‘^Aa?pricot’)

1 代表建立 SELECT (‘Aaapricot’ REGEXP ‘^Aa?pricot’);

(‘Aaapricot’ regexp ‘^Aa?pricot’)

0 — (cub)* : match zero or more instances of the sequence abc.

SELECT (‘cubcub’ REGEXP ‘^(cub)*$’);

(‘cubcub’ regexp ‘^(cub)*$’)

1 — [a-dX], [^a-dX] : matches any character that is (or is not, if
^ is used) either a, b, c, d or X.

SELECT (‘aXbc’ REGEXP ‘^[a-dXYZ]+’);

(‘aXbc’ regexp ‘^[a-dXYZ]+’)

1 SELECT (‘strike’ REGEXP ‘^[^a-dXYZ]+$’);

(‘strike’ regexp ‘^[^a-dXYZ]+$’)

1

case 运算符

SELECT a, CASE WHEN a=1 THEN ‘one’ WHEN a=2 THEN ‘two’ ELSE ‘other’ END
FROM case_tbl;

结果 a case when a=1 then ‘one’ when a=2 then ‘two’ else ‘other’ end

        1  'one'
        2  'two'
        3  'other'
     NULL  'other'


     SELECT a,
   CASE a WHEN 1 THEN 'one'
          WHEN 2 THEN 'two'
          ELSE 'other'
   END

FROM case_tbl;

SELECT a, CASE WHEN a=1 THEN 1 WHEN a=2 THEN 1.2345 ELSE 1.234567890 END
FROM case_tbl;

SELECT a, CASE WHEN a=1 THEN ‘one’ WHEN a=2 THEN ‘two’ ELSE 1.2345 END
FROM case_tbl; //报错,类型转换错误

COALESCE 函数 对null处理的函数 类似 CASE WHEN a IS NOT NULL THEN a ELSE
b END

当a的值为null的时候 返回10.0000 SELECT a, COALESCE(a, 10.0000) FROM
case_tbl;

decode 函数 类似 CASE WHEN a = b THEN c WHEN a = d THEN e ELSE f END

参数有多个, 第二个 可以是表达式要依旧正如的值 如下例的a 第一个 结果
如下例的1 ,2 第多个 默许值 如other a 列 1 2 3 NULL

执行
SELECT a, DECODE(a, 1, ‘one’, 2, ‘two’, ‘other’) FROM case_tbl; 结果 1
‘one’ 2 ‘two’ 3 ‘other’ NULL ‘other’

a为1 等于one a为2 约等于two a为3和null ,没有符合条件的项,使用默许值other

SELECT a, DECODE(a, 1, 1, 2, 1.2345, 1.234567890) FROM case_tbl;
注意以下的结果,数字都是单精度的.隐式转换了. 以默许结果的花色为准. 1
1.000000000 2 1.234500000 3 1.234567890 NULL 1.234567890
上边的那些会破产,因为one不可能转为 double SELECT a, DECODE(a, 1, ‘one’, 2,
‘two’, 1.2345) FROM case_tbl;

GREATEST 函数 获取最大的值

SELECT gold, silver , bronze, GREATEST (gold, silver, bronze) FROM
participant WHERE nation_code = ‘KOR’;

结果是三个参数列每行值中的的最大值 gold silver bronze greatest(gold,
silver, bronze)

        9           12            9                              12
        8           10           10                              10
        7           15            5                              15
       12            5           12                              12
       12           10           11                              12

     c#,net,cubrid,教程,学习,笔记欢迎转载 ,转载时请保留作者信息。本文版权归本人所有,如有任何问题,请与我联系wang2650@sohu.com 。 过错  

if函数 四个参数, 第三个表明式返回真假 第四个象征确实时候回来的值
第多个代表假的时候回来的值

类似 CASE WHEN a IS TRUE THEN b ELSE c END

SELECT a, IF(a=1, ‘one’, ‘other’) FROM case_tbl;

IFNULL NVL函数
给null的列设默许值,注意重回的品种,取决于默许值的类型.ifnull和Nvl类似.不过Nvl协理集合类型.
类似 CASE WHEN a IS NULL THEN b ELSE a END

例如SELECT a, IFNULL(a, ‘UNKNOWN’) FROM case_tbl; 结果 1 ‘1’ 2 ‘2’ 3
‘3’ NULL ‘UNKNOWN’

ISNULL函数 重返整数 1或者0

LEAST函数 参数能够是多个表明式,再次来到最小的值, 参数是null则赶回null

SELECT gold, silver , bronze, LEAST(gold, silver, bronze) FROM
participant WHERE nation_code = ‘KOR’; 结果 gold silver bronze
least(gold, silver, bronze)

        9           12            9                            9
        8           10           10                            8
        7           15            5                            5
       12            5           12                            5
       12           10           11                           10

NULLIF 函数 相等再次回到null 否则原样重回 类似CASE WHEN a = b THEN NULL ELSE
a END

      SELECT a, NULLIF(a, 1) FROM case_tbl; 
      结果 a是1的时候返回null
       a  nullif(a, 1)

        1          NULL
        2             2
        3             3
     NULL          NULL

NVL2(expr1, expr2, expr3) 函数
第三个表明式不对等null的是或不是再次回到首个表明式,等于null再次来到第一个表达式
SELECT a, NVL2(a, a+1, 10.5678) FROM case_tbl;

     a  nvl2(a, a+1, 10.5678)

        1                      2
        2                      3
        3                      4
     NULL                     11

    在对window CE 5.0手持机程序支付进程中,使用了SQL Server CE
3.5的数据库,由于需求筛选出某个字段不为空(包罗null和””)的景况的数额。故使用ISNULL函数来替换null的情事,可是在运用的经过中,发现SQL
Server CE 中的ISNULL函数和SQL Server 2000或SQL Server
2005中的函数ISNULL不太雷同。为啥会有这般的差距吗?带着题材通过考虑找到了答案。

 

一、先看看一个SQL Server
2000中的SQL语句:

Select coalesce(null,null,’1′,’2′) //结果为 1

TestIsNull表的全体数目如下:(SELECT * FROM [TestIsNull])

coalesce(expression1**,**…n) 与此 case函数等效:

TransactionNumber   Name   WorkName
1                   test1    NULL

 

2                   test2    NULL

CASE
WHEN (expression1 IS NOT NULL) THEN expression1

WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
END

3                   test3    NULL

 

4                   test4    NULL

注意:
当首个表明式为字符串且不可能转化为整数时,若在前边的表明式中有整数,那样的语句是会报错的。
例如:

5                   test5    NULL

Select COALESCE(‘a’,null,’1′,2) //那是不当的

6                   test6    NULL

这一定于

7                   test6   

 

8                   test00   workName 

select 
CASE
WHEN (‘a’ IS NOT NULL) THEN ‘a’
WHEN (2 IS NOT NULL) THEN 2
ELSE NULL
END //会出现错误,因为系统不可以将a转换为对应的平头

大家选择ISNULL函数

 

select TransactionNumber,Name,isnull(WorkName,”)
as NewWorkName from TestIsNull

但是

 

 

 获得的结果集如下:

select 
CASE
WHEN (‘a’ IS NOT NULL) THEN ‘a’
WHEN (2 IS NOT NULL) THEN ‘2’
ELSE NULL
END //那是合情合理的

TransactionNumber   Name     NewWorkName
1                   test1 

 

2                   test2  

所以大家在利用coalesce函数时,应该万分小心。
isnull( check_expression , replacement_value ) 使用指定的替换值替换
NULL。

replacement_value 必须是足以隐式转换为 check_expresssion 类型的门类。
在表stu中
997755.com澳门葡京 1

3                   test3

select isnull(ssex,’p’) from stu //若ssex列中有NULL值,那么重返p

4                   test4 

nullif( expression , expression
) 借使五个指定的表明式相等,则赶回空值,否则 NULLIF
重回第四个 expression 的值。

5                   test5  

select nullif(1,1) //结果为 NULL
select nullif(1,2) //结果为 1

6                   test6  

7                   test6

8                   test00   workName 

 

可知SQL Server 2000对准ISNULL函数的用法是(SQL Server 2005也如此):

采纳指定的替换值替换 NULL

语法:ISNULL(check_expression,replacement_value)

参数:check_expression
将被检查是否为NULL的表明式。check_expression可以是任何类型的。

replacement_value
在check_expression为NULL时将赶回的表明式。replacement_value必须与check_expression具有相同的品种。

返回值:返回与check_expression相同的花色。

注释:如果check_expresssion不为NULL,那么重临该表明式的值;否则重临replacement_value。

 

二、SQL Server CE中的ISNULL的用法。

数据库的表TestIsNull表结构和数量同上:我们先来看看同样一条SQL语句在SQL
Server CE中查询出来的数据是怎么着?

SELECT transactionNumber, Name, ISNULL(WorkName, ”) AS NewWorkName
FROM TestIsNull

 

TransactionNumber Name NewWorkName
1 test1 True
2 test2 True
3 test3 True
4 test4 True
5 test5 True
6 test6 True
7 test6 False
8 test00 False

很明显和上边用SQL Server
2000询问出来的结果差距。通过阅览发现那里的ISNULL中的第三个参数无效,这里只看清了WorkName字段是还是不是为NULL,如果为NULL,则ISNULL(WorkName,”)重返值为True,否则为False。

最终通过寻找SQL CE的佑助文档,终于找到原因了:

 官方的SQL Server Compact
Edition不协理的ISNULL,但所描述的作为即使未曾SQL语法错误,或者抛出万分景况。另一个交接函数(COALESCE)可以取代ISNULL的机能,再次回到首个非空参数。

俺们用COALESCE来模仿上面的ISNULL的写法:

SELECT transactionNumber, Name, COALESCE (WorkName, ”) AS NewWorkName
FROM  TestIsNull

 

如此经过询问出来的结果集就和SQL Server 2000(或SQL Server
2005)查询出来的结果集一样了。

上面容易介绍一下有关COALESCE函数的用法:

COALESCE 重临其参数中首先个非空表明式。

语法:COALESCE (expression [,…n])

参数:expression:任何项目的表达式。n:表示能够指定三个表明式的占位符。所有表达式必须是一模一样档次。或者能够隐式转换成相同的品种。

重回值:将同样的值作为expression再次回到。

诠释:假如具有自变量均为NULL,则COLALESCE再次回到NULL值。COALESCE(expression,…n)与此CASE函数等价:

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   …
   WHEN (expressionN IS NOT NULL) THEN expressionN
   ELSE NULL

 

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website