国家 书名 价格 kor java 20
kor java 10
japan VB 15
japan c# 10
USA java 10
USA VB 2
我想实现 书名 kor japan USA 统计 java 30 0 10 40
VB 0 15 2 17
c# 0 10 0 10
记 30 25 12 52这样的结果
kor java 10
japan VB 15
japan c# 10
USA java 10
USA VB 2
我想实现 书名 kor japan USA 统计 java 30 0 10 40
VB 0 15 2 17
c# 0 10 0 10
记 30 25 12 52这样的结果
use tempdb;
/*
create table test
(
国家 nvarchar(10) not null,
书名 nvarchar(10) not null,
价格 int not null
);
insert into test(国家,书名,价格)
values
('kor','java',20),
('kor','java',10),
('japan','VB',15),
('japan','c#',10),
('USA','java',10),
('USA','VB',2);
*/
select t.书名,
ISNULL(SUM(case when t.国家 = 'kor' then t.价格 end),0) as [kor],
ISNULL(SUM(case when t.国家 = 'japan' then t.价格 end),0) as [japan],
ISNULL(SUM(case when t.国家 = 'USA' then t.价格 end),0) as [USA]
from test as t
group by 书名union allselect '记',SUM(kor),SUM(japan),SUM(USA)
from
(
select t.书名,
ISNULL(SUM(case when t.国家 = 'kor' then t.价格 end),0) as [kor],
ISNULL(SUM(case when t.国家 = 'japan' then t.价格 end),0) as [japan],
ISNULL(SUM(case when t.国家 = 'USA' then t.价格 end),0) as [USA]
from test as t
group by 书名
) as temp;
use tempdb;
/*
create table test
(
国家 nvarchar(10) not null,
书名 nvarchar(10) not null,
价格 int not null
);
insert into test(国家,书名,价格)
values
('kor','java',20),
('kor','java',10),
('japan','VB',15),
('japan','c#',10),
('USA','java',10),
('USA','VB',2);
*/select t.书名,
ISNULL(SUM(case when t.国家 = 'kor' then t.价格 end),0) as [kor],
ISNULL(SUM(case when t.国家 = 'japan' then t.价格 end),0) as [japan],
ISNULL(SUM(case when t.国家 = 'USA' then t.价格 end),0) as [USA],
SUM(价格) as [统计]
from test as t
group by 书名union allselect '记',SUM(kor),SUM(japan),SUM(USA),SUM(统计)
from
(
select t.书名,
ISNULL(SUM(case when t.国家 = 'kor' then t.价格 end),0) as [kor],
ISNULL(SUM(case when t.国家 = 'japan' then t.价格 end),0) as [japan],
ISNULL(SUM(case when t.国家 = 'USA' then t.价格 end),0) as [USA],
SUM(价格) as [统计]
from test as t
group by 书名
) as temp;
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-18 10:26:11
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([国家] varchar(5),[书名] varchar(4),[价格] int)
insert [tb]
select 'kor','java',20 union all
select 'kor','java',10 union all
select 'japan','VB',15 union all
select 'japan','c#',10 union all
select 'USA','java',10 union all
select 'USA','VB',2
--------------开始查询--------------------------
select
isnull(书名,'记') as 书名,
isnull(sum(case when 国家 = 'kor' then 价格 end),0) as [kor],
isnull(sum(case when 国家 = 'japan' then 价格 end),0) as [japan],
isnull(sum(case when 国家 = 'USA' then 价格 end),0) as [USA],
sum(价格) as [统计]
from
tb t
group by
书名
with rollup----------------结果----------------------------
/* 书名 kor japan USA 统计
---- ----------- ----------- ----------- -----------
c# 0 10 0 10
java 30 0 10 40
VB 0 15 2 17
记 30 25 12 67
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)*/