sql 查询问题
0
悬赏园豆：20
[已解决问题]
解决于 2013-05-14 09:50
如题：
A
A
A
B
B
A
A
C
C
B
查询结果如下：
A 3
B 2
A 2
C 2
B 1
最佳答案
1
with t as (select Row_Number() over (order by ID ASC) as Rank, * from test)
, b as (
select Test,(select count(Test) from t where t.Rank<t1.Rank and t.Test=t1.Test ) as count1 from t as t1 )
select Test,count(count1) as count1 from b group by Test
收获园豆：10
其他回答(8)
0
请详细问题
0
好像挺难的，等结果。
0
你.....这是怎么想出来的
1
用游标？
游标已解决但是我想用select 直接查询出来出来
--附上游标
CREATE TABLE #Tmp ( col varchar(50), Sum_Num int ) DECLARE @Col VARCHAR(50)-- DECLARE @Tmp_Col VARCHAR(50)--中间变量 DECLARE @Sum_Num INT--统计数 SELECT @Sum_Num=0 declare Cur_Count Cursor for select col from dbo.T1 open Cur_Count fetch next from Cur_Count into @Col while(@@FETCH_STATUS<>-1) begin IF(@Col=@Tmp_Col) BEGIN --直接更新数据 SELECT @Sum_Num=@Sum_Num+1 UPDATE #Tmp SET Sum_Num=@Sum_Num WHERE col=@Col AND Sum_Num=@Sum_Num-1 END ELSE BEGIN SELECT @Sum_Num=1 SELECT @Tmp_Col=@Col INSERT INTO #Tmp ( col, Sum_Num ) VALUES ( @Col,@Sum_Num) END fetch next from Cur_Count into @Col end close Cur_Count deallocate Cur_Count SELECT * FROM #Tmp -- drop table #Tmp
@like%'远远'%: 只有一个字段吗？估计够呛
@捂汗:
你可以人为的增加查询列 如： row_number over...
@like%'远远'%: 你的想法哪来的， 用于什么场景？？
0
咩啊？上面的是数据库中的样子，下面的是你想要的结果，你要一个查询的sql语句？
0
这个规则的统计，还是用程序来搞定吧
0
程序解决+1
1
create table #test (id int identity(1,1), val varchar(20)) insert into #test(val) values ('A') ,('A') ,('A') ,('B') ,('B') ,('A') ,('A') ,('C') ,('C') ,('B') --select * from #test select base.val, ISNULL(nextId - id , 1) as [count] from ( select t.id, t.val, previousval = (select top 1 t2.val from #test t2 where t2.id < t.id order by t2.id desc) , nextId = (select top 1 t2.id from #test t2 where t2.id > t.id and t2.val <> t.val order by t2.id) from #test t ) base where base.val <> base.previousval or base.previousval is null drop table #test
收获园豆：10