SQL语句查询 嵌套 子查询 一年中每天的统计数据按月统计出来 结果月份为字段
SELECT ITEM_CD AS 物料代码,(SELECT t2.item_e_nm
FROM (SELECT item_cd, item_e_nm, spec
FROM bba010t) t2
WHERE (t2.item_cd = a1.item_cd)) AS 物料名称,
(SELECT t2.spec
FROM (SELECT item_cd, item_e_nm, spec
FROM bba010t) t2
WHERE t2.item_cd = a1.item_cd) AS 物料规格,
(SELECT a2.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT)
AS d
FROM MGA100T
where type='1'and org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a2
WHERE a2.d = '01' AND a2.ITEM_CD = a1.ITEM_CD) AS 一月入,
(SELECT a2.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT)
AS d
FROM MGA100T
where type='2'and org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a2
WHERE a2.d = '01' AND a2.ITEM_CD = a1.ITEM_CD) AS 一月出,
(SELECT a2.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT)
AS d
FROM MGA100T
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a2
WHERE a2.d = '02' AND a2.ITEM_CD = a1.ITEM_CD) AS 二月出入,
(SELECT a2.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT)
AS d
FROM MGA100T
where org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a2
WHERE a2.d = '03' AND a2.ITEM_CD = a1.ITEM_CD) AS 三月出入,
(SELECT a2.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT)
AS d
FROM MGA100T
where org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a2
WHERE a2.d = '041' AND a2.ITEM_CD = a1.ITEM_CD) AS 四月出入,
(SELECT a2.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT)
AS d
FROM MGA100T
where org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a2
WHERE a2.d = '05' AND a2.ITEM_CD = a1.ITEM_CD) AS 五月出入,
(SELECT a2.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT)
AS d
FROM MGA100T
where org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a2
WHERE a2.d = '06' AND a2.ITEM_CD = a1.ITEM_CD) AS 六月出入,
(SELECT a2.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT)
AS d
FROM MGA100T
where org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a2
WHERE a2.d = '07' AND a2.ITEM_CD = a1.ITEM_CD) AS 七月出入,
(SELECT a2.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT)
AS d
FROM MGA100T
where org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a2
WHERE a2.d = '08' AND a2.ITEM_CD = a1.ITEM_CD) AS 八月出入,
(SELECT a2.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT)
AS d
FROM MGA100T
where org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a2
WHERE a2.d = '09' AND a2.ITEM_CD = a1.ITEM_CD) AS 九月出入,
(SELECT a2.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT)
AS d
FROM MGA100T
where org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a2
WHERE a2.d = '10' AND a2.ITEM_CD = a1.ITEM_CD) AS 十月出入,
(SELECT a2.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT)
AS d
FROM MGA100T
where org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a2
WHERE a2.d = '11' AND a2.ITEM_CD = a1.ITEM_CD) AS 十一月出入,
(SELECT a2.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT)
AS d
FROM MGA100T
where org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a2
WHERE a2.d = '12' AND a2.ITEM_CD = a1.ITEM_CD) AS 十二月出入,
(SELECT a3.c
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD AS d
FROM MGA100T
where org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY ITEM_CD) a3
WHERE (a3.d = a1.ITEM_CD)) AS 合计
FROM (SELECT SUM(TR_QTY) AS c, ITEM_CD, DATENAME(month, TR_DT) AS d
FROM MGA100T
where org_part='21' and comp_cd='21' and wh_cd='m010'and datename(year,tr_dt)='2012'
GROUP BY DATENAME(month, TR_DT), ITEM_CD) a1
GROUP BY ITEM_CD
--------------------编程问答-------------------- 不知道你的数据库结构，如何来做？ --------------------编程问答-------------------- 这么长的统计语句？ --------------------编程问答--------------------
发表结构出来 看看吧
--------------------编程问答--------------------
根据月份分组不行吗，整这么复杂的。
--------------------编程问答--------------------
这个sql看的像去WC
--------------------编程问答--------------------
什么来的~又长有臭~
--------------------编程问答--------------------
你这个是库存历史吗，还是全年进出历史
补充：.NET技术 , 分析与设计
