以下是我的SQL,现在日期控件有按日和按月,按日期分的现在显示没问题,按年月分的时候该如何在SQL里设置,想要获得的效果就是月份之间的对比,例如选2018年,1月和2月,显示的是1月和2月的数据,即两行 SELECT X.日期 , CASE WHEN X.部门编号='0801'THEN 1 WHEN X.部门编号='0802'THEN 2 WHEN X.部门编号='0809'THEN 3 WHEN X.部门编号='0805'THEN 4 WHEN X.部门编号='0806'THEN 5 WHEN X.部门编号='0813'THEN 6 WHEN X.部门编号='0810'THEN 7 WHEN X.部门编号='0808'THEN 8 WHEN X.部门编号='0814'THEN 9 WHEN X.部门编号='0804'THEN 10 WHEN X.部门编号= '0803'THEN 11 WHEN X.部门编号='21' THEN 12 WHEN X.部门编号='0812'THEN 13 END AS 部门编号1, X.部门编号, X.部门名称, CAST(SUM (X.入库件数*X.支数)AS DECIMAL(16,0))AS 产能 FROM (SELECT CONVERT(VARCHAR(100),A.DOC_DATE ,23)AS 日期, D.ADMIN_UNIT_CODE AS 部门编号, D.ADMIN_UNIT_NAME AS 部门名称, CASE WHEN C.UDF021='' THEN 1 ELSE CAST(C.UDF021 AS DECIMAL(16,0))END AS 支数, CAST(B.ACCEPTED_INVENTORY_QTY AS DECIMAL(16,4))AS 入库件数 FROM MO_RECEIPT A LEFT JOIN MO_RECEIPT_D B ON B.MO_RECEIPT_ID=A.MO_RECEIPT_ID LEFT JOIN ITEM C ON C.ITEM_BUSINESS_ID =B.ITEM_ID LEFT JOIN ADMIN_UNIT D ON D.ADMIN_UNIT_ID =A.Owner_Dept where A.DOC_DATE BETWEEN '${开始日期}'AND '${结束日期}')X GROUP BY X.日期 , X.部门名称 , X.部门编号 ORDER BY 2,1 ASC |
最佳回答 |
||||
0
|
|
|||
0
|
|
|||
0
|
|
|||
0
|
|
|||
0
|
|