时间戳

首页 > 解决方案 > 如何在 SQL Server 中显示年份中的月份数据，而输出中没有日期

sql-server-2014 - 如何在 SQL Server 中显示年份中的月份数据，而输出中没有日期

问题描述

我正在尝试以明智的方式获取数据，因为我已经编写了这样的查询，但没有得到我想要的结果

SET dateformat dmy 

SELECT * 
FROM   (SELECT 'Apr'                                 AS xData, 
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '04' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'May'                                 AS xData, 
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '05' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Jun'                                 AS xData, 
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '06' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Jul'                                 AS xData, 
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '07' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Aug'                                 AS xData, 
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '08' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Sep'                                 AS xData, 
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '09' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Oct'                                 AS xData, 
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '10' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Nov'                                 AS xData, 
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '11' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Dec'                                 AS xData, 
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '12' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Jan'                                 AS xData, 
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2016'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '01' 
               AND Year(dt) = '2016' 
        UNION 
        SELECT 'Feb'                                 AS xData, 
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2016'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '02' 
               AND Year(dt) = '2016' 
        UNION 
        SELECT 'Mar'                                 AS xData, 
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2016'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '03' 
               AND Year(dt) = '2016') AS tbl 
ORDER  BY year

得到了这个结果
得到这个结果

但我实际上想要这样（年月明智）
实际我想要的

所以我必须为此做些什么让我知道我必须在哪里更改我的查询
任何人都可以提前帮助我..thanx

标签： sql-server-2014

解决方案

SET dateformat dmy 

SELECT 
xData,
asswt,
waxwt,
corewt
'Year'
FROM   (SELECT 'Apr'                                 AS xData, 
                 1                                   AS xNum,
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '04' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'May'                                 AS xData, 
                 2                                   AS xNum,
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '05' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Jun'                                 AS xData, 
                 3                                   AS xNum,
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '06' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Jul'                                 AS xData, 
                 4                                   AS xNum,
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '07' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Aug'                                 AS xData, 
                 5                                   AS xNum,
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '08' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Sep'                                 AS xData, 
                6                                    AS xNum,
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '09' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Oct'                                 AS xData, 
                 7                                   AS xNum,
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '10' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Nov'                                 AS xData, 
                 8                                   AS xNum,
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '11' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Dec'                                 AS xData, 
                9                                    AS xNum,
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2015'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '12' 
               AND Year(dt) = '2015' 
        UNION 
        SELECT 'Jan'                                 AS xData, 
               10                                    AS xNum,
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2016'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '01' 
               AND Year(dt) = '2016' 
        UNION 
        SELECT 'Feb'                                 AS xData, 
               11                                    AS xNum,
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2016'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '02' 
               AND Year(dt) = '2016' 
        UNION 
        SELECT 'Mar'                                 AS xData,
               12                                    AS xNum,       
               Sum(Isnull(( qty * assemblywt ), 0))  AS asswt, 
               Sum(Isnull(( qty * [waxwt/pcs] ), 0)) AS waxwt, 
               Sum(Isnull(( qty * corewt ), 0))      AS corewt, 
               '2016'                                AS 'Year' 
        FROM   tdcwaxweight 
        WHERE  Month(dt) = '03' 
               AND Year(dt) = '2016') AS tbl 
ORDER  BY year and xNum

推荐阅读