SQL Update的问题

[狼异族]

有两个表

 T_A(name, first,  last, min, max), T_B(name, date，score)

，T_B中存在多个name一样，date不一样的数据，我现在想对同一个name取第一次的分数，最后一次分数，最低分，最高分，专业那个的语句该怎么写？

[二月十六]

--测试数据
if not object_id(N'T_A') is null
	drop table T_A
Go
Create table T_A([name] nvarchar(22),[first] int,[last] int,[min] int,[max] int)
Insert T_A
select N'张三',1,1,1,1 union all
select N'李四',1,1,1,1
GO
if not object_id(N'T_B') is null
	drop table T_B
Go
Create table T_B([name] nvarchar(22),[date] Date,[score] int)
Insert T_B
select N'张三','2018-10-01',80 union all
select N'张三','2018-11-12',100 union all
select N'张三','2018-12-12',90 union all
select N'李四','2018-10-05',85
Go
--测试数据结束
UPDATE dbo.T_A
SET [min] = T.minscore,
    [max] = T.maxscore
FROM
(
    SELECT name,
           MAX(score) maxscore,
           MIN(score) minscore
    FROM dbo.T_B
    GROUP BY name
) t
WHERE t.name = T_A.name

UPDATE dbo.T_A
SET first = T.firstscore,
    last = lastscore
FROM
(
    SELECT t1.name,
           t1.score AS lastscore,
           t2.score AS firstscore
    FROM
    (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY name ORDER BY date DESC) rn
        FROM dbo.T_B
    ) t1
        JOIN
        (
            SELECT *,
                   ROW_NUMBER() OVER (PARTITION BY name ORDER BY date) rn
            FROM dbo.T_B
        ) t2
            ON t2.rn = t1.rn
               AND t2.name = t1.name
    WHERE t1.rn = 1
) t
WHERE t.name=T_A.name


SELECT * FROM dbo.T_A

[Dear SQL(燊)]

if object_id('tempdb..#list') is not null  drop table #list

select name='A', date=CAST('2018/1/1' AS DATETIME),score=90 into #list UNION ALL
select name='A', date=CAST('2018/2/1' AS DATETIME),score=100 UNION ALL
select name='A', date=CAST('2018/3/1' AS DATETIME),score=88 UNION ALL
select name='A', date=CAST('2018/4/1' AS DATETIME),score=98 UNION ALL
select name='A', date=CAST('2018/5/1' AS DATETIME),score=89 UNION ALL
select name='B', date=CAST('2018/5/1' AS DATETIME),score=89 
;
with list as(
	select name,score,descname='first',rid=ROW_NUMBER()over(partition by name order by [date])
	from #list
	union all
	select name,score,descname='last',rid=ROW_NUMBER()over(partition by name order by [date] desc)
	from #list
	union all
	select name,score=min(score),descname='min',rid=1
	from #list
	group by name
	union all
	select name,score=max(score),descname='max',rid=1
	from #list
	group by name
)
select *
from (
	select [name],score,descname
	from list
	where rid=1
	) a pivot( max(score) for descname in([first],[last],[min],[max]))pt

[Dear SQL(燊)]

if object_id('tempdb..#list') is not null  drop table #list

select name='A', date=CAST('2018/1/1' AS DATETIME),score=90 into #list UNION ALL
select name='A', date=CAST('2018/2/1' AS DATETIME),score=100 UNION ALL
select name='A', date=CAST('2018/3/1' AS DATETIME),score=88 UNION ALL
select name='A', date=CAST('2018/4/1' AS DATETIME),score=98 UNION ALL
select name='A', date=CAST('2018/5/1' AS DATETIME),score=89 UNION ALL
select name='B', date=CAST('2018/5/1' AS DATETIME),score=89 
;
with list as(
	select name,score,descname='first',rid=ROW_NUMBER()over(partition by name order by [date])
	from #list
	union all
	select name,score,descname='last',rid=ROW_NUMBER()over(partition by name order by [date] desc)
	from #list
	union all
	select name,score=min(score),descname='min',rid=1
	from #list
	group by name
	union all
	select name,score=max(score),descname='max',rid=1
	from #list
	group by name
)
update ta set [first]=pt.[first],[last]=pt.[last],[min]=pt.[min],[max]=pt.[max]
from (
	select [name],score,descname
	from list
	where rid=1
	) a pivot( max(score) for descname in([first],[last],[min],[max]))pt
inner join t_a ta on pt.name=ta.name

[RINK_1]

WITH CTE
AS
(SELECT * ,
ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY DATE) AS RANK_DATE,
ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY SCORE) AS RANK_SCORE,
COUNT(*) OVER (PARTITION BY NAME) AS QTY
FROM T_B)

SELECT NAME,
MAX(CASE WHEN RANK_DATE=1 THEN SCORE ELSE 0 END) AS FIRST,
MAX(CASE WHEN RANK_DATE=QTY THEN SCORE ELSE 0 END) AS LAST,
MAX(CASE WHEN RANK_SCORE=1 THEN SCORE ELSE 0 END) AS [MIN],
MAX(CASE WHEN RANK_SCORE=QTY THEN SCORE ELSE 0 END) AS [MAX]
FROM CTE
GROUP BY NAME

[xiaoxiangqing]

比较好的办法，是先按分数排好序