遇到个查询统计的sql，已经不会写了...help

钛元素 2014-11-27 10:45:13
有几张表，需要做统计分析，如下：







现在要统计如下内容：
统计表:统计某个月内的完成情况，必须是完成的批次

批次 日期 原材料 领用量 入库数 消耗差 成本对比

批次，日期，就是生产情况信息表:A1中的内容；日期就是领用日期
原材料是几种原材料的明细列表，每种原材料独立一行，显示领用量
入库数是该批次在该日期的入库数量
消耗差是(实际消耗 - 标准消耗)
而实际消耗=该批次该物料领用量，标准消耗=经济模型中的该物料的每吨消耗量*该批次产品总入库数
成本对比=消耗差*经济模型中的该物料的标准单价

救命！我写不来了！大大！T_T

...全文
511 26 打赏 收藏 转发到动态 举报
写回复
用AI写文章
26 条回复
切换为时间正序
请发表友善的回复…
发表回复
还在加载中灬 2014-11-28
  • 打赏
  • 举报
 回复
PARTITION这个不是行转列的，是开窗函数的东西，你可以直接百度了解，博客园或CSDN博客的文章都不错。 行转列的是PIVOT 列转行是UNPIVOT，有兴趣都可以百度了解下 然后，那个AS语法错误，是我忘了加;WITH 
;WITH A1Cte AS(
    SELECT
        id
        ,pici
        ,lydate
        ,jjmxid
        ,SUM(cprks)OVER(PARTITION BY pici)cprks
        ,MAX(ynend)OVER(PARTITION BY pici)ynend
    FROM A1
    --月份条件加在此处
)
SELECT
    T.pici
    ,T.lydate[日期]
    ,C1.yclname[原材料]
    ,T.lynum[领用量]
    ,T.cprks[入库数]
    ,T.lynum[实际消耗]
    ,T.cprks*B2.XHB[标准消耗]
    ,ABS(T.lynum-T.cprks*B2.XHB)[消耗差]
    ,ABS(T.lynum*B2.price-T.cprks*B2.XHB*B2.price)[成本对比]
FROM
    (SELECT T1.pici
        ,MAX(T1.lydate)lydate
        ,T1.jjmxid
        ,SUM(A2.lynum)[lynum]
        ,T1.cprks
        ,A2.yclid
    FROM A1Cte T1
        JOIN A2 ON T1.id=A2.aid
    WHERE T1.ynend='Y'
    GROUP BY T1.pici
        ,T1.jjmxid
        ,T1.cprks
        ,A2.yclid
    )T
    LEFT JOIN B2 ON T.jjmxid=B2.bid AND T.yclid=B2.yclid
    LEFT JOIN C1 ON T.yclid=C1.id
你直接放下面也可以
钛元素 2014-11-28
  • 打赏
  • 举报
 回复
引用 24 楼 ky_min 的回复:
A1Cte AS(
	SELECT
		id
		,pici
		,lydate
		,jjmxid
		,SUM(cprks)OVER(PARTITION BY pici)cprks
		,MAX(ynend)OVER(PARTITION BY pici)ynend
	FROM A1
	--月份条件加在此处
)
试试这个
老大，真厉害，就是这样子的了。 不过第一个 as 那里提示语法错误，我也不明，就不用a1Cte ，直接放在下面的代码里面了，执行成功了 sql2005那个语法糖就是 PARTITION 吧，这个专门做行列转换用的吧，挺好用的 学习！给力！
还在加载中灬 2014-11-27
  • 打赏
  • 举报
 回复
不确定信息太多，你试下，有些东西我不知道你是怎么连接的，你要对上号
SELECT
	T.批次
	,T.领用日期[日期]
	,C1.原材料名称[原材料]
	,SUM(T.领用数量)[领用量]
	,SUM(T.产品入库数)[入库数]
	,SUM(T.领用数量)-SUM(T.产品入库数*B2.每顿消耗量)[消耗差]
	,SUM(T.领用数量*B2.每顿价格)-SUM(T.产品入库数*B2.每顿消耗量*B2.每顿价格)[成本对比]
FROM
	(SELECT A1.批次,A1.领用日期,A1.经济模型ID
		,SUM(A2.领用数量)[领用数量]
		,A1.产品入库数
		,A2.ID
	FROM A1
		LEFT JOIN A2 ON A1.ID=A2.生产情况信息ID
	GROUP BY A1.批次,A1.领用日期,A1.经济模型ID
		,A1.产品入库数
		,A2.ID
	)T
	LEFT JOIN B2 ON T.经济模型ID=B2.经济模型ID AND T.ID=B2.ID
	LEFT JOIN C1 ON T.ID=C1.ID
GROUP BY
	T.批次
	,T.领用日期
	,C1.原材料名称
程序猿老曾 2014-11-27
  • 打赏
  • 举报
 回复
你的表可以整好看点吗~~~~你所勾勒出来的表貌似有问题啊~~~ 你那个A1生产信息表中的仅有批次、领用日期？？ 还有你把B1和B2作成一个视图，把A1和A2作成一个视图，两两视图再关联可能会清晰点吧！！ 以下是纯手打且未经测试的代码~~~~有点不太负责任的说。 仅供参考 

--统计某月份的完成情况
select count(*) from 子表A1
where 是否完成=N'Y' and Month(日期)='某月'

--第二个问题
select c.批次,c.领用日期,c.领用数量,c.产品入库数,(c.领用数量 -  (d.每吨消耗量 * c.产品入库数)) as Consume,
((c.领用数量 -  (d.每吨消耗量 * c.产品入库数))*d.每吨价格) as Cost 
from
(select a.经济模型ID,a.批次,a.领用日期,b.原材料名称,b.领用数量,b.产品入库数 from A1 as a
left join A2 as b
on a.原材料名称=b.原材料名称) as c
left join B1 as d
on c.经济模型ID=d.ID
group by c.批次,c.领用日期,c.领用数量,c.产品入库数
钛元素 2014-11-27
  • 打赏
  • 举报
 回复


如图，a1表中存放的是a2表的id
b1表中同理，也是存放b2表的id

叫a2id如何，就是个意思
钛元素 2014-11-27
  • 打赏
  • 举报
 回复
引用 6 楼 ky_min 的回复:
1、A1的子表，该叫A2吧 2、B2里边和B1的关联字段叫什么呢，同理A1的子表 3、B2里面的ID是材料的ID，还是自己的表ID呢
是我画的问题，我错了，总共有5张表， a1 a1的子表a2 b1 b1的子表b2 c1
还在加载中灬 2014-11-27
  • 打赏
  • 举报
 回复
1、A1的子表，该叫A2吧 2、B2里边和B1的关联字段叫什么呢，同理A1的子表 3、B2里面的ID是材料的ID，还是自己的表ID呢
钛元素 2014-11-27
  • 打赏
  • 举报
 回复
那两张子表，也是独立的数据表，只是跟它的主表有关联
钛元素 2014-11-27
  • 打赏
  • 举报
 回复
引用 2 楼 fredrickhu 的回复:
3表联查 其实就是3个表JOIN啊。涉及到一个行列转换的问题。你的原材料这些是固定的吗？如果是固定的，就直接写，不固定的话 需要用动态SQL
不是固定的，有的生产批次产品需要3种，有的4种，这样 3表join一个二层统计，再行转列？？
钛元素 2014-11-27
  • 打赏
  • 举报
 回复
引用 1 楼 ky_min 的回复:
你数据里面的子表是字符，还是真的有个子表
真的是一张表，关联的
--小F-- 2014-11-27
  • 打赏
  • 举报
 回复
3表联查 其实就是3个表JOIN啊。涉及到一个行列转换的问题。你的原材料这些是固定的吗？如果是固定的，就直接写，不固定的话 需要用动态SQL
还在加载中灬 2014-11-27
  • 打赏
  • 举报
 回复
你数据里面的子表是字符，还是真的有个子表
还在加载中灬 2014-11-27
  • 打赏
  • 举报
 回复 
A1Cte AS(
	SELECT
		id
		,pici
		,lydate
		,jjmxid
		,SUM(cprks)OVER(PARTITION BY pici)cprks
		,MAX(ynend)OVER(PARTITION BY pici)ynend
	FROM A1
	--月份条件加在此处
)
SELECT
	T.pici
	,T.lydate[日期]
	,C1.yclname[原材料]
	,T.lynum[领用量]
	,T.cprks[入库数]
	,T.lynum[实际消耗]
	,T.cprks*B2.XHB[标准消耗]
	,ABS(T.lynum-T.cprks*B2.XHB)[消耗差]
	,ABS(T.lynum*B2.price-T.cprks*B2.XHB*B2.price)[成本对比]
FROM
	(SELECT T1.pici
		,MAX(T1.lydate)lydate
		,T1.jjmxid
		,SUM(A2.lynum)[lynum]
		,T1.cprks
		,A2.yclid
	FROM A1Cte T1
		JOIN A2 ON T1.id=A2.aid
	WHERE T1.ynend='Y'
	GROUP BY T1.pici
		,T1.jjmxid
		,T1.cprks
		,A2.yclid
	)T
	LEFT JOIN B2 ON T.jjmxid=B2.bid AND T.yclid=B2.yclid
	LEFT JOIN C1 ON T.yclid=C1.id
试试这个
钛元素 2014-11-27
  • 打赏
  • 举报
 回复
引用 21 楼 ky_min 的回复:
SELECT
	T.pici
	,MAX(T.lydate)[日期]
	...
	,C1.yclname
应该是这样，你试试
嗯，我试了下，发现入库数那里不对 入库数跟物料无关，跟批次有关，一个批次可以有多次入库的，而所有物料都跟该批次的入库总数进行计算的 我觉得这里不知道怎么写了 大哥你写的好溜
还在加载中灬 2014-11-27
  • 打赏
  • 举报
 回复
时间条件加在20后面 然后，有些问题不知道你考虑过没有，就是同年批次有没有可能出现，出现Y后再出现N 另外，按时间查询时的一些细节
还在加载中灬 2014-11-27
  • 打赏
  • 举报
 回复 
SELECT
	T.pici
	,MAX(T.lydate)[日期]
	,C1.yclname[原材料]
	,SUM(T.lynum)[领用量]
	,SUM(T.cprks)[入库数]
	,SUM(T.lynum)[实际消耗]
	,SUM(T.cprks*B2.XHB)[标准消耗]
	,ABS(SUM(T.lynum)-SUM(T.cprks*B2.XHB))[消耗差]
	,ABS(SUM(T.lynum*B2.price)-SUM(T.cprks*B2.XHB*B2.price))[成本对比]
FROM
	(SELECT A1.pici
		,A1.lydate
		,A1.jjmxid
		,SUM(A2.lynum)[lynum]
		,A1.cprks
		,A2.yclid
		,MAX(A1.ynend)OVER(PARTITION BY A1.pici)ynend
	FROM A1
		JOIN A2 ON A1.id=A2.aid
	GROUP BY A1.pici
		,A1.lydate
		,A1.jjmxid
		,A1.cprks
		,A2.yclid
		,A1.ynend
	)T
	LEFT JOIN B2 ON T.jjmxid=B2.bid AND T.yclid=B2.yclid
	LEFT JOIN C1 ON T.yclid=C1.id
WHERE T.ynend='Y'
GROUP BY
	T.pici
	,C1.yclname
应该是这样，你试试
钛元素 2014-11-27
  • 打赏
  • 举报
 回复
批次pc002使用的经济模型是XH2，如果pc002 也完成了的话，就是有一个Y标记的话，那么，数值统计应该是：

钛元素 2014-11-27
  • 打赏
  • 举报
 回复
我重新整理下，是这样的：
我要统计如下内容：
已完成的批次的数据统计（pc001完成，pc002未完成）




而表结构是这样的：



还在加载中灬 2014-11-27
  • 打赏
  • 举报
 回复
呃~~ 另外，你还是根据你的数据，贴出你整体想要的结果吧，这样下去有点乱
引用 13 楼 tgh1981 的回复:
啊 ，统计结果有误，sorry ，每种物料都是应该要有消耗差的，比如pc001物料中的木粉，应该就是： 1-(2.3*(3.2+2.4))，就是木粉领用数-(木粉标准消耗量*该产品对应的入库总数)
钛元素 2014-11-27
  • 打赏
  • 举报
 回复
引用 16 楼 ky_min 的回复:
我觉得有问题，主从表，应该是从表存主表的主键啊 你这样的结构，你是怎么关联两张表的
实际上就是从表保存主表ID的，我又写错了... a1中没有a2id，a2中有a1_id这样的字段 b1中也没有b2id，b2中是b1_id字段
加载更多回复（6）
JAVA上百实例源码以及开源项目
5个目标文件，演示Address EJB的实现，创建一个EJB测试客户端，得到名字上下文，查询jndi名，通过强制转型得到Home接口，getInitialContext()函数返回一个经过初始化的上下文，用client的getHome()函数调用Home接口...
JAVA上百实例源码以及开源项目源代码
数字证书：从文件中读取数字证书，生成文件输入流,输入文件为c:/mycert.cer，获取一个处理X.509证书的证书工厂…… Java+ajax写的登录实例 1个目标文件 内容索引:Java源码,初学实例,ajax,登录 一个Java+ajax写的...
SQl Server数据库
讲述Sql Server数据库的企业级开发常用的关键知识点。尽量把概念讲的通俗易懂，了解其架构原理，不仅仅会操作应用，还要把握其本质。帮大家更深层次的理解数据库。 【课程目录】 一.Sql Server数据库架构 1.架构 2....
SQLServer的命令行工具-sqlcmd等..
来看看sqlcmd的启动参数们：   C:Documents and Settingschenxie.IFLYTEK>sqlcmd.exe /? Microsoft (R) SQL Server 命令行工具 版本 9.00.1399.06 NT INTEL X86 版权所有 (c) Microsoft ...
mysql.help_topic：join后面on居然还可以用 ＜ 连接实现列转行
mysql.help_topic：join后面on居然还可以用 连接实现列转行
MS-SQL Server

34,593

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章