求一sql 语句 在线等。。。

Question

有一个表
ID     name    Time
1      aa      2009-10-1
2      bb      2009-9-30
3      bb      2009-9-28
4      cc      2009-9-27
5      aa      2009-9-25我要分组成  ID （1），（2，3），（4），（5）这四组

Answer 1

结果
1      aa      2009-10-1 
---------------------------
2      bb      2009-9-30 
3      bb      2009-9-28 
------------------------------
4      cc      2009-9-27 
--------------------------
5      aa      2009-9-25 

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-18 10:37:50
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID] int,[name] varchar(2),[Time] datetime)
insert [tb]
select 1,'aa','2009-10-1' union all
select 2,'bb','2009-9-30' union all
select 3,'bb','2009-9-28' union all
select 4,'cc','2009-9-27' union all
select 5,'aa','2009-9-25'
--------------开始查询--------------------------
select 
  name,
  [ID]=stuff((select ','+ltrim([ID]) from tb t where name=tb.name for xml path('')), 1, 1, ''),
  [Time]=stuff((select ','+ltrim([Time]) from tb t where name=tb.name for xml path('')), 1, 1, '')
from 
  tb 
group by 
  name 
----------------结果----------------------------
/* name ID                                                                                                                                                                                                                                                               Time
---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
aa   1,5                                                                                                                                                                                                                                                              10  1 2009 12:00AM,09 25 2009 12:00AM
bb   2,3                                                                                                                                                                                                                                                              09 30 2009 12:00AM,09 28 2009 12:00AM
cc   4                                                                                                                                                                                                                                                                09 27 2009 12:00AM(3 行受影响)*/

Answer 3

就是有的数据是连续的，如bb，有的是不连续的，如aa，所以连续的放一起，不连续的就单独放

Answer 4

---是不是这样
----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-18 10:37:50
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID] int,[name] varchar(2),[Time] datetime)
insert [tb]
select 1,'aa','2009-10-1' union all
select 2,'bb','2009-9-30' union all
select 3,'bb','2009-9-28' union all
select 4,'cc','2009-9-27' union all
select 5,'aa','2009-9-25'
--------------开始查询--------------------------
select 
  name,
  [ID]=stuff((select ','+ltrim([ID]) from tb t where name=tb.name for xml path('')), 1, 1, ''),
  [Time]=stuff((select ','+convert(varchar(10),[Time],120) from tb t where name=tb.name for xml path('')), 1, 1, '')
from 
  tb 
group by 
  name 
----------------结果----------------------------
/* name ID                                                                                                                                                                                                                                                               Time
---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
aa   1,5                                                                                                                                                                                                                                                              2009-10-01,2009-09-25
bb   2,3                                                                                                                                                                                                                                                              2009-09-30,2009-09-28
cc   4                                                                                                                                                                                                                                                                2009-09-27(3 行受影响)
*/

Answer 5

if OBJECT_ID('tb') is not null drop table tb
create table tb( ID int,name varchar(5),Times date)GO
insert into tb
select 1,'aa','2009-10-1' union all
select 2,'bb','2009-9-30' union all
select 3,'bb','2009-9-28' union all
select 4,'cc','2009-9-27' union all
select 5,'aa','2009-9-25'GOif OBJECT_ID('getGroup') is not null drop function getGroup
GOCreate Function getGroup(

@ID int,
@Name varchar(5)
)
returns intas
begin

While ((select name from tb where ID=(@ID-1) ANd name=@Name) is not null)
begin
SEt @ID=@ID-1
end return @ID
endGO
select ID,name,dbo.getGroup(ID,name) as 组号 from tb(5 行受影响)
ID          name  组号
----------- ----- -----------
1           aa    1
2           bb    2
3           bb    2
4           cc    4
5           aa    5(5 行受影响)