求大牛们指点个SQL。

[hzcenter]

表如下：

id product indate recorder
1 a 2009-5-30 recorder1
2 b 2009-5-30 recorder2
3 c 2009-5-31 recorder1
4 d 2009-5-31 recorder1
5 e 2009-5-29 recorder3
6 f 2009-5-29 recorder4
7 g 2009-5-29 recorder5

希望的到的结果是：

2009-5-29 3 recorder3,recorder4,recorder5
2009-5-30 2 recorder1,recorder2
2009-5-31 2 recorder1

就是得到该天的数据记录共几条，并且记录员的名字都列出来（格式不一定要上面写的那样）。id和product暂没有考虑

[anwsp]

mark

[Andy-W]

Use test
GO
Set Nocount On
--> --> (Andy)生成測試數據 20090531
 
if not object_id('test') is null
	drop table test
Go
Create table test([id] int,[product] nvarchar(1),[indate] Datetime,[recorder] nvarchar(9))
Insert test
select 1,N'a','2009-5-30',N'recorder1' union all
select 2,N'b','2009-5-30',N'recorder2' union all
select 3,N'c','2009-5-31',N'recorder1' union all
select 4,N'd','2009-5-31',N'recorder1' union all
select 5,N'e','2009-5-29',N'recorder3' union all
select 6,N'f','2009-5-29',N'recorder4' union all
select 7,N'g','2009-5-29',N'recorder5'
Go
--2005
Select a.indate,b.recorder 
	From test As a
	Outer Apply(Select Stuff((Select ','+recorder From test Where indate=a.indate For Xml Path('')),1,1,'') As recorder) As b
	Group By a.indate,b.recorder

[寻找Python之禅]

牛人就是多，学习了

[gzhantonio]

很实用，学习，mark

[liangCK]

问题描述：
无论是在sql 2000，还是在 sql 2005 中,都没有提供字符串的聚合函数，
  所以，当我们在处理下列要求时，会比较麻烦：
有表tb, 如下：
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc
需要得到结果：
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即， group by id, 求 value 的和（字符串相加）

1. 旧的解决方法

-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value
    FROM tb
    WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数

SELECt id, values=dbo.f_str(id) 
FROM tb 
GROUP BY id

-- 2. 新的解决方法(适用于2005及以后版本)
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'

-- 查询处理
SELECT *
FROM(
    SELECT DISTINCT 
        id
    FROM @t
)A
OUTER APPLY(
    SELECT 
        [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM @t N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N

/*--结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc
(2 行受影响)
--*/

CSDN 社区帖子地址 

附: 合并与分拆的CLR， sql2005的示例中有：
在安装sql 2005的示例后，默认安装目录为
drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities中

[liangCK]

-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@indate DATETIME)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + recorder 
    FROM tb
    WHERE indate = @indate
    RETURN STUFF(@r, 1, 1, '')
END
GO

SELECT indate,COUNT(*), dbo.f_str(indate)
FROM tb
GROUP BY indate

[hzcenter]

那我计算indate数是用count(indate)吗？

统计当天记录数

[百年树人]

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[product] varchar(1),[indate] datetime,[recorder] varchar(9))
insert [tb]
select 1,'a','2009-5-30','recorder1' union all
select 2,'b','2009-5-30','recorder2' union all
select 3,'c','2009-5-31','recorder1' union all
select 4,'d','2009-5-31','recorder1' union all
select 5,'e','2009-5-29','recorder3' union all
select 6,'f','2009-5-29','recorder4' union all
select 7,'g','2009-5-29','recorder5'

---创建字符连接函数---
create function F_Str(@col1 datetime)
returns nvarchar(1000)
as
begin
    declare @S nvarchar(1000) 
    select
      @S=isnull(@S+',','')+recorder
    from 
      tb
    where
      datediff(dd,indate,@col1)=0
    return @S
end 

---查询---
select 
  convert(varchar(10),indate,120) as indate,
  count(1) as cnt,
  dbo.f_str(indate) as recorder
from 
  tb
group by
  indate

---结果---
indate     cnt         recorder                          
---------- ----------- ----------------------------------
2009-05-29 3           recorder3,recorder4,recorder5
2009-05-30 2           recorder1,recorder2
2009-05-31 2           recorder1,recorder1

（所影响的行数为 3 行）

[JonasFeng]

IF OBJECT_ID('LI') IS NOT NULL
   DROP TABLE LI 

 

CREATE TABLE LI (id INT ,product VARCHAR(20),indate smalldatetime,recorder VARCHAR(20)) 
INSERT INTO LI (id,product,indate,recorder)
SELECT 1, 'a', '2009-5-30', 'recorder1' UNION ALL
SELECT 2, 'b', '2009-5-30', 'recorder2' UNION ALL
SELECT 3, 'c', '2009-5-31 ', 'recorder1' UNION ALL
SELECT 4, 'd', '2009-5-31', 'recorder1' UNION ALL
SELECT 5, 'e', '2009-5-29', 'recorder3' UNION ALL
SELECT 6, 'f', '2009-5-29', 'recorder4' UNION ALL
SELECT 7, 'g', '2009-5-29', 'recorder5'  
 
--SQL语句(一步一个脚印)
SELECT *
FROM(
    SELECT indate,count(1) as mark   
    FROM LI group by indate
)A
OUTER APPLY(
    SELECT 
        [recorders]= STUFF(REPLACE(REPLACE(
            (
                SELECT RECORDER  FROM LI N 
                WHERE indate = A.indate  
                FOR XML AUTO
            ), '<N RECORDER="', ','), '"/>', ''), 1, 1, '')
)N

/*结果
indate                mark            recorders
2009-05-29 00:00:00	3	recorder3,recorder4,recorder5
2009-05-30 00:00:00	2	recorder1,recorder2
2009-05-31 00:00:00	2	recorder1,recorder1
*/

[hzcenter]

先看看，明天加点分再结，谢谢各位大牛们了

[hzcenter]

recorder1,recorder1

这里能不能合并成一个呢？

[百年树人]

[Quote=引用 7 楼 hzcenter 的回复:]
recorder1,recorder1

这里能不能合并成一个呢？
[/Quote]
可以，改一下函数就可以了

---创建字符连接函数---
create function F_Str(@col1 datetime)
returns nvarchar(1000)
as
begin
    declare @S nvarchar(1000) 
    select
      @S=isnull(@S+',','')+recorder
    from 
      (select distinct recorder from tb where datediff(dd,indate,@col1)=0) t     
    return @S
end

---查询---
select 
  convert(varchar(10),indate,120) as indate,
  count(1) as cnt,
  dbo.f_str(indate) as recorder
from 
  tb
group by
  indate

---结果---
indate     cnt         recorder                                
---------- ----------- ---------------------------------------
2009-05-29 3           recorder3,recorder4,recorder5
2009-05-30 2           recorder1,recorder2
2009-05-31 2           recorder1

（所影响的行数为 3 行）

[数字化从业者老高]

同意二楼的。