一个SQL update更新问题（更新表的多个字段值从另一张表的多个记录里抓）

如题现有两个表
表一
Type student1,student2,student3,score1,score2,score3
数学     NULL      NULL    NULL    NULL   NULL   NULL


表2
nXH Type student score
1   数学    003     74
2   数学    001     90
3   数学    002     85


该怎么写SQL才能把表一更新为如下
Type student1,student2,student3,score1,score2,score3
数学    003      001        002    74      90   85

Type是两个表的关联字段，表1的student 1就是表2中 nXH 为1的学员
如果不足三条记录就更新为空

5 个解决方案

#1

sf

#2

---------------------------------
--  Author: liangCK 小梁
--  Date  : 2009-05-22 09:41:15
---------------------------------
 
--> 生成测试数据: @表一
DECLARE @表一 TABLE (Type VARCHAR(4),student1 VARCHAR(3),student2 VARCHAR(3),student3 VARCHAR(3),score1 INT,score2 INT,score3 INT)
INSERT INTO @表一
SELECT '数学',null,null,null,null,null,null
 
--> 生成测试数据: @表2
DECLARE @表2 TABLE (nXH INT,Type VARCHAR(4),student VARCHAR(3),score INT)
INSERT INTO @表2
SELECT 1,'数学','003',74 UNION ALL
SELECT 2,'数学','001',90 UNION ALL
SELECT 3,'数学','002',85

--SQL查询如下:

UPDATE A SET
    student1=B.student1,
    score1=B.score1,
    student2=B.student2,
    score2=B.score2,
    student3=B.student3,
    score3=B.score3
FROM @表一 AS A
    JOIN (
        SELECT
            Type,
            MAX(CASE WHEN rowid=1 THEN student END) AS student1,
            MAX(CASE WHEN rowid=1 THEN score END) AS score1,
            MAX(CASE WHEN rowid=2 THEN student END) AS student2,
            MAX(CASE WHEN rowid=2 THEN score END) AS score2,
            MAX(CASE WHEN rowid=3 THEN student END) AS student3,
            MAX(CASE WHEN rowid=3 THEN score END) AS score3
        FROM (SELECT rowid=ROW_NUMBER() OVER(PARTITION BY Type ORDER BY nXH),
                     Type,student,score
              FROM @表2) AS A
        GROUP BY Type
    ) AS B
ON A.Type=B.Type;


SELECT * FROM @表一

/*
Type student1 student2 student3 score1      score2      score3
---- -------- -------- -------- ----------- ----------- -----------
数学   003      001      002      74          90          85

(1 row(s) affected)
*/

#3

DECLARE @TA TABLE([nXH] INT, [Type] NVARCHAR(2), [student] VARCHAR(3), [score] INT)
INSERT @TA 
SELECT 1, N'数学', '003', 74 UNION ALL 
SELECT 2, N'数学', '001', 90 UNION ALL 
SELECT 3, N'数学', '002', 85

DECLARE @TB TABLE([Type] NVARCHAR(2), [student1] VARCHAR(3), [student2] VARCHAR(3), [student3] VARCHAR(3), [score1] INT, [score2] INT, [score3] INT)
INSERT @TB 
SELECT N'数学', NULL, NULL, NULL, NULL, NULL, NULL

UPDATE @TB 
	SET student1=S1,student2=S2,student3=S3,
	    score1=C1,score2=C2,score3=C3
FROM @TB AS B JOIN (
SELECT [Type],
		MAX(CASE WHEN [nXH]=1 THEN [student] END) AS S1,
		MAX(CASE WHEN [nXH]=2 THEN [student] END) AS S2,
		MAX(CASE WHEN [nXH]=3 THEN [student] END) AS S3,
		MAX(CASE WHEN [nXH]=1 THEN [score] END) AS C1,		
		MAX(CASE WHEN [nXH]=2 THEN [score] END) AS C2,		
		MAX(CASE WHEN [nXH]=3 THEN [score] END) AS C3
FROM @TA 
GROUP BY [Type]
) A ON B.[Type]=A.[Type]

SELECT * FROM @TB
/*
Type student1 student2 student3 score1      score2      score3
---- -------- -------- -------- ----------- ----------- -----------
数学   003      001      002      74          90          85
*/

#4

引用 3 楼 csdyyr 的回复:

SQL codeDECLARE@TATABLE([nXH]INT,[Type]NVARCHAR(2),[student]VARCHAR(3),[score]INT)INSERT@TASELECT1, N'数学','003',74UNIONALLSELECT2, N'数学','001',90UNIONALLSELECT3, N'数学','002',85DECLARE@TBTABLE([Type]NVARCHAR(2),[student1]VARCHAR(3),[student2]VARCHAR(3),[student3]VARCHAR(3),[score1]INT,[score2]INT,[score3]INT)INSERT@TBSELECTN'数学',NULL,NULL,NULL,NULL,NULL,NULLUPDATE@TBSETstudent1=S1,student…

--考虑不够全面，修改一下
DECLARE @TA TABLE([nXH] INT, [Type] NVARCHAR(2), [student] VARCHAR(3), [score] INT)
INSERT @TA 
SELECT 1, N'数学', '003', 74 UNION ALL 
SELECT 2, N'数学', '001', 90 UNION ALL 
SELECT 3, N'数学', '002', 85 UNION ALL
SELECT 4, N'语文', '004', 96 UNION ALL 
SELECT 5, N'语文', '005', 88

DECLARE @TB TABLE([Type] NVARCHAR(2), [student1] VARCHAR(3), [student2] VARCHAR(3), [student3] VARCHAR(3), [score1] INT, [score2] INT, [score3] INT)
INSERT @TB 
SELECT N'数学', NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
SELECT N'语文', NULL, NULL, NULL, NULL, NULL, NULL

SELECT *,ID=IDENTITY(int,1,1) INTO # FROM @TA ORDER BY [Type],nXH

UPDATE @TB 
	SET student1=S1,student2=S2,student3=S3,
	    score1=C1,score2=C2,score3=C3
FROM @TB AS B JOIN (
SELECT [Type],
		MAX(CASE WHEN SEQ=1 THEN [student] END) AS S1,
		MAX(CASE WHEN SEQ=2 THEN [student] END) AS S2,
		MAX(CASE WHEN SEQ=3 THEN [student] END) AS S3,
		MAX(CASE WHEN SEQ=1 THEN [score] END) AS C1,		
		MAX(CASE WHEN SEQ=2 THEN [score] END) AS C2,		
		MAX(CASE WHEN SEQ=3 THEN [score] END) AS C3
FROM (SELECT *,SEQ=ID-(SELECT COUNT(*) FROM # WHERE [Type]<T.[Type]) FROM # T) T 
GROUP BY [Type]
) A ON B.[Type]=A.[Type]

SELECT * FROM @TB
DROP TABLE #
/*
Type student1 student2 student3 score1      score2      score3
---- -------- -------- -------- ----------- ----------- -----------
数学   003      001      002      74          90          85
语文   004      005      NULL     96          88          NULL
*/

#5

if  object_id('table_01') is not null drop table table_01
create table table_01(nXH int, Type varchar(500), student varchar(500), score varchar(500))
insert into table_01
select 1 , '数学'   , '003',    '74'  union all
select 2 , '数学'  ,  '001'  ,  '90'  union all
select 3 , '数学'  ,  '002'  ,  '85' 

select * from  table_01


declare @sql varchar(8000)
set @sql = ''
select @sql =@sql+ ', max( case when  cast(nXH as varchar) ='''+cast(nXH as varchar)+''' then student  else  ''000'' end ) as "student'+ cast(nXH as varchar)+'" '
from table_01  group by  nXH 

select @sql =@sql+ ', max( case when  cast(nXH as varchar) ='''+cast(nXH as varchar)+''' then score  else  ''000'' end ) as "score'+ cast(nXH as varchar)+'" '
from table_01  group by  nXH 

set @sql = 'select type '+ @sql +' from table_01  group by Type '

print(@sql)
exec (@sql)
Type student1,student2,student3,score1,score2,score3 
数学    003      001        002    74      90  85