sql中详解round(),floor(),ceiling()函数的用法和区别？

round() 遵循四舍五入把原值转化为指定小数位数，

如：round(1.45,0) = 1;round(1.55,0)=2

floor()向下舍入为指定小数位数 如：floor(1.45,0)= 1;floor(1.55,0) = 1

ceiling()向上舍入为指定小数位数 如：ceiling(1.45,0) = 2;ceiling(1.55,0)=2

 

SQL中ISNULL用法示例

 

ISNULLSQL查询示例SELECT

ISNULL

使用指定的替换值替换 NULL。

语法

ISNULL ( check_expression , replacement_value )

参数

check_expression

将被检查是否为 NULL的表达式。check_expression 可以是任何类型的。

replacement_value

在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。

返回类型

返回与 check_expression 相同的类型。

注释

如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value。

示例

1 示例数据

表tb_Student及其示例数据如下图所示。

 

合薪条码仓库管理系统

【点击进入】

合薪科技运用条形码技术，计算机软硬件技术 无线网络技术为您提供仓库管理解决方案。

查 看

 

 

2 查询要求

查询出其中成绩（score）小于等于60的学生信息保存至表变量@tempTable中，当学生成绩为空时，成绩记为0。

?

1

2

3

4

5

6

7

8

9

[csharp]

declare@tempTable table( 

    stunamenchar(10), 

    stuageint,  

               stuscorefloat); 

insertinto @tempTable 

selectname,age,ISNULL(score,0)fromtb_Student 

where ISNULL(score,0)<=60 

select* from@tempTable

 

3 执行结果

 

 

sql 中isnull函数的用法:

declare @t table

( id int identity(1,1),

UserId varchar(10),

Odds int)

insert @t select 'a',12

union all select 'b',23

union all select 'c',34

select isnull(userid,'total') as userid,

 sum(odds) as odds  from @t

group by userid

with rollup

/**userid odds--------------

a 12

b 23

c 34

total 69**/

1. ISNULL使用指定的替换值替换 NULL。

语法ISNULL ( check_expression , replacement_value )

注释

如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value。

2. ROLLUP ( )生成简单的 GROUP BY 聚合行以及小计行或超聚合行，还生成一个总计行。

返回的分组数等于 <composite element list> 中的表达式数加一。

例如，请考虑下面的语句。

SELECT a, b, c, SUM ( <expression> )FROM T

GROUP BY ROLLUP (a,b,c)

会为 (a, b, c)、(a, b) 和 (a) 值的每个唯一组合生成一个带有小计的行。

还将计算一个总计行。

列是按照从右到左的顺序汇总的。

列的顺序会影响 ROLLUP 的输出分组，而且可能会影响结果集内的行数。

 

 

为什么要引入ROLLUP？
可方便的生成"合计"、"小计"、"总计" 等混合统计的报表
如下数据集A经过ROLLUP运算可生成数据集B

1.ROLLUP简单应用
SELECT Groups,Item,Color FROM @t GROUP BY Groups,Item,Color  WITH ROLLUP
查询结果如下：

 

如果用ROLLUP的另一个关键字CUBE产生的结果如下:

SELECT Groups,Item,Color FROM @t GROUP BY Groups,Item,Color  WITH CUBE

引出一个概念:
维：查询所指定的列称为维，列的个数称为维度
以上两个SQL语句涉及三个维度，ROLLUP和CUBE都可以用于多维计算，不同点在于CUBE可以包含所有纬度的组合，而
ROLLUP只涉及指定列的维度计算。

2.ROLLUP运算关键字GROUPING
GROUPING：判断是否为多维计算中的附加列，如果是则输出1，否则输出0

 

SELECT Groups= CASE  WHEN GROUPING(GROUPS)=1 THEN 1 ELSE 0 END ,Item,Color FROM @t GROUP BY Groups,Item,Color WITH ROLLUP

结果:

排序应用：

SELECT Groups,Item,Color FROM @t GROUP BY Groups,Item,Color WITH ROLLUP ORDER BY GROUPING(Groups),Groups DESC

结果:

 

SELECT * FROM @t ORDER BY groups DESC 
结果：

至于自定义附加列的样式在第三步讲解

 

3.ROLLUP综合运用

 

SELECT Groups=CASE     
  WHEN GROUPING(Color)=0 THEN Groups    
  WHEN GROUPING(Groups)=1 THEN '总计'   
  ELSE '' END,    
 Item=CASE     
  WHEN GROUPING(Color)=0 THEN Item    
  WHEN GROUPING(Item)=1 THEN Groups+' 合计'   
  ELSE '' END,    
 Color=CASE     
  WHEN GROUPING(Color)=0 THEN Color    
  WHEN GROUPING(Color)=1 THEN Item+' 小计'   
  ELSE '' END,    
 Quantity=SUM(Quantity)    
FROM @t    
GROUP BY Groups,Item,Color WITH ROLLUP   
ORDER BY GROUPING(Groups),    
CASE WHEN GROUPING(Groups)=0 THEN Groups END  DESC,    
GROUPING(Item),    
CASE WHEN GROUPING(Item)=0 THEN Item END  DESC,    
GROUPING(Color),    
CASE WHEN GROUPING(Color)=0 THEN Color END  DESC

 

结果:

 

Cube 和 RollUp 的区别

 

The differences between CUBE and ROLLUP are:

• CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.

• 

• 可以看到Cube 比 RollUp多了三行.一比较发现，多的三行都是以Color列为纬度统计的。再结合书上说的那点区别，说RollUp是按层统计的。
  猜想区别就是Cube对每个纬度都统计了，而RollUp对Group by 后跟的第一个纬度按照值不同顺序统计完后，终止在Null,NUll,1117这一行，不再继续。
  我又加了一列Dept，调整之后再次比较结果，证实了我的猜想，RollUp以Item作为基本纬度，统计完所有情况后，就终止在Null,Null,NUll,1117这一行了。

  下面附上sql语句：
  CREATE TABLE Inventory (
  Item varchar(53) NOT NULL,
  Color varchar(53) NULL,
  Department varchar(53) Null,
  Quantity int NULL
  );

  INSERT Inventory VALUES ('Table', 'Red','IT', 223);
  INSERT Inventory VALUES ('Table', 'White','HR', 54);
  INSERT Inventory VALUES ('Table', 'Blue','ADM', 124);
  INSERT Inventory VALUES ('Chair', 'Red', 'IT',210);
  INSERT Inventory VALUES ('Chair', 'Blue','HR', 101);
  INSERT Inventory VALUES ('Chair', 'White','ADM', 23);
  INSERT Inventory VALUES ('Stand', 'Red','IT', 213);
  INSERT Inventory VALUES ('Stand', 'Blue','HR', 141);
  INSERT Inventory VALUES ('Stand', 'White','ADM', 28);

  SELECT * FROM Inventory

  SELECT Item, Color, Department,SUM(Quantity) AS Quantity
  FROM Inventory
  GROUP BY Item, Color,Department WITH CUBE;

  SELECT Item, Color, Department,SUM(Quantity) AS Quantity
  FROM Inventory
  GROUP BY Item, Color,Department WITH Rollup