有没有办法得到像这样的数据 [英] is there any way to get data like

问题描述

我有桌子，



派对名称|日期

AAA             2013-12-01

BBB             2013-12-03

CCC             2013-12-05

DDD             2013-12-06



我希望结果使用选择查询，例如



派对名称|日期

AAA             2013-12-01

-                   2013-12-02

BBB             2013-12-03

-                  2013-12-04

CCC             2013-12-05

DDD            2013-12-06

I have table like,

Party Name | Date
AAA              2013-12-01
BBB              2013-12-03
CCC              2013-12-05
DDD             2013-12-06

I want result using select query like

Party Name | Date
AAA              2013-12-01
-                   2013-12-02
BBB              2013-12-03
-                  2013-12-04
CCC             2013-12-05
DDD            2013-12-06

推荐答案

如果你是使用 SQL Server 2005 或更高版本，您可以这样做（我使用CTE生成最小和最大日期之间的日期）：

If you're using SQL Server 2005 or above, you can do it like this (I'm using CTE to generate dates between min and max dates):

DECLARE @MinDate DATE, @MaxDate DATE;

SET @MinDate = (SELECT MIN([Date]) FROM [TableName]);
SET @MaxDate = (SELECT MAX([Date]) FROM [TableName]);

;WITH Dates
     AS (SELECT [Date] = @MinDate
         UNION ALL
         SELECT [Date] = DATEADD(DAY, 1, [Date])
         FROM Dates
         WHERE [Date] < @MaxDate)
SELECT ISNULL(t.[Party Name], '-') AS [Party Name],
       d.[Date]
FROM   Dates AS d
       LEFT OUTER JOIN [TableName] AS t
                    ON t.[Date] = d.[Date]
ORDER  BY d.[Date]
OPTION (MAXRECURSION 32767);

快捷方式可能就像

A quick way could be like

SELECT COALESCE(MT.PARTYNAME, TMP.PARTYNAME), TDATE
FROM 
(
   SELECT '2013-12-01' AS TDATE, '-' as PARTYNAME
   UNION ALL
   SELECT '2013-12-02' AS TDATE 
   UNION ALL
   SELECT '2013-12-03' AS TDATE 
   UNION ALL
   SELECT '2013-12-04' AS TDATE 
   UNION ALL
   SELECT '2013-12-05' AS TDATE 
   UNION ALL
   SELECT '2013-12-06' AS TDATE 
) TMP LEFT OUTER JOIN MYTABLE MT ON TMP.TDATE = MT.DATE

这篇关于有没有办法得到像这样的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！