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