qq_40673781
一曲悲画秋
采纳率50%
2020-04-16 10:27 阅读 195

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

20

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

    --查询上月入库数量
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
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    seakingx 陈年椰子 2020-04-16 10:33

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

    
    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.物料编码
    
    
    点赞 1 评论 复制链接分享

相关推荐