现我有以下两张表：表1：字段1 字段2 年度
1
3
4
2
7表2：id 字段1 Yr1 Yr2 Yr3 Yr4 Yr5 Yr6 Yr7
1
2
3
4
5同时，表1和表2有一个共同的字段：字段 我现在想在表1中加入字段3，字段3的取值逻辑是这样的：按照表1中的年度去表2的对应年度字段中取值。
例如：表1第一个记录的年度字段是1，那么将表1和表2连接后，新加入的字段3取Yr1的值；表1第二个记录的年度字段是3，那么新加入的字段3取Yr3的值。这该怎么实现呢？
1
3
4
2
7表2：id 字段1 Yr1 Yr2 Yr3 Yr4 Yr5 Yr6 Yr7
1
2
3
4
5同时，表1和表2有一个共同的字段：字段 我现在想在表1中加入字段3，字段3的取值逻辑是这样的：按照表1中的年度去表2的对应年度字段中取值。
例如：表1第一个记录的年度字段是1，那么将表1和表2连接后，新加入的字段3取Yr1的值；表1第二个记录的年度字段是3，那么新加入的字段3取Yr3的值。这该怎么实现呢？
1 yr1的值
3 yr3的值
4 yr4的值
2 yr2的值
7 yr7的值
字段1 字段2 年度
xxx1 yyy1 1
xxx2 yyy2 3
xxx3 yyy3 4
xxx4 yyy4 2
xxx5 yyy5 7表2：
有如下几个字段
id 字段1 Y1 Y2 Y3 Y4 Y5 Y6 Y7
1 xxx1 3
2 xxx2 4
3 xxx3 5
4 xxx4 1
5 xxx5 9
同时，表1和表2有一个共同的字段：字段1我现在想在表1中加入字段3，字段3的取值逻辑是这样的：按照表1中的年度去表2的对应年度字段中取值例如：表1第一个记录的年度字段是1，那么将表1和表2按照字段1连接后，新加入的字段3取对应的Y1的值，即：3；表1第二个记录的年度字段是3，那么新加入的字段3取Y3的值，即4。该如何实现呢？
字段3=case 年度 when 1 then b.Y1 when 2 then b.Y2
when 3 then b.Y3 when 4 then b.Y4
when 5 then b.Y4 when 6 then b.Y6
when 7 then b.Y7 end
from 表1 a,表2 b where a.字段1=b.字段1
字段1 字段2 年度 字段3
xxx1 yyy1 1 3
xxx2 yyy2 3 4
xxx3 yyy3 4 5
xxx4 yyy4 2 1
xxx5 yyy5 7 9
*/
如果是取Yr3的值，你5楼举的例子就不应该是Y1的值，真服了你
-- Author :fredrickhu(小F，向高手学习)
-- Date :2011-12-10 13:59:13
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go
create table [a]([字段1] varchar(4),[字段2] varchar(4),[年度] varchar(2))
insert [a]
select 'xxx1','yyy1','01' union all
select 'xxx2','yyy2','03' union all
select 'xxx3','yyy3','04' union all
select 'xxx4','yyy4','02' union all
select 'xxx5','yyy5','07'
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go
create table [b]([字段1] varchar(4),[Y1] varchar(2),[Y2] varchar(2),[Y3] varchar(2),[Y4] varchar(2),[Y5] varchar(2),[Y6] varchar(2),[Y7] varchar(2))
insert [b]
select 'xxx1','33','00','00','00','00','00','00' union all
select 'xxx2','00','00','44','00','00','00','00' union all
select 'xxx3','00','00','00','55','00','00','00' union all
select 'xxx4','00','11','00','00','00','00','00' union all
select 'xxx5','00','00','00','00','00','00','19'
--------------开始查询--------------------------
select
a.*,b.字段3
from
a
join
(
select 字段1,coalesce(nullif(y1,'00'),nullif(y2,'00'), nullif(y3,'00'), nullif(y4,'00'), nullif(y5,'00'),nullif(y6,'00'),nullif(y7,'00')) as 字段3 from b)b
on
a.字段1=b.字段1----------------结果----------------------------
/* 字段1 字段2 年度 字段3
---- ---- ---- ----
xxx1 yyy1 01 33
xxx2 yyy2 03 44
xxx3 yyy3 04 55
xxx4 yyy4 02 11
xxx5 yyy5 07 19(5 行受影响)
*/
/******************************************************************************************************************************************************
以学生成绩为例子，比较形象易懂整理人：中国风(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 行受影响)
*/
select a.col01,a.col02,a.years,'字段3' = case a.years when 1 then b.Y1 when 3 then b.Y3 when 4 then b.Y4 when 2 then b.Y2 when 7 then b.Y7
end from t1 a, t2 b where a.col01=b.col01
--5楼列举的数据有误，LZ你真可以，第二个记录的年度字段是3，那么新加入的字段3取Y3的值怎么就为4了，你的问题表达不正确就容易误导别人。以下为结果：/*
字段1 字段2 年度 字段3
xxx1 yyy1 1 13
xxx2 yyy2 3 34
xxx3 yyy3 4 45
xxx4 yyy4 2 21
xxx5 yyy5 7 79
*/