set @sql = @sql + ' from A LEFT JOIN B ON A.Unti=B.Unti AND A.RecordID=B.RecordID WHERE 条件为生产线1 group by A.GkID,A.OrderNo,A.Customer,A.Project,A.Unti' 如上位置 如果B的MaterialCode也和生产线有关 from (select distinct MaterialCode from B WHERE 条件为生产线1) as a 这个地方也可以加
能不能转换下 把这个条件转换下 加个GROUP BY 后面换成 HAVING 生产线为1
set @sql = @sql + ' from A LEFT JOIN B ON A.Unti=B.Unti AND A.RecordID=B.RecordID group by A.GkID,A.OrderNo,A.Customer,A.Project,A.Unti' 换成 set @sql = @sql + ' from (select * from a where a.unit=1)as a LEFT JOIN B ON A.Unti=B.Unti AND A.RecordID=B.RecordID group by A.GkID,A.OrderNo,A.Customer,A.Project,A.Unti' 试试。
set @sql = @sql + ' from (select * from A WHERE UNTI='1#') A LEFT JOIN PR_Batch B ON A.Unti=B.Unti AND A.RecordID=B.RecordID ' 报错是'1' 附近有语法错误。 set @sql = @sql + ' from (select * from A WHERE UNTI=1#) A LEFT JOIN PR_Batch B ON A.Unti=B.Unti AND A.RecordID=B.RecordID ' '#' 附近有语法错误。
set @sql = @sql + ' from (select * from A WHERE UNTI=‘’1#‘’) A LEFT JOIN PR_Batch B ON A.Unti=B.Unti AND A.RecordID=B.RecordID '要两个单撇号
一开始我想到的办法是select @sql = @sql + ' ,SUM(case MaterialCode when ''' + MaterialCode + ''' then ActualQuality else 0 end) [' + MaterialCode + ']' from (select distinct MaterialCode from B) as a 改成 select @sql = @sql + ' ,SUM(case MaterialCode when ''' + MaterialCode + ''' then ActualQuality else 0 end) [' + MaterialCode + ']' from (select distinct MaterialCode from B right join A on A.Unti=B.Unti AND A.RecordID=B.RecordID ) as a SQL提示命令已成功执行,但是不会返回任何结果,这是什么原因额
set @sql = @sql + ' from (select * from A WHERE UNTI='1#') A LEFT JOIN PR_Batch B ON A.Unti=B.Unti AND A.RecordID=B.RecordID ' 报错是'1' 附近有语法错误。 set @sql = @sql + ' from (select * from A WHERE UNTI=1#) A LEFT JOIN PR_Batch B ON A.Unti=B.Unti AND A.RecordID=B.RecordID ' '#' 附近有语法错误。 谢谢,这下解决了
如上位置
如果B的MaterialCode也和生产线有关
from (select distinct MaterialCode from B WHERE 条件为生产线1) as a
这个地方也可以加
HAVING 生产线为1
set @sql = @sql + ' from A LEFT JOIN B ON A.Unti=B.Unti AND A.RecordID=B.RecordID group by A.GkID,A.OrderNo,A.Customer,A.Project,A.Unti'
换成
set @sql = @sql + ' from (select * from a where a.unit=1)as a LEFT JOIN B ON A.Unti=B.Unti AND A.RecordID=B.RecordID group by A.GkID,A.OrderNo,A.Customer,A.Project,A.Unti'
试试。
GkID RecordID Unti OrderNo Customer Project
B表(记录材料消耗)
RecordID Unti MaterialCode ActualQuality
两表通过RecordID 和Unti相关联
报错啥?
你单独运行下
select * from a where unit='1#'
看看这个报错不
GkID 生产机上的ID
RecordID 任务单ID
Unti 生产线
OrderNo 合同编号
Customer 工程
Project 项目
B表(记录材料消耗)
RecordID 任务单ID
Unti 生产线
MaterialCode 材料代码
ActualQuality 实际用量
两表通过RecordID 和Unti相关联
我把这个写在存储过程中,要求可以根据Unti 生产线,OrderNo 合同编号,Customer 工程 组合起来查询,3个都是字符串类型,2张表除了实际用量是float,其余字段全是nvarchar
单独运行不报错额,包括行列转换运行都不报错,但是不管在哪加上限制条件要么不起作用,要么就报语法错误
报错语句贴出来看看。
报错是'1' 附近有语法错误。
set @sql = @sql + ' from (select * from A WHERE UNTI=1#) A LEFT JOIN PR_Batch B ON A.Unti=B.Unti AND A.RecordID=B.RecordID '
'#' 附近有语法错误。
from (select distinct MaterialCode from B) as a
改成
select @sql = @sql + ' ,SUM(case MaterialCode when ''' + MaterialCode + ''' then ActualQuality else 0 end) [' + MaterialCode + ']'
from (select distinct MaterialCode from B right join A on A.Unti=B.Unti AND A.RecordID=B.RecordID ) as a
SQL提示命令已成功执行,但是不会返回任何结果,这是什么原因额
报错是'1' 附近有语法错误。
set @sql = @sql + ' from (select * from A WHERE UNTI=1#) A LEFT JOIN PR_Batch B ON A.Unti=B.Unti AND A.RecordID=B.RecordID '
'#' 附近有语法错误。
谢谢,这下解决了