----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-07 16:14:40
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([计算机名] varchar(6),[开始时间] datetime,[结束时间] datetime,[姓名] varchar(8),[部门] varchar(5))
insert [huang]
select 'zhuji1','2001-01-01 00:00:00','2001-02-02 00:00:00','zhangsan','sale' union all
select 'zhuji1','2002-01-01 00:00:00','2002-03-03 00:00:00','lisi','sale2' union all
select 'zhuji2','2001-05-05 00:00:00','2002-03-03 00:00:00','wangwu','sale2' union all
select 'zhuji2','2002-05-05 00:00:00','2003-06-06 00:00:00','zhangsan','sale' union all
select 'zhuji2','2004-01-01 00:00:00','2004-03-03 00:00:00','lisi','sale2'
--------------开始查询-------------------------
select [计算机名],MIN([开始时间])[开始时间],MAX([结束时间])[结束时间],stuff((select ','+[姓名] from [huang] b
where b.[计算机名]=a.[计算机名]
for xml path('')),1,1,'') [姓名],stuff((select ','+[部门] from [huang] b
where b.[计算机名]=a.[计算机名]
for xml path('')),1,1,'')[部门]
from [huang] a
GROUP BY [计算机名]----------------结果----------------------------
/*
计算机名 开始时间 结束时间 姓名 部门
------ ----------------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
zhuji1 2001-01-01 00:00:00.000 2002-03-03 00:00:00.000 zhangsan,lisi sale,sale2
zhuji2 2001-05-05 00:00:00.000 2004-03-03 00:00:00.000 wangwu,zhangsan,lisi
*/
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-07 16:14:40
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([计算机名] varchar(6),[开始时间] datetime,[结束时间] datetime,[姓名] varchar(8),[部门] varchar(5))
insert [huang]
select 'zhuji1','2001-01-01 00:00:00','2001-02-02 00:00:00','zhangsan','sale' union all
select 'zhuji1','2002-01-01 00:00:00','2002-03-03 00:00:00','lisi','sale2' union all
select 'zhuji2','2001-05-05 00:00:00','2002-03-03 00:00:00','wangwu','sale2' union all
select 'zhuji2','2002-05-05 00:00:00','2003-06-06 00:00:00','zhangsan','sale' union all
select 'zhuji2','2004-01-01 00:00:00','2004-03-03 00:00:00','lisi','sale2'
--------------开始查询-------------------------
select [计算机名],MIN([开始时间])[开始时间],MAX([结束时间])[结束时间],stuff((select ','+[姓名] from [huang] b
where b.[计算机名]=a.[计算机名]
for xml path('')),1,1,'') [姓名],stuff((select ','+[部门] from [huang] b
where b.[计算机名]=a.[计算机名]
for xml path('')),1,1,'')[部门]
from [huang] a
GROUP BY [计算机名]----------------结果----------------------------
/*
计算机名 开始时间 结束时间 姓名 部门
------ ----------------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
zhuji1 2001-01-01 00:00:00.000 2002-03-03 00:00:00.000 zhangsan,lisi sale,sale2
zhuji2 2001-05-05 00:00:00.000 2004-03-03 00:00:00.000 wangwu,zhangsan,lisi
*/
你在后面加WHERE是可以的 也可以不加 不过加了以后过滤掉很多数据 效率估计高点。但是上面那个一定要加WHERE的
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-07 16:14:40
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([计算机名] varchar(6),[开始时间] datetime,[结束时间] datetime,[姓名] varchar(8),[部门] varchar(5))
insert [huang]
select 'zhuji1','2001-01-01 00:00:00','2001-02-02 00:00:00','zhangsan','sale' union all
select 'zhuji1','2002-01-01 00:00:00','2002-03-03 00:00:00','lisi','sale2' union all
select 'zhuji2','2001-05-05 00:00:00','2002-03-03 00:00:00','wangwu','sale2' union all
select 'zhuji2','2002-05-05 00:00:00','2003-06-06 00:00:00','zhangsan','sale' union all
select 'zhuji2','2004-01-01 00:00:00','2004-03-03 00:00:00','lisi','sale2'
--------------开始查询-------------------------
SELECT [计算机名] ,
MIN([开始时间]) [开始时间] ,
MAX([结束时间]) [结束时间] ,
STUFF(( SELECT ',' + [姓名]
FROM [huang] b
WHERE b.[计算机名] = a.[计算机名]
FOR
XML PATH('')
), 1, 1, '') [姓名] ,
STUFF(( SELECT ',' + [部门]
FROM [huang] b
WHERE b.[计算机名] = a.[计算机名]
FOR
XML PATH('')
), 1, 1, '') [部门]
FROM [huang] a
WHERE [结束时间]<'2004-03-03'
GROUP BY [计算机名]----------------结果----------------------------
/*
计算机名 开始时间 结束时间 姓名 部门
------ ----------------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
zhuji1 2001-01-01 00:00:00.000 2002-03-03 00:00:00.000 zhangsan,lisi sale,sale2
zhuji2 2001-05-05 00:00:00.000 2003-06-06 00:00:00.000 wangwu,zhangsan,lisi
*/
select cpname,MIN(starttime),MAX(endtime),(select ','+username from TestTable b
where b.cpname=a.cpname and b.endtime < '2004-03-01'
for xml path(''))
,
(select ','+dpartment from TestTable b
where b.cpname=a.cpname and b.endtime < '2004-03-01'
for xml path(''))
from TestTable a
where endtime < '2004-03-01'
GROUP BY cpname
zhuji2 2001-05-05 00:00:00 2003-06-06 00:00:00 ,wangwu,zhangsan ,sale2,sale
zhuji1 2001-01-01 00:00:00 2002-03-03 00:00:00 ,zhangsan,lisi ,sale,sale2
zhuji2 2001-05-05 00:00:00 2004-03-03 00:00:00 ,wangwu,zhangsan ,sale2,sale谢谢你。现在终于清楚不是我的问题了。