SQL Server查询分类汇总

构建测试数据：

-- 判断是否已存在名为sales的表，若存在，则删除：
if object_id('sales','U') is not null
drop 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 null
drop table #SaleInfo

-- 为了保护原始数据，我们将销售表sales中的数据查询出来放入临时表SaleInfo中
select year,country,product,profit into #SaleInfo from sales

-- 向临时表#SaleInfo中插入从销售表sales计算得到的总计
insert into #SaleInfo
select year,'总计' country,product,SUM(profit) as profit
from #SaleInfo
group by year,product

select country,product,sum(profit) as total_profit from #SaleInfo
where year=2000 
group by country,product
order by charindex(country,'India,Finland,USA,总计'),charindex(product,'Computer,Phone,Calculator,TV')

查询结果：