create table t1 ( time1 varchar(50) , name varchar(50),score int )insert t1
select'1999' ,'a',80 union all
select'1999' ,'b',70 union all
select'1999' ,'c',65 union all
select'1999' ,'d',70 union all
select'2000' ,'a',62 union all
select'2000' ,'b',80 union all
select'2000' ,'c',67 union all
select'2000' ,'d',70 select * from t1
drop table t1
得到排名表
年份 a b c d
select'1999' ,'a',80 union all
select'1999' ,'b',70 union all
select'1999' ,'c',65 union all
select'1999' ,'d',70 union all
select'2000' ,'a',62 union all
select'2000' ,'b',80 union all
select'2000' ,'c',67 union all
select'2000' ,'d',70 select * from t1
drop table t1
得到排名表
年份 a b c d
a b c
7 aa 153
9 aa 152
6 aa 120
8 aa 168
5 bb 159
7 bb 179
8 bb 149
9 bb 139
6 bb 169
对b列中的值来分类排序并分别加一序号,形成一新表
px a b c
1 6 aa 120
2 9 aa 152
3 7 aa 153
4 8 aa 168
1 9 bb 139
2 8 bb 149
3 5 bb 159
4 6 bb 169
5 7 bb 179
declare @tab table(a int,b varchar(2),c int)insert @tab values(7,'aa',153)
insert @tab values(9,'aa',152)
insert @tab values(6,'aa',120)
insert @tab values(8,'aa',168)
insert @tab values(5,'bb',159)
insert @tab values(7,'bb',179)
insert @tab values(8,'bb',149)
insert @tab values(9,'bb',139)
insert @tab values(6,'bb',169)select * from @tabselect px=(select count(1) from @tab where b=a.b and c<a.c)+1 , a,b,c from @tab a
order by b , c px a b c
----------- ----------- ---- -----------
1 6 aa 120
2 9 aa 152
3 7 aa 153
4 8 aa 168
1 9 bb 139
2 8 bb 149
3 5 bb 159
4 6 bb 169
5 7 bb 179(所影响的行数为 9 行)
在上面例中我们看到,以B分类排序,C是从小到大,如果C从大到小排序,即下面结果:
px a b c
1 8 aa 168
2 9 aa 153
3 7 aa 152
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139declare @tab table(a int,b varchar(2),c int)insert @tab values(7,'aa',153)
insert @tab values(9,'aa',152)
insert @tab values(6,'aa',120)
insert @tab values(8,'aa',168)
insert @tab values(5,'bb',159)
insert @tab values(7,'bb',179)
insert @tab values(8,'bb',149)
insert @tab values(9,'bb',139)
insert @tab values(6,'bb',169)select * from @tabselect px=(select count(1) from @tab where b=a.b and c>a.c)+1 , a,b,c from @tab a
order by b , c descpx a b c
----------- ----------- ---- -----------
1 8 aa 168
2 7 aa 153
3 9 aa 152
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139(所影响的行数为 9 行)
Select
time1 As 年份,
SUM(Case name When 'a' Then score Else 0 End) As a,
SUM(Case name When 'b' Then score Else 0 End) As b,
SUM(Case name When 'c' Then score Else 0 End) As c,
SUM(Case name When 'd' Then score Else 0 End) As d
From
t1
Group By
time1
--如果name不固定
Declare @S varchar(8000)
Select @S = 'Select time1 As 年份'
Select @S = @S + ' , SUM(Case name When ''' + name + ''' Then score Else 0 End) As ' + name
From t1 Group By Name
Select @S = @S + ' From t1 Group By time1'
EXEC(@S)
select'1999' ,'a',80 union all
select'1999' ,'b',70 union all
select'1999' ,'c',65 union all
select'1999' ,'d',70 union all
select'2000' ,'a',62 union all
select'2000' ,'b',80 union all
select'2000' ,'c',67 union all
select'2000' ,'d',70 select time1,
max(case when name = 'a' then px else null end) a,
max(case when name = 'b' then px else null end) b,
max(case when name = 'c' then px else null end) c,
max(case when name = 'd' then px else null end) d
from
(
select px=(select count(1) from t1 where time1=a.time1 and score>a.score)+1 , time1 , name from t1 a
) t
group by time1drop table t1
/*
time1 a b c d
---------- ----------- ----------- ----------- -----------
1999 1 2 4 2
2000 4 1 3 2(所影响的行数为 2 行)
*/
create table t1 ( time1 varchar(10) , name varchar(50),score int )insert t1
select'1999' ,'a',80 union all
select'1999' ,'b',70 union all
select'1999' ,'c',65 union all
select'1999' ,'d',70 union all
select'2000' ,'a',62 union all
select'2000' ,'b',80 union all
select'2000' ,'c',67 union all
select'2000' ,'d',70
godeclare @sql varchar(8000)
set @sql = 'select time1'
select @sql = @sql + ' , max(case name when ''' + name + ''' then px end) [' + name + ']'
from (select distinct name from (select px=(select count(1) from t1 where time1=a.time1 and score>a.score)+1 , time1 , name from t1 a) t) as a
set @sql = @sql + ' from (select px=(select count(1) from t1 where time1=a.time1 and score>a.score)+1 , time1 , name from t1 a) t group by time1'
exec(@sql) drop table t1/*
time1 a b c d
---------- ----------- ----------- ----------- -----------
1999 1 2 4 2
2000 4 1 3 2(所影响的行数为 2 行)
*/
-----------------------------------
动态的我怎么还是不太明白呢
动态的sql和存储过程又有什么区别的?我见都用到局部变量啊,还是没搞懂啊
给说一下啊