如何在SQL中获取每个日期的活动记录

I have a table like below:

我有这样一张桌子:

ID  FID MDate       Active
--------------------------
1   1   2009-05-25  1
1   2   2009-05-25  1
1   1   2010-02-04  0
1   3   2010-02-04  1
1   1   2009-04-01  0
1   1   2009-03-01  1

How to get active FId for each date?

如何在每次约会中获得活跃的FId ?

I was trying like below:

我试着说:

SELECT DISTINCT 
    ID, MDate,
    STUFF ((SELECT DISTINCT ',' + CAST(FID AS VARCHAR) 
            FROM 
                (SELECT ID, MDate, FID 
                 FROM Table1 
                 WHERE IsActive = 1) t
            WHERE t.MDate = a.MDate 
            FOR XML PATH('')), 1, 1, '') colb
FROM 
    (SELECT ID, MDate, FID 
     FROM Table1 
     WHERE IsActive = 1) a

Somehow, it is giving partial results. In last row of the result of above query FID 3 is getting selected, there should be two FIDs 2, 3 as FID 2 is active since 2009-05-25 to till date.

某种程度上，它给出了部分结果。在上面的查询结果的最后一排，FID 3被选中，应该有两个fids2, 3作为FID 2，从2009-05-25到日期。

I want output like below:

我希望输出如下:

ID  MDate       FID
1   2009-03-01  1
1   2009-05-25  1,2
1   2010-02-04  2,3

How to get this in SQL?

如何在SQL中获得这个?

3 个解决方案

#1

You need to specify names of fields correctly, and no need for that number of sub-query:

您需要正确地指定字段的名称，而不需要那么多子查询:

;WITH Table1 AS (
SELECT * FROM (VALUES
(1, 1, '2009-05-25', 1),
(1, 2, '2009-05-25', 1),
(1, 1, '2010-02-04', 0),
(1, 3, '2010-02-04', 1),
(1, 1, '2009-04-01', 0),
(1, 1, '2009-03-01', 1))
AS t(ID, FID, ModifyDate, IsActive)
)

SELECT DISTINCT ID,
                ModifyDate,
                STUFF(( SELECT DISTINCT ',' + CAST(FID AS VARCHAR) 
                        FROM Table1 t
                        WHERE t.ModifyDate = a.ModifyDate
                        FOR XML PATH('')),1,1,'') colb
FROM Table1 a
WHERE IsActive=1

Results:

结果:

ID          ModifyDate colb
----------- ---------- ----------
1           2009-03-01 1
1           2009-05-25 1,2
1           2010-02-04 1,3

(3 row(s) affected)

#2

I would use a CTE to filter out the active row first

我将使用CTE首先过滤出活动行

; WITH
CTE AS
(
    SELECT  *
    FROM    Table1
    WHERE   IsActive    = 1
)
SELECT  ID, ModifyDate,
    colb    = STUFF (   (   
                    SELECT  ',' + CAST(FID AS VARCHAR(10)) 
                    FROM    CTE x 
                    WHERE   x.ModifyDate = c.ModifyDate 
                    FOR XML PATH ('')
                ) , 1, 1, '')
FROM    CTE c
GROUP BY ID, ModifyDate

#3

SELECT distinct [ID],[MDate],STUFF((SELECT ',' + CAST(FID AS VARCHAR) FROM [dbo].[tableName] WHERE [MDate] = a.[MDate] and active =1 FOR XML PATH('')),1 ,1 ,'') as FID
  FROM [dbo].[tableName] AS a
  WHERE  active = 1
  ORDER BY [MDate]

Sample Output as below:

样例输出如下:

ID  MDate       FID
1   2009-03-01  1
1   2009-05-25  1,2
1   2010-02-04  3

I think this is what you want.

我想这就是你想要的。

#1