MSSQL中按时间字段分组查询，并在查询结果中显示所有分组数据列

Question

按时间查询，如何整合为一条查询语句？并在查询结果中分列显示

    --查询上月入库数量
SELECT
    t3.FNUMBER AS '物料编码',
    t1.FDATE AS '入库时间',
    SUM( t2.FREALQTY ) AS '上月累计入库数量' 
FROM
    T_PRD_INSTOCK t1
    LEFT JOIN T_PRD_INSTOCKENTRY t2 ON t1.FID = t2.FID
    LEFT JOIN T_BD_MATERIAL t3 ON t2.FMATERIALID = t3.FMATERIALID 
WHERE
    DATEDIFF(month,t1.FDATE,GETDATE())=1
GROUP BY
    t1.FDATE,
    t3.FNUMBER 
ORDER BY
    t1.FDATE DESC


    --查询本月入库数量
SELECT
    t3.FNUMBER AS '物料编码',
    t1.FDATE AS '入库时间',
    sum( t2.FREALQTY ) AS '本月累计入库数量' 
FROM
    T_PRD_INSTOCK t1
    LEFT JOIN T_PRD_INSTOCKENTRY t2 ON t1.FID = t2.FID
    LEFT JOIN T_BD_MATERIAL t3 ON t2.FMATERIALID = t3.FMATERIALID 
WHERE
    DATEDIFF(month,t1.FDATE,GETDATE())=0           
GROUP BY
    t1.FDATE,
    t3.FNUMBER 
ORDER BY
    t1.FDATE DESC

Answer 1

应该是这么写， 没有数据，还没测试。

select A.物料编码 , A.入库时间 as 上月入库时间 , A.上月累计入库数量 ,
B.入库时间 as 本月入库时间 , B.本月累计入库数量
FROM (
SELECT
    t3.FNUMBER AS '物料编码',
    t1.FDATE AS '入库时间',
    SUM( t2.FREALQTY ) AS '上月累计入库数量' 
FROM
    T_PRD_INSTOCK t1
    LEFT JOIN T_PRD_INSTOCKENTRY t2 ON t1.FID = t2.FID
    LEFT JOIN T_BD_MATERIAL t3 ON t2.FMATERIALID = t3.FMATERIALID 
WHERE
    DATEDIFF(month,t1.FDATE,GETDATE())=1
GROUP BY
    t1.FDATE,
    t3.FNUMBER 
) A LEFT JOIN (

SELECT
    t3.FNUMBER AS '物料编码',
    t1.FDATE AS '入库时间',
    sum( t2.FREALQTY ) AS '本月累计入库数量' 
FROM
    T_PRD_INSTOCK t1
    LEFT JOIN T_PRD_INSTOCKENTRY t2 ON t1.FID = t2.FID
    LEFT JOIN T_BD_MATERIAL t3 ON t2.FMATERIALID = t3.FMATERIALID 
WHERE
    DATEDIFF(month,t1.FDATE,GETDATE())=0           
GROUP BY
    t1.FDATE,
    t3.FNUMBER 
) B ON A.物料编码 = B.物料编码