有个成绩表A,里有成绩字段chengji
另有一个表B,里面有chengji1,chengji2,chengji3
要求:取表A中的前三名成绩,分别插入到B的chengji1,chengji2,chengji3中
另有一个表B,里面有chengji1,chengji2,chengji3
要求:取表A中的前三名成绩,分别插入到B的chengji1,chengji2,chengji3中
select
(select top 1 chengji from A order by chengji desc),
(select top 1 chengji from (select top 2 chengji from A order by chengji desc) t order by chengji),
(select top 1 chengji from (select top 3 chengji from A order by chengji desc) t order by chengji)
select
(select top 1 chengji from A order by chengji desc),
(select top 1 chengji from A where chengji not in(select top 1 chengji from A order by chengji desc) order by chengji desc),
(select top 1 chengji from A where chengji not in(select top 2 chengji from A order by chengji desc) order by chengji desc)
SELECT
MAX(CASE WHEN ID=1 THEN chengji ELSE 0 END),
MAX(CASE WHEN ID=2 THEN chengji ELSE 0 END),
MAX(CASE WHEN ID=3 THEN chengji ELSE 0 END)
FROM(
SELECT
chengji,
ROW_NUMBER() OVER(ORDER BY chengji DESC) AS ID
FROM A
) AS A
WHERE ID<=3
INSERT @tb1 VALUES(98)
INSERT @tb1 VALUES(60)
INSERT @tb1 VALUES(50)
INSERT @tb1 VALUES(78)
INSERT @tb1 VALUES(88)DECLARE @tb2 TABLE(chengji1 int,chengji2 int,chengji3 int)INSERT @tb2
SELECT
MAX(CASE WHEN ID=1 THEN chengji ELSE 0 END),
MAX(CASE WHEN ID=2 THEN chengji ELSE 0 END),
MAX(CASE WHEN ID=3 THEN chengji ELSE 0 END)
FROM(
SELECT
chengji,
ROW_NUMBER() OVER(ORDER BY chengji DESC) AS ID
FROM @tb1
) AS A
WHERE ID<=3
SELECT *
FROM @tb2/*
chengji1 chengji2 chengji3
----------- ----------- -----------
98 88 78
*/
create table B(chengji1 int,chengji2 int,chengji3 int)insert into A
select 100 union all
select 60 union all
select 90 union all
select 70 union all
select 88 union all
select 96
insert B(chengji1,chengji2,chengji3)
select
(select top 1 chengji from A order by chengji desc),
(select top 1 chengji from (select top 2 chengji from A order by chengji desc) t order by chengji),
(select top 1 chengji from (select top 3 chengji from A order by chengji desc) t order by chengji)select * from B
/**
chengji1 chengji2 chengji3
----------- ----------- -----------
100 96 90(所影响的行数为 1 行)
**/
select top 3 id = identity(int,1,1) ,* into #from taupdate b
set chenji1 = a.chengji1,chengji2 = a.chengji2 ,chengji3 = a.chengji3
from tb as t
,(select max(case when id = 1 then chengji else 0 end as chengji1) ,
max(case when id = 2 then chengji else 0 end as chengji2 ),
max(case when id = 3 then chengji else 0 end as chengji3 ) from #) b
2005 可用排名函数