2. isnull与rollup用法(SQL)

最新推荐文章于 2025-09-12 21:19:01 发布

xinqiba01

最新推荐文章于 2025-09-12 21:19:01 发布

阅读量575

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/xinqiba01/article/details/6342806

本文介绍了SQL中的ISNULL函数及ROLLUP运算符的应用。通过具体实例展示了如何使用ISNULL函数处理NULL值，并利用ROLLUP生成包含小计和总计的数据报表。

CREATE TABLE [dbo].[test_isnull](
[name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[odder] [int] NULL
) ON [PRIMARY]
insert into test_isnull values('a',12)
insert into test_isnull values('b',23)
insert into test_isnull values('c',34)
-------------------------------
select
isnull(name,'total') as userid,
sum(odder) as odds
from test_isnull
group by name
with rollup

--生成一行统计,rollup 是汇总数据
在生成包含小计和合计的报表时，rollup 运算符很有用。rollup 运算符生成的结果集类似于 cube 运算符所生成的结果集

-------------------------------------------------------------------------------------------------------

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

语法
ISNULL ( check_expression , replacement_value )

---------------------------------------------小计与汇总----------------------------------

CREATE TABLE tb(province nvarchar(10),city nvarchar(10),score int)
INSERT tb SELECT '陕西','西安',3
UNION ALL SELECT '陕西','安康',4
UNION ALL SELECT '陕西','汉中',2
UNION ALL SELECT '广东','广州',5
UNION ALL SELECT '广东','珠海',2
UNION ALL SELECT '广东','东莞',3
UNION ALL SELECT '江苏','南京',6
UNION ALL SELECT '江苏','苏州',1
GO
select * from tb
select isnull(province,'总合计') as 省,city as 市,sum(score) as 分数,grouping(province) as g_p,grouping(city) as g_c from tb group by province,city with rollup
select case when grouping(province)=1 then '总计' else province end 省, case when grouping(city)=1 and grouping(province)=0 then '小计' else city end 市,sum(score) as 分数 from tb group by province,city with rollup