三个SQL语句请教！

[showfray]

////////////////////////////////////////
一、表TableA，结构及数据如下
字符 递增 Float char
Name id salary month
a 1 71 4
b 2 45.6 4
c 3 33.2 4
a 4 61 5
b 6 22.2 5
....

a 9999 52.1 12
....

现要求把数据提练成如下表：（每行要求name不重复distinct name）
Name 1月 2月 3月... 12月 平均值
a xx xx xx xx (1~12)/12
b yy yy yy yy (1~12)/12
....

请问这样的sql语句如何写呀~？
（注意原表TableA中同一个月可能不只包含1个Name为a的数据，可能包含2个或更多个，此时被提炼的a对应的月份的xx数据是sum()/月）


////////////////////////////////////////////////////

二、相反，假设已有上提炼的表数据，如何改成TableA的形式？（当然被提炼的表只含唯一的Name，只能改为1条记录的a了）
请教这样的SQL又是如何写？


//////////////////////////////////////////////////////

三、请看下SQL语句
UPDATE t1 SET name=b.name FROM t1 a INNER JOIN t2 b ON a.id = b.id
是将t2的id与t1的id相等的t2数据中的name覆盖t1的name字段。

我想问，当t2的数据记录数大于t1的数据记录数时，我想找出除了a.id=b.id外t2剩余的数据如何写呀？

[子陌红尘]

1、
DECLARE @S VARCHAR(8000)

SET @S = 'SELECT NAME'
SET @S = @S + ',[ 1月] = MAX(CASE month WHEN ''1'' THEN salary END)'
SET @S = @S + ',[ 2月] = MAX(CASE month WHEN ''2'' THEN salary END)'
SET @S = @S + ',[ 3月] = MAX(CASE month WHEN ''3'' THEN salary END)'
SET @S = @S + ',[ 4月] = MAX(CASE month WHEN ''4'' THEN salary END)'
SET @S = @S + ',[ 5月] = MAX(CASE month WHEN ''5'' THEN salary END)'
SET @S = @S + ',[ 6月] = MAX(CASE month WHEN ''6'' THEN salary END)'
SET @S = @S + ',[ 7月] = MAX(CASE month WHEN ''7'' THEN salary END)'
SET @S = @S + ',[ 8月] = MAX(CASE month WHEN ''8'' THEN salary END)'
SET @S = @S + ',[ 9月] = MAX(CASE month WHEN ''9'' THEN salary END)'
SET @S = @S + ',[10月] = MAX(CASE month WHEN ''10'' THEN salary END)'
SET @S = @S + ',[11月] = MAX(CASE month WHEN ''11'' THEN salary END)'
SET @S = @S + ',[12月] = MAX(CASE month WHEN ''12'' THEN salary END)'
SET @S = @S + ',[平均值] = SUM(salary)/12 FROM TableA GROUP BY NAME'

EXEC(@S)

[子陌红尘]

select
name,identity(int,1,1) as id,salary,month
into
TableA
from
(select name,[ 1月] as salary,'1' as month from t union all
select name,[ 2月] as salary,'2' as month from t union all
select name,[ 3月] as salary,'3' as month from t union all
select name,[ 4月] as salary,'4' as month from t union all
select name,[ 5月] as salary,'5' as month from t union all
select name,[ 6月] as salary,'6' as month from t union all
select name,[ 7月] as salary,'7' as month from t union all
select name,[ 8月] as salary,'8' as month from t union all
select name,[ 9月] as salary,'9' as month from t union all
select name,[10月] as salary,'10' as month from t union all
select name,[11月] as salary,'11' as month from t union all
select name,[12月] as salary,'12' as month from t
order by month,name) a

[子陌红尘]

楼上是对第二个问题的回复

3、
select a.* from t2 a where not exists(select 1 from t1 where id=a.id)

[xiaoxiangqing]

------------A-----------
/*
一、表TableA，结构及数据如下
字符 递增 Float char
Name id salary month
a 1 71 4
b 2 45.6 4
c 3 33.2 4
a 4 61 5
b 6 22.2 5
....

a 9999 52.1 12
*/
if exists (select * from sysobjects where object_id('tableA')=id and objectproperty(id,'isUserTable')=1)
drop table tableA

go

create table tableA(Name varchar(10),id int not null identity(1,1),salary float,month char(2))
go

---------增加数据---------
insert tableA
select 'a',11, 1 union all
select 'b',21, 2 union all
select 'c',31, 3 union all
select 'a',71, 4 union all
select 'b',45, 4 union all
select 'c',33, 4 union all
select 'a',61, 5 union all
select 'b',22, 5 union all
select 'c',33, 5 union all
select 'a',11, 6 union all
select 'b',15, 6 union all
select 'c',13, 6 union all
select 'c',23, 6 union all
select 'b',25, 6 union all
select 'b',35, 7 union all
select 'b',45, 8 union all
select 'a',52, 9 union all
select 'a',62, 9 union all
select 'a',72, 10 union all
select 'a',82, 11 union all
select 'a',92, 11 union all
select 'c',95, 12
go

select * from tableA
/*
Name id salary month
a 1 11.0 1
b 2 21.0 2
c 3 31.0 3
a 4 71.0 4
b 5 45.0 4
c 6 33.0 4
a 7 61.0 5
b 8 22.0 5
c 9 33.0 5
a 10 11.0 6
b 11 15.0 6
c 12 13.0 6
c 13 23.0 6
b 14 25.0 6
b 15 35.0 7
b 16 45.0 8
a 17 52.0 9
a 18 62.0 9
a 19 72.0 10
a 20 82.0 11
a 21 92.0 11
c 22 95.0 12

*/
select Name,sum(case when month=1 then sum_salary/cnt end) '1月',sum(case when month=2 then sum_salary/cnt end) '2月',sum(case when month=3 then sum_salary/cnt end) '3月',sum(case when month=4 then sum_salary/cnt end) '4月',sum(case when month=5 then sum_salary/cnt end) '5月',sum(case when month=6 then sum_salary/cnt end) '6月',sum(case when month=7 then sum_salary/cnt end) '7月',sum(case when month=8 then sum_salary/cnt end) '8月',sum(case when month=9 then sum_salary/cnt end) '9月',sum(case when month=10 then sum_salary/cnt end) '10月',sum(case when month=11 then sum_salary/cnt end) '11月',sum(case when month=12 then sum_salary/cnt end) '12月',avg(sum_salary/cnt) '平均值' from
(
select name,month,sum(salary) sum_salary,count(*) cnt from tableA group by name,month
)a group by name
/*
Name 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 平均值
a 11.0 NULL NULL 71.0 61.0 11.0 NULL NULL 57.0 72.0 87.0 NULL 52.8571428571
b NULL 21.0 NULL 45.0 22.0 20.0 35.0 45.0 NULL NULL NULL NULL 31.33333333
c NULL NULL 31.0 33.0 33.0 18.0 NULL NULL NULL NULL NULL 95.0 42.0
*/

------------C-----------
/*
三、请看下SQL语句
UPDATE t1 SET name=b.name FROM t1 a INNER JOIN t2 b ON a.id = b.id
是将t2的id与t1的id相等的t2数据中的name覆盖t1的name字段。

我想问，当t2的数据记录数大于t1的数据记录数时，我想找出除了a.id=b.id外t2剩余的数据如何写呀？
*/

if exists (select * from sysobjects where object_id('t1')=id and objectproperty(id,'isUserTable')=1)
drop table t1

if exists (select * from sysobjects where object_id('t2')=id and objectproperty(id,'isUserTable')=1)
drop table t2

go

create table t1(id int not null,name varchar(20))
create table t2(id int not null,name varchar(20))
go

insert into t1
select '1','aaa' union all
select '2','bbb' union all
select '3','ccc'

insert into t2
select '1','aaa' union all
select '2','bbb' union all
select '3','ccc' union all
select '4','ddd' union all
select '5','eee'

go

select * from t1
/*
id name
1 aaa
2 bbb
3 ccc
*/

select * from t2
/*
id name
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
*/

select a.* from t2 a left join t1 b on a.id=b.id where b.id is null
/*
id name
4 ddd
5 eee
*/

[showfray]

真是太谢谢两位了。真是大开眼界，方法还挺多的。

尤其xiaoxiangqing(肖祥清)，比较符合实际，会计算出cnt，因为可能存在某月不存在Name为a的Salary，所以除以实际的cnt并非12是更符合实际情况。

总之谢谢两位。

接分吧。