在sqlserver中合并数据，求一SQL

[快跑蜗牛哥]

其中cardate是date类型
已知一表：
MANAGERCAR
select * from MANAGERCAR可以得到：
carnumber cardate name depart ...
S5689 2008/4/22 09:00:00 A we
S5689 2008/4/22 09:05:00 x xe
S1234 2008/4/21 08:00:00 B wq
S4856 2008/4/20 09:00:00 C cq
S5689 2008/4/22 09:00:00 W wr
S1234 2008/4/21 08:00:00 E ef
S5689 2008/4/22 03:00:00 R ri
...
现要求得到如下数据，该如何写sql语句，
carnumber cardate name depart...
S5689 2008/4/22 09:00:00 A,x,W we,xe,wr
S5689 2008/4/22 03:00:00 R ri
S1234 2008/4/21 08:00:00 B,E wq,ef
S4856 2008/4/20 09:00:00 C cq
...
(即:carnumber相等与cardate之间的时间相差小于或等于10分中时，合并数据)

[gggg007]

这个有结果有问题吧?

同一个NUMBER不同的姓名?

[fuanwei]

Create FUNCTION 合并列1 (
    @l nvarchar(50)
) RETURNS nvarchar(4000)
AS
BEGIN
    DECLARE @s nvarchar(4000)
    set @s=''
    select @s=@s+','+name from MANAGERCAR where convert(nvarchar(15),cardate,120)=@l
    if (@s is null)
    set @s=''
    RETURN(@s)
END

Create FUNCTION 合并列2 (
    @l nvarchar(50)
) RETURNS nvarchar(4000)
AS
BEGIN
    DECLARE @s nvarchar(4000)
    set @s=''
    select @s=@s+','+depart from MANAGERCAR where convert(nvarchar(15),cardate,120)=@l
    if (@s is null)
    set @s=''
    RETURN(@s)
END

select convert(nvarchar(15),cardate,120),carnumber,dbo.合并列1(convert(nvarchar(15),cardate,120)),dbo.合并列2(convert(nvarchar(15),cardate,120)) from MANAGERCAR group by convert(nvarchar(15),cardate,120),carnumber

思路:
取时间的前15位,精度为10分钟,同样可以取16位为1分钟,14位为1小时

[lxq619]

--函数创建
CREATE function dbo.f_getCol3(@custid varchar(10),@begin_date datetime,@kd int)
returns varchar(500)
as
begin

declare @jg varchar(500)
set @jg = ''
select @jg = @jg+convert(varchar(10),name) +',' from tb where carnumber like @custid and
cardate between @begin_date and dateadd(minute,@kd,@begin_date)
return @jg
end
go
---存储过程

CREATE procedure pro_GetResults
as
begin
declare @v_name varchar(30),
@v_cardate datetime,
@v_carnumber varchar(20),
@flag int,
@v_depart varchar(30),
@v_ID varchar(30)
select @v_name = 0,
@v_carnumber = ''

create table #tb1(
carnumber varchar(20),
cardate datetime,
names varchar(100)，
depart varchar(30)
)
create table #tb2(
name int
)

set @flag = 0
declare cur_test cursor for
select 主键,name,cardate,carnumber,depart from tb order by orderdate asc
open cur_test
fetch next from cur_test into @v_id,@v_name,@v_cardate,@v_carnumber,@v_depart
while @@fetch_status = 0
begin
--判断name是否已经进行了统计
select @flag= count(*) from #tb2 where name like @v_name
if @flag = 0
begin
--print cast(@v_name as varchar)

insert into #tb1--(carnumber,cardate,names,depart)
select @v_carnumber,@v_cardate,dbo.f_getCol3(@v_carnumber,@v_cardate,10)

insert into #tb2--(id)
select 主键 from tb
where cardate between @v_cardate and dateadd(minute,10,@v_cardate)and
carnumber like @v_carnumber
set @flag = 0
end
fetch next from cur_test into @v_id,@v_name,@v_cardate,@v_carnumber,@v_depart
end

close cur_test
deallocate cur_test
select * from #tb1
drop table #tb1
drop table #tb2
end
GO
执行:
exec pro_getresults

比较笨的方法，
对于基准以最小的那个时间记录
纯粹学习交流

[caorobby]

ding.

[caorobby]

干嘛非要一句sql来搞啊..

[肥胖的柠檬]

如果这样呢?


S5689 2008/4/22 09:00:00 A we
S5689 2008/4/22 09:05:00 x xe
S5689 2008/4/22 09:06:00 A we
S5689 2008/4/22 09:11:00 x xe
S5689 2008/4/22 09:12:00 A we

是这样
S5689 2008/4/22 09:00:00 A we
S5689 2008/4/22 09:05:00 x xe
S5689 2008/4/22 09:06:00 A we
---------------------------------------------------------
S5689 2008/4/22 09:11:00 x xe
S5689 2008/4/22 09:12:00 A we

嘛?
还是
S5689 2008/4/22 09:00:00 A we
S5689 2008/4/22 09:05:00 x xe
S5689 2008/4/22 09:06:00 A we
---------------------------------------------------------
S5689 2008/4/22 09:05:00 x xe
S5689 2008/4/22 09:06:00 A we
S5689 2008/4/22 09:11:00 x xe
S5689 2008/4/22 09:12:00 A we
-------------------------------------------------------------
...............

[wangxuelid]

有难度，，待高手，，

[sp4]

可以做是可以,但是这种处理逻辑性太强了,不适合SQL里完成

不建议.......

[dawugui]

[Quote=引用楼主 qw0907 的帖子:]
其中cardate是date类型
已知一表：
MANAGERCAR
select * from MANAGERCAR可以得到：
carnumber cardate name depart ...
S5689 2008/4/22 09:00:00 A we
S5689 2008/4/22 09:05:00 x xe
S1234 2008/4/21 08:00:00 B wq
S4856 2008/4/20 09:00:00 C cq
S5689 …
[/Quote]
没有基准值,没法合并数据.
如:

S5689 2008/4/22 09:00:00 A we
S5689 2008/4/22 09:05:00 x xe
S5689 2008/4/22 09:15:00 x xe
S5689 2008/4/22 09:25:00 x xe
S5689 2008/4/22 09:55:00 x xe
S5689 2008/4/22 09:58:00 x xe
这样的数据如何合并,以谁为基准值?

[-狙击手-]

写一个函数，按小时整点分组可得

[ojuju10]

需要写个字符串合并函数

[nextflying]

关注中

[knowledge_Is_Life]

不会,帮顶