t-sql 选择查询 [英] t-sql select query

问题描述

基于下表

Title Jul-10  Aug-10 Sep-10 Oct-10 Nov-10  Dec-10 Jan-11  Feb-11 Mar-11 Apr-11 May-11 Jun-11
--------------------------------------------------------------------------------------------
A      Null    M1     Null    M2     Null   Null   Null    Null   M3     Null   Null   Null
B      Null    M1     Null    Null   Null   Null   M2      Null   Null   Null   Null   Null
C      Null    Null   Null    Null   Null   M1     Null    Null   Null   Null   Null   Null

如何只选择某个范围内的列.

How can i select only the columns between a certain range.

For instance if input variables are:
-------------------------------------
@start = 'Oct-10'
@end = 'Apr-11'

然后输出将是:

Title  Oct-10  Nov-10  Dec-10  Jan-11   Feb-11   Mar-11   Apr-11
-----------------------------------------------------------------
A      M2      Null    Null     Null     Null     M3       Null   
B      Null    Null    Null     M2       Null     Null     Null
C      Null    Null    M1       Null     Null     Null     Null

推荐答案

这里有一个更简单的方法来做你的数据透视，使用一个名为 pivot_query 的方便的存储过程(代码在这里，此处的示例).通过这种方式，您可以使用开始日期和结束日期条件来首先限制要透视的数据，从而限制您在透视之后获得的列.

Here is an easier way to do your pivot, using a handy stored procedure named pivot_query (code is here, examples here ). This way you use your start and end date criteria to first limit the data to be pivoted, thus limiting the columns you get after the pivot.

fn_MonthRange() 函数是一个递归 CTE，它提供开始日期和结束日期之间相隔一个月的日期表，然后您将其 OUTER 连接到您的数据.这将填补任何缺失的月份.

The fn_MonthRange() function is a recursive CTE that provides a table of dates a month apart between the start and end dates, which you then OUTER join to your data. That will fill in any missing months.

(fn_DateRange() 类似，但适用于任意时间段，例如每 15 分钟"、每小时、每 3 天等)

(fn_DateRange() is similar, but works for arbitrary time segments like "every 15 minutes", every hour, every 3 days etc.)

    create table #testdata
       (
       id          integer,
       Title       varchar(20),
       TheDate     datetime,
       Metadata    varchar(20)
       )
    go


    insert into #testdata values(1,'A','08/01/2010','M1')
    insert into #testdata values(1,'A','10/05/2010','M2')
    insert into #testdata values(1,'A','03/15/2011','M3')
    insert into #testdata values(2,'B','09/20/2010','M1')
    insert into #testdata values(2,'B','01/15/2011','M2')
    insert into #testdata values(3,'C','12/15/2010','M1')
    go

    declare @mySQL       varchar(MAX);
    declare @StartDate   varchar(20);
    declare @EndDate     varchar(20);

    set @StartDate = '08/01/2010';
    set @EndDate   = '03/15/2011';

    set @mySQL = '
    select
       id,
       Title,
       Left(Datename(month, TheDate),3) + ''-'' + right(cast(Year(theDate) as varchar(4)),2) monyr,
       Metadata
    from
   dbo.fn_MonthRange( ''' + @StartDate + ''',''' + @EndDate + ''') dr

   LEFT OUTER JOIN #testdata td
      on (td.TheDate between dr.startdate and dr.enddate )
where
   dr.StartDate between ''' + @StartDate + ''' and ''' + @EndDate + '''';

    exec pivot_query @mySQL, 'Title', 'monyr','max(Metadata)'
    go

    Result:
Title                Aug-10               Dec-10               Feb-11               Jan-11               Mar-11               Nov-10               Oct-10               Sep-10               
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 
A                    M1                   NULL                 NULL                 NULL                 M3                   NULL                 M2                   NULL                 
B                    NULL                 NULL                 NULL                 M2                   NULL                 NULL                 NULL                 M1                   
C                    NULL                 M1                   NULL                 NULL                 NULL                 NULL                 NULL                 NULL                 
None                 NULL                 NULL                 None                 NULL                 NULL                 None                 NULL                 NULL                 

这篇关于t-sql 选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！