SQL Server查询分类汇总

构建测试数据：

-- 判断是否已存在名为sales的表，若存在，则删除：if object_id('sales','U') is not nulldrop table sales-- 创建新的销售表sales:create table sales(year int,country varchar(20),product varchar(32),profit int)-- 向销售表sales中插入数据：insert into sales values(2000,'USA','Calculator',105)insert into sales values(2000,'USA','Computer',1500)insert into sales values(2000,'USA','TV',1350)insert into sales values(2000,'USA','Calculator',50)insert into sales values(2000,'USA','Computer',2500)insert into sales values(2000,'USA','Phone',3100)insert into sales values(2001,'USA','Computer',1200)insert into sales values(2001,'USA','TV',3050)insert into sales values(2001,'USA','TV',2000)insert into sales values(2000,'Finland','Computer',1500)insert into sales values(2000,'Finland','Phone',1000)insert into sales values(2001,'Finland','Phone',2500)insert into sales values(2000,'India','Calculator',150)insert into sales values(2000,'India','Calculator',75)insert into sales values(2000,'India','Computer',1200)insert into sales values(2000,'India','TV',2200)insert into sales values(2001,'India','Calculator',300)

查看销售表sales的表结构：

select * from sales

查询需求：要求从销售表sales中查询出不同国家下各个产品在2000年时的总利润，且要求查询结果中的国家按照India,Finland,USA这样的顺序排列，产品按照Computer,Phone,Calculator,TV这样的顺序排列。

SQL代码如下：

-- 判断临时表#SaleInfo是否已经存在，若存在，则删除if OBJECT_ID('tempdb..#SaleInfo') is not nulldrop table #SaleInfo-- 为了保护原始数据，我们将销售表sales中的数据查询出来放入临时表SaleInfo中select year,country,product,profit into #SaleInfo from sales-- 向临时表#SaleInfo中插入从销售表sales计算得到的总计insert into #SaleInfoselect year,'总计' country,product,SUM(profit) as profitfrom #SaleInfogroup by year,productselect country,product,sum(profit) as total_profit from #SaleInfowhere year=2000 group by country,productorder by charindex(country,'India,Finland,USA,总计'),charindex(product,'Computer,Phone,Calculator,TV')

查询结果：

如果觉得《SQL Server查询分类汇总》对你有帮助，请点赞、收藏，并留下你的观点哦！