以下语句,查询时报错,请教应如何写?

lifndcw 2015-08-06 09:22:41
SELECT pcm.班组名称,
sum(pcm.工时) as 总工时,round(sum(pcm.工时)/24,2) as 平均天数,sum(pcs.排产只数*pcs.方数) as 车间电镀面积,
(case when pcs.收货类型='返工' then sum(pcs.排产只数*pcs.方数) else 0 end) as 客户返工面积,
sum(pcs.直镀单价*pcs.排产只数) as 车间直镀产值,
(case when pcs.收货类型='返工' then sum(pcs.排产只数*pcs.单价) else 0 end) as 客户返工产值,
(case when pcs.工艺要求='返工1' then sum(pcs.排产只数*pcs.单价) else 0 end) as 抛光面积,
sum(pcs.抛光单价*pcs.排产只数) as 抛光产值,
(case when pcs.工艺要求='抛镀' then sum(pcs.排产只数*pcs.单价) else 0 end) as 抛镀产值,
(case when pcs.工艺要求='黑抛' then sum(pcs.排产只数*pcs.单价) else 0 end) as 黑抛产值
FROM pcm INNER JOIN pcs ON pcm.id = pcs.idd group by pcm.班组名称
...全文
86 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
道玄希言 2015-08-07
应该是你 group by 后的分组字段不完全. 没看到测试数据, 无法帮你测试.
回复
lifndcw 2015-08-07
CREATE TABLE [dbo].[pcm] ( [id] [bigint] IDENTITY (1, 1) NOT NULL , [排产单号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , [排产日期] [datetime] NULL , [录入人] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , [录入日期] [datetime] NULL , [审核人] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [审核日期] [datetime] NULL , [备注] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [审核] [bit] NULL , [班组名称] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , [班组编码] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [工时] [float] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[pcs] ( [id] [bigint] IDENTITY (1, 1) NOT NULL , [idd] [bigint] NULL , [镀色] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [客户编码] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [客户名称] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [来货日期] [datetime] NULL , [品名编码] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , [品名] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL , [规格] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [工艺要求] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [件数] [bigint] NULL , [急件] [bit] NULL , [是否返电] [bit] NULL , [备注] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [lh_id] [bigint] NULL , [流水号] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL , [结单] [bit] NULL , [班组名称] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [排产只数] [float] NULL , [排产重量] [float] NULL , [优先级] [int] NULL , [是否含税] [bit] NULL , [已排产] [float] NULL , [来货数] [float] NULL , [单价] [money] NULL , [抛光单价] [money] NULL , [选用] [bit] NULL , [直镀单价] [money] NULL , [方数] [float] NULL , [收货类型] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] GO
回复
许晨旭 2015-08-06
应该是你的连接方式有问题:INNER JOIN pcs ON pcm.id = pcs.idd 把所有数据用SQL语句贴出来
回复
lifndcw 2015-08-06


以上语句统计出结果如下
1号线1班 60
实际应是:
1号线1班 20
帮手再看下
回复
许晨旭 2015-08-06
SELECT 
	pcm.班组名称,
	sum(pcm.工时) as 总工时,
	round(sum(pcm.工时)/24,2) as 平均天数,
	sum(pcs.排产只数*pcs.方数) as  车间电镀面积,
	sum(case 
		 when pcs.收货类型='返工' then (pcs.排产只数*pcs.方数) 
		 else 0
	 end) as 客户返工面积,
	 sum(pcs.直镀单价*pcs.排产只数) as 车间直镀产值,
	 sum(case 
		 when pcs.收货类型='返工' then (pcs.排产只数*pcs.单价) 
		 else 0
	 end) as 客户返工产值,
	sum(case 
		when pcs.工艺要求='返工1' then (pcs.排产只数*pcs.单价) 
		else 0 
	end) as 抛光面积,
	sum(pcs.抛光单价*pcs.排产只数) as  抛光产值,
	sum(case 
		when pcs.工艺要求='抛镀' then (pcs.排产只数*pcs.单价) 
		else 0 
	end) as 抛镀产值,
	sum(case 
		when pcs.工艺要求='黑抛' then (pcs.排产只数*pcs.单价) 
		else 0 
	end) as 黑抛产值
FROM pcm 
INNER JOIN  pcs ON pcm.id = pcs.idd 
group by pcm.班组名称
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-08-06 09:22
社区公告
暂无公告