代码之家 › 专栏 › 技术社区 › Raghav

查询以在图表中显示结果

charts sql-server-2005 sql-server

1

Raghav · 技术社区 · 16 年前

 DECLARE @SONGS TABLE
 (
    [ID] INT IDENTITY,
    [SONGNAME] VARCHAR(20),
    [CREATEDDATE] DATETIME
 )


 INSERT INTO @SONGS
 SELECT 'SONG1','20091102' UNION ALL
 SELECT 'SONG2','20091103' UNION ALL
 SELECT 'SONG3','20091107' UNION ALL
 SELECT 'SONG4','20091107' UNION ALL
 SELECT 'SONG5','20091107' UNION ALL
 SELECT 'SONG6','20091109'

现在用户将从外部传递开始日期和结束日期作为如下参数

DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME 

SET @STARTDATE='20091101'
SET @ENDDATE='20091111'

现在,用户还有另外一个选项(比如@option VARCHAR(20)),无论他是否希望将日期分为开始日期和结束日期之间的各个日期,第二个选项他可以选择将日期分为开始日期和结束日期之间的月份,类似于年份。

--OUTPUT I NEED IS when @OPTION IS DATE

DATE        [SONGCOUNT]
------------------------------------------
20091101       0
20091102       1
20091103       1
20091104       0
20091105       0
20091106       0
20091107       3
20091108       0
20091109       1
20091110       0
20091111       0

我的目标是在X轴上显示日期,在y轴上计数,你能给我一个实现同样的方法吗。

2 回复 | 直到 16 年前

1

Damir Sudarevic 16 年前

DECLARE @dimDate TABLE (
 myDate datetime
,dt int
,yr int
,ym int
)

DECLARE  @dte datetime

SET @dte = @STARTDATE
WHILE @dte <= @ENDDATE
    BEGIN
       INSERT INTO @dimDate (myDate, dt, yr, ym) 
         VALUES(
             @dte
            ,datepart(yy,@dte)*10000+ datepart(mm,@dte)*100 + datepart(dd,@dte)
            ,datepart(yy,@dte)
            ,datepart(yy,@dte)*100+ datepart(mm,@dte)
            )
        SET @dte = dateadd(dd,1,@dte)
    END

DECLARE @option varchar(2)
SET @option ='dt'

.

-- per day
IF @option ='dt'
BEGIN
SELECT d.dt, COUNT(s.ID) AS "song_count"
  FROM @dimDate AS d 
    LEFT JOIN @SONGS AS s ON d.myDate = s.CREATEDDATE
  GROUP BY d.dt
END

-- per year
IF @option ='yr'
BEGIN
SELECT d.yr, COUNT(s.ID) AS "song_count"
  FROM @dimDate AS d 
    LEFT JOIN @SONGS AS s ON d.myDate = s.CREATEDDATE
  GROUP BY d.yr
END

.

-- per year-month
IF @option ='ym'
BEGIN
SELECT d.ym, COUNT(s.ID) AS "song_count"
  FROM @dimDate AS d 
    LEFT JOIN @SONGS AS s ON d.myDate = s.CREATEDDATE
  GROUP BY d.ym
END

2

0

Community Mohan Dere 9 年前

这种查询称为交叉表查询

供参考 SQL Server PIVOT examples