SQL Server查询分类汇总

客官°小女子只卖身不卖艺 2021-12-16 04:20 314阅读 0赞

**构建测试数据：**

-- 判断是否已存在名为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

![20190705085454154.png][]

查询需求：要求从销售表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')

查询结果：

![20190705090736776.png][]

[20190705085454154.png]: /images/20211214/9e42acfc038a4fd9a04ec59c87df433b.png
[20190705090736776.png]: /images/20211214/e687c1a13f0a4d4ca14e94c3665b818c.png