求个语句

Question

sql

Answer 1

按年分开查询也可  若无该标题 填充null  求语句

Answer 2

可以建一张表存下所有的月份，然后用这张表left join 你的张表

Answer 3

DECLARE @i VARCHAR(10) = '测试1';
WITH    TT
          AS ( SELECT   number
               FROM     master..spt_values
               WHERE    type = 'P'
                        AND number BETWEEN 0 AND 11
             )
    SELECT  CONVERT(VARCHAR(7), A.dd, 120) AS dd ,
            A.数量 ,
            A.标题 ,
            CONVERT(VARCHAR(7), B.dd, 120) dd ,
            B.数量 ,
            B.标题
    FROM    ( SELECT    DATEADD(mm, TT.number, '2013-01-01') AS dd ,
                        T.数量 ,
                        T.标题
              FROM      TT
                        LEFT JOIN test T ON CONVERT(VARCHAR(7), DATEADD(mm,
                                                              TT.number,
                                                              '2013-01-01'), 120) = T.月份
              WHERE     T.标题 = @i
                        OR T.标题 IS NULL
            ) A
            INNER JOIN ( SELECT DATEADD(mm, TT.number, '2014-01-01') AS dd ,
                                T1.数量 ,
                                T1.标题
                         FROM   TT
                                LEFT JOIN test T1 ON CONVERT(VARCHAR(7), DATEADD(mm,
                                                              TT.number,
                                                              '2014-01-01'), 120) = T1.月份
                         WHERE  T1.标题 = @i
                                OR T1.标题 IS NULL
                       ) B ON DATEPART(mm, A.dd) = DATEPART(mm, b.dd)

Answer 4

DECLARE @i VARCHAR(10) = '测试5';
WITH    TT
          AS ( SELECT   number
               FROM     master..spt_values
               WHERE    type = 'P'
                        AND number BETWEEN 0 AND 11
             )
    SELECT  CONVERT(VARCHAR(7), A.dd, 120) AS dd ,
            A.数量 ,
            A.标题 ,
            CONVERT(VARCHAR(7), ISNULL(B.dd,DATEADD(yy,1,A.dd)), 120) dd ,  --修正一下这里
            B.数量 ,
            B.标题
    FROM    ( SELECT    DATEADD(mm, TT.number, '2013-01-01') AS dd ,
                        T.数量 ,
                        T.标题
              FROM      TT
                        LEFT JOIN test T ON CONVERT(VARCHAR(7), DATEADD(mm,
                                                              TT.number,
                                                              '2013-01-01'), 120) = T.月份
              WHERE     T.标题 = @i
                        OR T.标题 IS NULL
            ) A
            left JOIN ( SELECT DATEADD(mm, TT.number, '2014-01-01') AS dd ,
                                T1.数量 ,
                                T1.标题
                         FROM   TT
                                LEFT JOIN test T1 ON CONVERT(VARCHAR(7), DATEADD(mm,
                                                              TT.number,
                                                              '2014-01-01'), 120) = T1.月份
                         WHERE  T1.标题 = @i
                                OR T1.标题 IS NULL
                       ) B ON DATEPART(mm, A.dd) = DATEPART(mm, b.dd)
ORDER BY A.dd