供应商 日期 数量
A 2008-4-16 1
A 2008-5-6 2
B 2008-4-20 3
B 2008-5-12 4
B 2008-5-13 5
结果
供应商 4月 5月
A 1 2
B 3 9
A 2008-4-16 1
A 2008-5-6 2
B 2008-4-20 3
B 2008-5-12 4
B 2008-5-13 5
结果
供应商 4月 5月
A 1 2
B 3 9
解决方案 »
- 弱问sql简单问题
- 主表插入数据,从表如何根据主表的自增长ID同时插入数据?
- sql server数据库触发器和备份数据表的问题
- 求助MSSQL语法
- vb 写的asp怎么和sql2005 连不上 弄了两天了 请大家帮帮忙
- 各位大虾们,有一个SQL-Server的问题,在SQL-Server中创建不了一个目录……
- 请教语句
- 【救急】关于sql2005 安装后windows身份验证无法登陆 T^T
- 附加数据库时: 错误5180:由于文件ID3(位于数据库'newlsb'中)无效,所以未能打开fcb
- 我用A表和B表的update觸發器互相修改對方的數據,怎樣才可避免循環修改?
- 看看我这个建库思路对吗?另外能否告诉我怎么查我要的结果。
- 生成SQL脚本,如何生成表数据的Insert...
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (供应商 varchar(1),日期 datetime,数量 int)
insert into #T
select 'A','2008-4-16',1 union all
select 'A','2008-5-6',2 union all
select 'B','2008-4-20',3 union all
select 'B','2008-5-12',4 union all
select 'B','2008-5-13',5select
供应商,
[4月]=sum(case month(日期) when 4 then 数量 else 0 end),
[5月]=sum(case month(日期) when 5 then 数量 else 0 end)
from #T group by 供应商
/*
供应商 4月 5月
------- ----------- -----------
A 1 2
B 3 9
*/
标题:普通行列转换(version 2.0)
作者:爱新觉罗.毓华
时间:2008-03-09
地点:广东深圳
说明:普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法,version 2.0增加sql server 2005的有关写法。问题:假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果):
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题:在上述结果的基础上加平均分,总分,得到如下结果:
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb ------------------
------------------/*
问题:如果上述两表互相换一下:即表结构和数据为:
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果):
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb,不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。--------------------
/*
问题:在上述的结果上加个平均分,总分,得到如下结果:
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (供应商 varchar(1),日期 datetime,数量 int)
insert into #T
select 'A','2008-4-16',1 union all
select 'A','2008-5-6',2 union all
select 'B','2008-4-20',3 union all
select 'B','2008-5-12',4 union all
select 'B','2008-5-13',5-->应该加上年份:
select
供应商,
年份=year(日期),
[4月]=sum(case month(日期) when 4 then 数量 else 0 end),
[5月]=sum(case month(日期) when 5 then 数量 else 0 end)
from #T group by 供应商,year(日期)
/*
供应商 年份 4月 5月
------- ----------- ----------- -----------
A 2008 1 2
B 2008 3 9
*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (供应商 varchar(1),日期 datetime,数量 int)
insert into #T
select 'A','2008-4-16',1 union all
select 'A','2008-5-6',2 union all
select 'B','2008-4-20',3 union all
select 'B','2008-5-12',4 union all
select 'B','2008-5-13',5
go
declare @sql varchar(8000)
set @sql='select 供应商'
select @sql=@sql+',sum(case when month(日期)='+ltrim(m)+' then 数量 else 0 end) ['+ltrim(m)+'月]'
from (select distinct month(日期) m from #T) t
exec (@sql+' from #T group by 供应商')
go
drop table #T/*
供应商 4月 5月
---- ----------- -----------
A 1 2
B 3 9(2 row(s) affected)*/
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([供应商] nvarchar(1),[日期] Datetime,[数量] int)
Insert #T
select 'A','2008-4-16',1 union all
select 'A','2008-5-6',2 union all
select 'B','2008-4-20',3 union all
select 'B','2008-5-12',4 union all
select 'B','2008-5-13',5
Go
declare @s nvarchar(4000)
select @s='select [供应商]'
select @s=@s+',['+rtrim(month([日期]))+'月]=sum(case when month([日期])='+rtrim(month([日期]))+' then [数量] else 0 end)'
from
#T group by month([日期])exec(@s+' from #T group by [供应商]')
供应商 4月 5月
---- ----------- -----------
A 1 2
B 3 9(2 行受影响)
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (供应商 varchar(1),日期 datetime,数量 int)
insert into #T
select 'A','2008-4-16',1 union all
select 'A','2008-5-6',2 union all
select 'B','2008-4-20',3 union all
select 'B','2008-5-12',4 union all
select 'B','2008-5-13',5
go
declare @sql varchar(8000)
set @sql='select 供应商,year(日期) 年份'
select @sql=@sql+',sum(case when month(日期)='+ltrim(m)+' then 数量 else 0 end) ['+ltrim(m)+'月]'
from (select distinct month(日期) m from #T) t
exec (@sql+' from #T group by 供应商,year(日期)')
go
drop table #T/*
供应商 年份 4月 5月
---- ----------- ----------- -----------
A 2008 1 2
B 2008 3 9(2 row(s) affected)
*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (供应商 varchar(1),日期 datetime,数量 int)
insert into #T
select 'A','2008-4-16',1 union all
select 'A','2008-5-6',2 union all
select 'B','2008-4-20',3 union all
select 'B','2008-5-12',4 union all
select 'B','2008-5-13',5select
供应商,
年份=year(日期),
[4月]=sum(case month(日期) when 4 then 数量 else 0 end),
[5月]=sum(case month(日期) when 5 then 数量 else 0 end)
from #T group by 供应商,year(日期)
/*
供应商 年份 4月 5月
------- ----------- ----------- -----------
A 2008 1 2
B 2008 3 9
*/-->动态:
declare @sql varchar(1000)
select @sql=isnull(@sql+',','')+'['+ltrim(month(日期))+'月]=sum(case month(日期) when '+ltrim(month(日期))+' then 数量 else 0 end)' from #T group by month(日期)exec ('select 供应商,年份=year(日期),'+@sql+' from #T group by 供应商,year(日期)')
/*
供应商 年份 4月 5月
------- ----------- ----------- -----------
A 2008 1 2
B 2008 3 9
*/
create table tb
(供应商 char,日期 datetime,数量 int )insert into tb select
'A','2008-4-16',1 union select
'A','2008-5-6', 2 union select
'B','2008-4-20',3 union select
'B','2008-5-12',4 union select
'B','2008-5-13',5
declare @s varchar(2000)
set @s='select 供应商'
select @s=@s+ ',sum(case when left(convert(varchar(10),日期,120),7) ='''+rq +''' then 数量 else 0 end) as '''+rq+'''' from
(
select distinct left(convert(varchar(10),日期,120),7) as rq from tb
) as tb_result
--
set @s =@s + ' from tb group by 供应商'exec(@s)
drop table tb
--结果供应商 2008-04 2008-05
---- ----------- -----------
A 1 2
B 3 9