求大牛们指点个SQL。

Question

表如下：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暂没有考虑

Answer 1

问题描述：
无论是在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中

Answer 2

-- 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
GOSELECT indate,COUNT(*), dbo.f_str(indate)
FROM tb
GROUP BY indate

Answer 3

那我计算indate数是用count(indate)吗？统计当天记录数

Answer 4

---测试数据---
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 行）

Answer 5

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    
    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                            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
*/

Answer 6

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

Answer 7

可以，改一下函数就可以了---创建字符连接函数---
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 行）

Answer 8

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