if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([value1] int,[value2] int)
insert [tb]
select 1,12 union all
select 1,13 union all
select 1,23 union all
select 0,14 union all
select 0,15 union all
select 1,16 union all
select 0,23 union all
select 0,22 union all
select 1,21 union all
select 1,12 树哥上面我已经理解了,
呵呵,我就是想弄成我楼上的那种效果,以前好像看到树哥做个这样的,可忘了是什么时候
就是想给这个表查询时增加一列排序的,结果就是我楼上的那样
value1 value2 COL3
----------- -----------
1 12 1
1 13 2
1 23 3
0 14 1
0 15 2
1 16 1
0 23 1
0 22 2
1 21 1
1 12 2 就实现COL3这种排序,
go
create table [tb]([value1] int,[value2] int)
insert [tb]
select 1,12 union all
select 1,13 union all
select 1,23 union all
select 0,14 union all
select 0,15 union all
select 1,16 union all
select 0,23 union all
select 0,22 union all
select 1,21 union all
select 1,12 树哥上面我已经理解了,
呵呵,我就是想弄成我楼上的那种效果,以前好像看到树哥做个这样的,可忘了是什么时候
就是想给这个表查询时增加一列排序的,结果就是我楼上的那样
value1 value2 COL3
----------- -----------
1 12 1
1 13 2
1 23 3
0 14 1
0 15 2
1 16 1
0 23 1
0 22 2
1 21 1
1 12 2 就实现COL3这种排序,
go
create table [tb]([value1] int,[value2] int)
insert [tb]
select 1,12 union all
select 1,13 union all
select 1,23 union all
select 0,14 union all
select 0,15 union all
select 1,16 union all
select 0,23 union all
select 0,22 union all
select 1,21 union all
select 1,12 alter table tb add value3 int
declare @i int,@j int
update tb set value3=@j,@j=case when @i <> value1 then 1 else isnull(@j,0)+1 end,@i=value1
select * from tbalter table tb drop column value3
/*
value1 value2 value3
----------- ----------- -----------
1 12 1
1 13 2
1 23 3
0 14 1
0 15 2
1 16 1
0 23 1
0 22 2
1 21 1
1 12 2(10 行受影响)*/
update col3 = @I,@i = case when @value1 = value1 then @i +1 else 0 end,@value1 = value1
set @i = 0
update col3 = @I,@i = case when @value1 = value1 then @i +1 else 0 end,@value1 = value1
晕,慢了一步
go
create table [tb]([value1] int,[value2] int)
insert [tb]
select 1,12 union all
select 1,13 union all
select 1,23 union all
select 0,14 union all
select 0,15 union all
select 1,16 union all
select 0,23 union all
select 0,22 union all
select 1,21 union all
select 1,12
alter table tb add id int
declare @n int,@k int,@flag int
set @flag=1
set @n=0
set @k=0
update tb
sET ID=@n,
@flag=case when value1=@k then 1 else 0 end,
@k=value1,
@n= case when @flag=1 then @n+1 else 1 end
select * from tb
/*
(10 行受影响)
value1 value2 id
----------- ----------- -----------
1 12 1
1 13 2
1 23 3
0 14 1
0 15 2
1 16 1
0 23 1
0 22 2
1 21 1
1 12 2 */
go
create table [tb]([value1] int,[value2] int)
insert [tb]
select 1,12 union all
select 1,13 union all
select 1,23 union all
select 0,14 union all
select 0,15 union all
select 1,16 union all
select 0,23 union all
select 0,22 union all
select 1,21 union all
select 1,12 select id=identity(int,1,1),* into # from tb
select
value1,value2,col3=(select count(1) from # where [value1]=a.[value1] and id<=a.id
and id>=(select isnull(max(id),0) from # where id<a.id and value1!=a.value1))
from # a--结果:
value1 value2 col3
----------- ----------- -----------
1 12 1
1 13 2
1 23 3
0 14 1
0 15 2
1 16 1
0 23 1
0 22 2
1 21 1
1 12 2
俺每次都没想到用这方法。汗
这里的UPDATE 貌似先更新变量 然后再赋值的吧
然后再中间,最后才col3 = @I这个???
变量优先,从左到右,逐行循环
其实PT的思路很巧妙 ~~。。
24楼这句不也是临时表
不过没有标识列还真是不好写
--结果:
value1 value2 col3
----------- ----------- -----------
1 12 1
1 13 2
1 23 3
1 26 4
1 31 5
1 42 6
0 14 1
0 15 2
0 21 3
0 22 4
除了先用临时表排序还有别的方法么
--2K CASE WHEN
go
create table [tb]([value1] int,[value2] int)
insert [tb]
select 1,12 union all
select 1,13 union all
select 1,23 union all
select 0,14 union all
select 0,15 union all
select 1,16 union all
select 0,23 union all
select 0,22 union all
select 1,21 union all
select 1,12 select *,col3=row_number()over(partition by value1 order by value2) from tb order by 1 desc
/*
value1 value2 col3
----------- ----------- --------------------
1 12 1
1 12 2
1 13 3
1 16 4
1 21 5
1 23 6
0 14 1
0 15 2
0 22 3
0 23 4(10 行受影响)
*/
-- 貌似是树哥以前写的排序,只记下来了,到底是不是忘了
--生成测试数据
create table #pm(sname varchar(50),sex varchar(50),cj int,pm int)insert into #pm(sname,sex,cj)
select '李三','男',100
union
select '张武','男',98
union
select '王四','男',87
union
select '刘二','男',87
union
select '李二','女',99
union
select '张四','女',98
union
select '王三','女',100
union
select '刘无','女',80
--实现按性别,分数排名
select sname,sex,cj ,(isnull((select count(1) from #pm where sex=t.sex and cj>t.cj),0)+1) As pm1
from #pm t
order by sex,cj desc
/* 结果
sname sex cj pm1
-------------------------------------------------- -------------------------------------------------- ----------- -----------
李三 男 100 1
张武 男 98 2
王四 男 87 3
刘二 男 87 3
王三 女 100 1
李二 女 99 2
张四 女 98 3
刘无 女 80 4
*/-- 更新
update #pm set pm = (isnull((select count(1) from #pm where sex=t.sex and cj>t.cj),0)+1)
from #pm t-- 查询更新结果
select *
from #pm
/*
sname sex cj pm
-------------------------------------------------- -------------------------------------------------- ----------- -----------
李二 女 99 2
李三 男 100 1
刘二 男 87 3
刘无 女 80 4
王三 女 100 1
王四 男 87 3
张四 女 98 3
张武 男 98 2
*/
-- 排序后的结果
select *
from #pm
order by sex,pm
/*
sname sex cj pm
-------------------------------------------------- -------------------------------------------------- ----------- -----------
李三 男 100 1
张武 男 98 2
王四 男 87 3
刘二 男 87 3
王三 女 100 1
李二 女 99 2
张四 女 98 3
刘无 女 80 4
*/