首页 新闻 搜索 专区 学院

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

sql
like%'远远'%的主页 like%'远远'% | 小虾三级 | 园豆:656
提问于:2013-05-13 14:51
< >
分享
最佳答案
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
happydaily | 小虾三级 |园豆:674 | 2013-05-13 18:35
其他回答(8)
0

请详细问题

胖子哥哥 | 园豆:341 (菜鸟二级) | 2013-05-13 15:00
0

好像挺难的,等结果。

Wolfe Yu | 园豆:434 (菜鸟二级) | 2013-05-13 15:04
0

你.....这是怎么想出来的

jerry-Tom | 园豆:4077 (老鸟四级) | 2013-05-13 15:07
1

用游标?

捂汗 | 园豆:168 (初学一级) | 2013-05-13 15:10

游标已解决但是我想用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
支持(0) 反对(0) like%'远远'% | 园豆:656 (小虾三级) | 2013-05-13 15:34

@like%'远远'%: 只有一个字段吗?估计够呛

支持(0) 反对(0) 捂汗 | 园豆:168 (初学一级) | 2013-05-13 15:44

@捂汗: 

你可以人为的增加查询列  如: row_number over... 

支持(0) 反对(0) like%'远远'% | 园豆:656 (小虾三级) | 2013-05-13 15:58

@like%'远远'%: 你的想法哪来的, 用于什么场景??

支持(0) 反对(0) 梦愿 | 园豆:165 (初学一级) | 2013-05-13 17:03
0

咩啊?上面的是数据库中的样子,下面的是你想要的结果,你要一个查询的sql语句?

W宁宁 | 园豆:522 (小虾三级) | 2013-05-13 15:25
0

这个规则的统计,还是用程序来搞定吧

chenping2008 | 园豆:9836 (大侠五级) | 2013-05-13 16:43
0

程序解决+1

飞来飞去 | 园豆:2057 (老鸟四级) | 2013-05-13 16:54
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
gunsmoke | 园豆:3592 (老鸟四级) | 2013-05-14 07:30
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册