case when 可以解决这个问题。。
解决方案 »
- @Sequence=right (@Tmp+convert(varchar(20),Sequence + 1)
- 查询语句
- 一个关于sqlserver的求多列数中的最小值的问题，小弟先感谢了！
- 求助，关于msde,在线等。
- 请数据库高手指点，一个问题
- 在从服务器接收结果时发生传输级错误。 (provider: TCP 提供程序, error: 0 - 指定的网络名不再可用。)
- 循环帮忙解释一下.
- left join 和Left outer join和join有什么区别??
- 这个 sql 语句该如何写？？
- 载存储过程中怎么才能动态创建临时表，并能对它进行增，删，改的数据操作？
- 托管的Sql数据库由外网可连接，总是被莫名的IP尝试SA密码，如何避免？
- 怎么选取某表中不在另一个表中的数据？
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂整理人：中国风(Roy)日期:2008.06.06
******************************************************************************************************************************************************/--1、行互列
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+' from Class group by [Student]')
生成静态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]GO
动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78(2 行受影响)
*/------------------------------------------------------------------------------------------
go
--加上总成绩(学科平均分)--2000方法:
动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=sum([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第一个逗号
exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')生成静态:select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337(2 行受影响)
*/go--2、列转行
--> --> (Roy)生成測試數據
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78(8 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([部门] varchar(1),[被考核产品] varchar(1),[产品考核项目] varchar(5),[月份] int,[结果] int)
insert [tb]
select 'a','A','项目1',1,100 union all
select 'a','A','项目1',2,90 union all
select 'a','A','项目1',3,80 union all
select 'a','A','项目2',1,70 union all
select 'a','A','项目2',2,60 union all
select 'a','A','项目2',3,50 union all
select 'a','B','项目1',1,40 union all
select 'a','B','项目1',2,30 union all
select 'b','A','项目1',1,20 union all
select 'b','A','项目1',2,10 union all
select 'b','A','项目1',3,0
---查询---
select
部门,
被考核产品,
产品考核项目,
sum(case 月份 when 1 then 结果 else 0 end) as [一月],
sum(case 月份 when 2 then 结果 else 0 end) as [二月],
sum(case 月份 when 3 then 结果 else 0 end) as [三月],
平均值=(select avg(结果) from tb where 部门=t.部门 and 被考核产品=t.被考核产品 and 产品考核项目=t.产品考核项目)
from tb t
group by 部门,被考核产品,产品考核项目---结果---
部门 被考核产品 产品考核项目 一月 二月 三月 平均值
---- ----- ------ ----------- ----------- ----------- -----------
a A 项目1 100 90 80 90
a A 项目2 70 60 50 60
a B 项目1 40 30 0 35
b A 项目1 20 10 0 10（所影响的行数为 4 行）
josy的方法非常正确。
还有一个问题我没表述清楚，就是：
表一中的【结果】的类型为char，并且存在“Wait for data”的数据项，请问这种情况怎么处理？谢谢！
josy的方法非常正确。
还有一个问题我没表述清楚，就是：
表一中的【结果】的类型为char，并且存在“Wait for data”的数据项，请问这种情况怎么处理？谢谢！
select
部门,
被考核产品,
产品考核项目,
sum(case 月份 when 1 then case 结果 else 0 end) as [一月],
sum(case 月份 when 2 then 结果 else 0 end) as [二月],
sum(case 月份 when 3 then 结果 else 0 end) as [三月],
平均值=(select avg(结果) from tb where 部门=t.部门 and 被考核产品=t.被考核产品 and 产品考核项目=t.产品考核项目)
from tb t
group by 部门,被考核产品,产品考核项目
where isnumeric(结果)=1
排除【结果】中的非数字
-->我借用josy建表代码,为楼主的需求重新修正了一下.
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([部门] varchar(1),[被考核产品] varchar(1),[产品考核项目] varchar(5),[月份] int,[结果] char(20))
insert [tb]
select 'a','A','项目1',1,100 union all
select 'a','A','项目1',2,90 union all
select 'a','A','项目1',3,80 union all
select 'a','A','项目2',1,70 union all
select 'a','A','项目2',2,60 union all
select 'a','A','项目2',3,50 union all
select 'a','B','项目1',1,40 union all
select 'a','B','项目1',2,30 union all
select 'b','A','项目1',1,20 union all
select 'b','A','项目1',2,10 union all
select 'b','A','项目1',2,'aa' union all
select 'b','A','项目1',3,0
---查询---
select
部门,
被考核产品,
产品考核项目,
sum(case 月份 when 1 then 结果 else 0 end) as [一月],
sum(case 月份 when 2 then 结果 else 0 end) as [二月],
sum(case 月份 when 3 then 结果 else 0 end) as [三月],
平均值=(select avg(cast(结果 as int)) from tb where 部门=t.部门 and 被考核产品=t.被考核产品 and 产品考核项目=t.产品考核项目 and 结果 like '%[0-9]%')
from tb t
where 结果 like '%[0-9]%'
group by 部门,被考核产品,产品考核项目部门 被考核产品 产品考核项目 一月 二月 三月 平均值
---- ----- ------ ----------- ----------- ----------- -----------
a A 项目1 100 90 80 90
a A 项目2 70 60 50 60
a B 项目1 40 30 0 35
b A 项目1 20 10 0 10（所影响的行数为 4 行）
（1）【结果】那一列出现小数的项，如0.7；
（2）【结果】的类型为vchar，并且存在“Wait for data”的数据项，输出内容想保持“Wait for data”，而不是0。大侠们帮忙啊
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([部门] varchar(1),[被考核产品] varchar(1),[产品考核项目] varchar(5),[月份] int,[结果] varchar(30))
insert [tb]
select 'a','A','项目1',1,100 union all
select 'a','A','项目1',2,90 union all
select 'a','A','项目1',3,80 union all
select 'a','A','项目2',1,70 union all
select 'a','A','项目2',2,60 union all
select 'a','A','项目2',3,50 union all
select 'a','B','项目1',1,40 union all
select 'a','B','项目1',2,30 union all
select 'b','A','项目1',1,20 union all
select 'b','A','项目1',2,10 union all
select 'b','A','项目1',3,.7 union all
select 'b','A','项目1',3,'Wait for data'
---查询---
select
部门,
被考核产品,
产品考核项目,
ltrim(sum(case when 月份=1 then cast(结果 as dec(18,2)) else 0.0 end)) as [一月],
ltrim(sum(case when 月份=2 then cast(结果 as dec(18,2)) else 0.0 end)) as [二月],
ltrim(sum(case when 月份=3 then cast(结果 as dec(18,2)) else 0.0 end)) as [三月],
平均值=(select avg(cast(结果 as dec(18,2))) from tb where isnumeric(结果)=1 and 部门=t.部门 and 被考核产品=t.被考核产品 and 产品考核项目=t.产品考核项目)
from tb t
where isnumeric(结果)=1
group by 部门,被考核产品,产品考核项目
union all
select
部门,
被考核产品,
产品考核项目,
case 月份 when 1 then 结果 else '' end as [一月],
case 月份 when 2 then 结果 else '' end as [二月],
case 月份 when 3 then 结果 else '' end as [三月],
0.0
from tb
where isnumeric(结果)=0
---结果---
部门 被考核产品 产品考核项目 一月 二月 三月 平均值
---- ----- ------ ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
a A 项目1 100.00 90.00 80.00 90.000000
a A 项目2 70.00 60.00 50.00 60.000000
a B 项目1 40.00 30.00 0.00 35.000000
b A 项目1 20.00 10.00 0.70 10.233333
b A 项目1 Wait for data .000000（所影响的行数为 5 行）
select
部门,
被考核产品,
产品考核项目,
max(case when 月份=1 then 结果 end) as [一月],
max(case when 月份=2 then 结果 end) as [二月],
max(case when 月份=3 then 结果 end) as [三月],
平均值=(select avg(cast(结果 as dec(18,2))) from tb where isnumeric(结果)=1 and 部门=t.部门 and 被考核产品=t.被考核产品 and 产品考核项目=t.产品考核项目)
from tb t
group by 部门,被考核产品,产品考核项目/**
部门 被考核产品 产品考核项目 一月 二月 三月 平均值
---- ----- ------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------------
a A 项目1 100 90 80 90.000000
a A 项目2 70 60 50 60.000000
a B 项目1 40 30 NULL 35.000000
b A 项目1 20 0.7 Wait for data 10.350000（所影响的行数为 4 行）
**/
这样？