统计连续出现次数

Question

declare @tb table (id int,名字 nvarchar(10))
insert into @tb select 1,'张三'
    union all   select 2,'张三'
    union all   select 3,'李四'
    union all   select 4,'王五'
    union all   select 6,'赵六'
    union all   select 7,'赵六'
    union all   select 8,'赵六'
    
select 名字,次数=count(1),连续的ID号=stuff((select '/'+ LTRIM( id) from @tb where a.名字=名字 order by id desc for XML path('')),1,1,'')  from @tb  a
                    group by 名字  order by 次数 desc
名字         次数          连续的ID号
---------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
赵六         3           8/7/6
张三         2           2/1
李四         1           3
王五         1           4(4 行受影响)

Answer 1

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(id int,名字 varchar(10))
go
insert into tb
select     
1  ,  '张三' union all select
2   , '张三' union all select
3   , '李四' union all select
4   , '王五' union all select
6   , '赵六' union all select
7   , '赵六' union all select
8 ,   '赵六' 
go
create FUNCTION dbo.f_tb(@name varchar(10)) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = '' 
    SELECT @str = @str + '/' + convert (varchar(10),id)  FROM tb WHERE 名字=@name order by id
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 
select 名字,次数=COUNT(*),
连续的id号=dbo.f_tb(名字)
from tb 
group by 名字/*------------
李四 1 3
王五 1 4
张三 2 1/2
赵六 3 6/7/8-------*/

Answer 2

CREATE TABLE TBTEST(id INT, 名字 VARCHAR(10))
INSERT TBTEST
SELECT 1  ,  '张三'  UNION ALL
SELECT 2  ,  '张三'  UNION ALL
SELECT 3  ,  '李四'  UNION ALL
SELECT 4  ,  '王五'  UNION ALL
SELECT 6  ,  '赵六'  UNION ALL
SELECT 7  ,  '赵六'  UNION ALL
SELECT 8  ,  '赵六' ALTER FUNCTION dbo.f_str(@NAME VARCHAR(10))
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + '/' + CONVERT(VARCHAR(10),ID)
    FROM TBTEST
    WHERE 名字 =@NAME  ORDER BY ID DESC
    RETURN STUFF(@r, 1, 1, '')
END
GOSELECT 名字,COUNT(ID)个数,dbo.f_str( 名字) FROM TBTEST GROUP BY 名字名字         个数                                                                                                                                                                                                                                                                           
---------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
李四         1           3
王五         1           4
张三         2           2/1
赵六         3           8/7/6（所影响的行数为 4 行）

Answer 3

--> 测试时间:2009-07-09 23:29:54
--> 我的淘宝: http://shop36766744.taobao.com/if object_id('[tab]') is not null drop table [tab]
create table [tab]([id] int,[名字] varchar(4))
insert [tab]
select 1,'张三' union all
select 2,'张三' union all
select 3,'李四' union all
select 4,'王五' union all
select 6,'赵六' union all
select 7,'赵六' union all
select 8,'赵六'select * from [tab]create function dbo.f_str(@name varchar(20)) returns varchar(100)
as
begin
    declare @str varchar(1000)
    select @str =isnull( @str + '/','') + cast(id as varchar) from tab where 名字 = @name
    return @str
end
goselect 名字, 次数=count(1),连续的id号=dbo.f_str(名字) from tab group by 名字 order by 名字 desc/*
名字   次数          连续的id号                                                                                               
---- ----------- ---------------------------------------------------------------------------------------------------- 
赵六   3           6/7/8
张三   2           1/2
王五   1           4
李四   1           3（所影响的行数为 4 行）
*/drop table tab
drop function dbo.f_str

Answer 4

---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-07-09 23:29:40
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[名字] nvarchar(2))
Insert tb
Select 1,'张三' union all
Select 2,'张三' union all
Select 3,'李四' union all
Select 4,'王五' union all
Select 6,'赵六' union all
Select 7,'赵六' union all
Select 8,'赵六'
Go
--Select * from tb-->SQL查询如下:
;with t as
(
select * 
from tb t 
where exists(
select 1 
from tb 
where 名字=t.名字 
  and (id=t.id-1 or id=t.id+1)
)
)
select 名字,次数=count(1),
    连续的id号=stuff((select ','+ltrim(id) from t a where a.名字=t.名字 for xml path('')),1,1,'')
from t
group by 名字
/*
名字   次数          连续的id号
---- ----------- -----------------------------
张三   2           1,2
赵六   3           6,7,8(2 行受影响)
*/

Answer 5

---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-07-09 23:29:40
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[名字] nvarchar(2))
Insert tb
Select 1,'张三' union all
Select 2,'张三' union all
Select 3,'李四' union all
Select 4,'王五' union all
Select 6,'赵六' union all
Select 7,'赵六' union all
Select 8,'赵六'
Go
--Select * from tb-->SQL查询如下:
--;with t as
--(
-- select * 
-- from tb t 
-- where exists(
-- select 1 
-- from tb 
-- where 名字=t.名字 
--   and (id=t.id-1 or id=t.id+1)
-- )
--)
select 名字,次数=count(1),
    连续的id号=stuff((select '/'+ltrim(id) from tb a where a.名字=tb.名字 for xml path('')),1,1,'')
from tb
group by 名字
order by 2 desc
/*
名字   次数          连续的id号
---- ----------- -----------------------
赵六   3           6/7/8
张三   2           1/2
李四   1           3
王五   1           4(4 行受影响)
*/没看好,被我复杂化了.

Answer 6

---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-07-10 10:43:16
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[名字] nvarchar(2))
Insert tb
Select 1,'张三' union all
Select 2,'张三' union all
Select 3,'李四' union all
Select 4,'王五' union all
Select 6,'赵六' union all
Select 7,'赵六' union all
Select 8,'王五' union all
Select 9,'赵六' 
Go
--Select * from tb-->SQL查询如下:
;with t as
(
    select * 
    from tb t 
    where exists(
        select 1 
        from tb 
        where 名字=t.名字 
          and (id=t.id-1 or id=t.id+1)
        )
)
select top 1 with ties 名字,次数=count(1),
    连续的id号=stuff((select ','+ltrim(id) from t a where a.名字=t.名字 for xml path('')),1,1,'')
from t
group by 名字
order by 2 desc
/*
名字   次数          连续的id号
---- ----------- ---------------------------
张三   2           1,2
赵六   2           6,7(2 行受影响)
*/这样行吗?

Answer 7

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-07-10 10:52:23
-------------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,名字 VARCHAR(4))
INSERT INTO @T
SELECT 1,'张三' UNION ALL
SELECT 2,'张三' UNION ALL
SELECT 3,'李四' UNION ALL
SELECT 4,'王五' UNION ALL
SELECT 6,'赵六' UNION ALL
SELECT 7,'赵六' UNION ALL
SELECT 8,'王五' UNION ALL
SELECT 9,'赵六'--SQL查询如下:SELECT TOP 1 WITH TIES A.名字,COUNT(*)+1 AS [rowcount]
FROM @T AS A
    LEFT JOIN @T AS B
ON A.名字=B.名字 AND A.id = B.id-1
WHERE A.名字=B.名字
GROUP BY A.名字
ORDER BY COUNT(*) DESC;/*
名字   rowcount
---- -----------
张三   2
赵六   2(2 行受影响)*/