求助，为什么出现错误！急急急！！！

[xuyang198711]

自己写的，有点乱，请原谅。老师提示有语法错误，怎么回事呢？
select s.ht_sid,l.pi_sid,l.pi_id,l.pi_name,l.pi_unit,l.FinQty,ef.elseDirect,ef.indirect,ef.tax,ef.elseCost,mn.mat as matup,mat=l.FinQty*mn.mat,
mn.mac as macup,mac=l.FinQty*mn.mac,mn.hum as humup,hum=l.FinQty*mn.hum,ef.elseDirect + ef.indirect + ef.tax + ef.elseCost + l.FinQty*mn.mat + l.FinQty*mn.mac + l.FinQty*mn.hum as zfy,(ef.elseDirect + ef.indirect + ef.tax + ef.elseCost + l.FinQty*mn.mat + l.FinQty*mn.mac + l.FinQty*mn.hum) / l.FinQty as price
from B11CA_MonFinishPi s join B11CA_MonFinishPiDl l on l.msid = s.sid
join (select ht_sid,pi_sid,sid,p_sid,iyear,imonth,
sum(case type when '材料' then up else 0 end) as 'mat',
sum(case type when '机械' then up else 0 end) as 'mac',
sum(case type when '人工' then up else 0 end) as 'hum'
from (select e.ht_sid,a.pi_sid,a.pi_id,a.pi_name,a.pi_unit,a.FinQty,e.iyear,dg.type,e.imonth,e.Type as type1,
dg.rec_sid,dg.num,f.rec_unitprice,f.p_sid,dg.sid
from B11CA_MonFinishPiDl a join B11CA_MonFinishPi e on a.msid = e.sid
join
(select b.version,c.pi_sid,d.rec_sid,d.type,d.rec_name,d.num ,b.sid
from B11CA_fixed b join B11CA_fixed1 c on b.sid = c.m_sid
join
view_B11CA_fixed11 d on c.sid = d.m_sid) dg on a.pi_sid = dg.pi_sid
join
view_rec f on f.type = dg.type and f.rec_sid = dg.rec_sid
) m
group by ht_sid,pi_sid,sid,p_sid,iyear,imonth
where ht_sid = 37 and iyear = '2010' and imonth = '6' and type1 = '自营' and sid = 1 and p_sid = 1
) mn
on s.ht_sid=mn.ht_sid and l.pi_sid=mn.pi_sid
join
(select x.ht_sid,y.pi_sid,y.elseDirect,y.indirect,y.tax,y.elseCost,x.sid as QT_sid
from B11CA_elseCost x join B11CA_elseCostDl y on x.sid = y.m_sid where x.sid = 1) ef
on l.pi_sid = ef.pi_sid and s.ht_sid=ef.ht_sid


消息 156，级别 15，状态 1，第 20 行
关键字 'where' 附近有语法错误。
消息 102，级别 15，状态 1，第 25 行
'ef' 附近有语法错误。

[thinclient]

看红色地方,明显有错了:
select s.ht_sid,l.pi_sid,l.pi_id,l.pi_name,l.pi_unit,l.FinQty,ef.elseDirect,ef.indirect,ef.tax,ef.elseCost,mn.mat as matup,mat=l.FinQty*mn.mat,
mn.mac as macup,mac=l.FinQty*mn.mac,mn.hum as humup,hum=l.FinQty*mn.hum,ef.elseDirect + ef.indirect + ef.tax + ef.elseCost + l.FinQty*mn.mat + l.FinQty*mn.mac + l.FinQty*mn.hum as zfy,(ef.elseDirect + ef.indirect + ef.tax + ef.elseCost + l.FinQty*mn.mat + l.FinQty*mn.mac + l.FinQty*mn.hum) / l.FinQty as price
from B11CA_MonFinishPi s join B11CA_MonFinishPiDl l on l.msid = s.sid
join (select ht_sid,pi_sid,sid,p_sid,iyear,imonth,
sum(case type when '材料' then up else 0 end) as 'mat',
sum(case type when '机械' then up else 0 end) as 'mac',
sum(case type when '人工' then up else 0 end) as 'hum'

[xuyang198711]

[Quote=引用 1 楼 thinclient 的回复:]
看红色地方,明显有错了:
select s.ht_sid,l.pi_sid,l.pi_id,l.pi_name,l.pi_unit,l.FinQty,ef.elseDirect,ef.indirect,ef.tax,ef.elseCost,mn.mat as matup,mat=l.FinQty*mn.mat,
mn.mac as macup,mac=l.FinQty*mn.mac,mn.hu……
[/Quote]

红色地方不错啊...

[htl258_Tony]

SELECT s.ht_sid, l.pi_sid, l.pi_id, l.pi_name, l.pi_unit, l.FinQty, ef.elseDirect, ef.indirect, ef.tax, 
       ef.elseCost, mn.mat AS matup, mat = l.FinQty*mn.mat, mn.mac AS macup, mac = l.FinQty*mn.mac, 
       mn.hum AS humup, hum = l.FinQty*mn.hum, ef.elseDirect+ef.indirect+ef.tax+ef.elseCost+l.FinQty
      *mn.mat+l.FinQty*mn.mac+l.FinQty*mn.hum AS zfy, (
           ef.elseDirect+ef.indirect+ef.tax+ef.elseCost+l.FinQty*mn.mat+l.FinQty*mn.mac+l.FinQty*mn.hum
       )/l.FinQty AS price 
FROM B11CA_MonFinishPi s
	JOIN B11CA_MonFinishPiDl l
		ON  l.msid = s.sid
	JOIN (
	         SELECT ht_sid, pi_sid, SID, p_sid, iyear, imonth, SUM(CASE TYPE WHEN '材料' THEN up ELSE 0 END) AS 
	                'mat', SUM(CASE TYPE WHEN '机械' THEN up ELSE 0 END) AS 'mac', SUM(CASE TYPE WHEN '人工' THEN up ELSE 0 END) AS 
	                'hum' 
	         FROM (
	                  SELECT e.ht_sid, a.pi_sid, a.pi_id, a.pi_name, a.pi_unit, a.FinQty, e.iyear, 
	                         dg.type, e.imonth, e.Type AS type1, dg.rec_sid, dg.num, f.rec_unitprice, 
	                         f.p_sid, dg.sid 
	                  FROM B11CA_MonFinishPiDl a
	                  	JOIN B11CA_MonFinishPi e
	                  		ON  a.msid = e.sid
	                  	JOIN (
	                  	         SELECT b.version, c.pi_sid, d.rec_sid, d.type, d.rec_name, d.num, b.sid 
	                  	         FROM B11CA_fixed b
	                  	         	JOIN B11CA_fixed1 c
	                  	         		ON  b.sid = c.m_sid
	                  	         	JOIN view_B11CA_fixed11 d
	                  	         		ON  c.sid = d.m_sid
	                  	     ) dg
	                  		ON  a.pi_sid = dg.pi_sid
	                  	JOIN view_rec f
	                  		ON  f.type = dg.type
	                  		    AND f.rec_sid = dg.rec_sid
	              ) m
	         WHERE ht_sid = 37
	         	AND iyear = '2010'
	         	AND imonth = '6'
	         	AND type1 = '自营'
	         	AND SID = 1
	         	AND p_sid = 1
	         GROUP BY ht_sid, pi_sid, SID, p_sid, iyear, imonth
	     ) mn
		ON  s.ht_sid = mn.ht_sid
		    AND l.pi_sid = mn.pi_sid
	JOIN (
	         SELECT x.ht_sid, y.pi_sid, y.elseDirect, y.indirect, y.tax, y.elseCost, x.sid AS QT_sid 
	         FROM B11CA_elseCost x
	         	JOIN B11CA_elseCostDl y
	         		ON  x.sid = y.m_sid
	         WHERE x.sid = 1
	     ) ef
		ON  l.pi_sid = ef.pi_sid
		    AND s.ht_sid = ef.ht_sid

try

[sgao1981]

不懂！

[ask_chang]

SELECT s.ht_sid, l.pi_sid, l.pi_id, l.pi_name, l.pi_unit, l.FinQty, ef.elseDirect, ef.indirect, ef.tax,
ef.elseCost, mn.mat AS matup, mat = l.FinQty*mn.mat, mn.mac AS macup, mac = l.FinQty*mn.mac,
mn.hum AS humup, hum = l.FinQty*mn.hum, ef.elseDirect+ef.indirect+ef.tax+ef.elseCost+l.FinQty
*mn.mat+l.FinQty*mn.mac+l.FinQty*mn.hum AS zfy, (
ef.elseDirect+ef.indirect+ef.tax+ef.elseCost+l.FinQty*mn.mat+l.FinQty*mn.mac+l.FinQty*mn.hum
)/l.FinQty AS price
FROM B11CA_MonFinishPi s
JOIN B11CA_MonFinishPiDl l
ON l.msid = s.sid
JOIN (
SELECT ht_sid, pi_sid, SID, p_sid, iyear, imonth, SUM(CASE TYPE WHEN '材料' THEN up ELSE 0 END) AS
'mat', SUM(CASE TYPE WHEN '机械' THEN up ELSE 0 END) AS 'mac', SUM(CASE TYPE WHEN '人工' THEN up ELSE 0 END) AS
'hum'
FROM (
SELECT e.ht_sid, a.pi_sid, a.pi_id, a.pi_name, a.pi_unit, a.FinQty, e.iyear,
dg.type, e.imonth, e.Type AS type1, dg.rec_sid, dg.num, f.rec_unitprice,
f.p_sid, dg.sid
FROM B11CA_MonFinishPiDl a
JOIN B11CA_MonFinishPi e
ON a.msid = e.sid
JOIN (
SELECT b.version, c.pi_sid, d.rec_sid, d.type, d.rec_name, d.num, b.sid
FROM B11CA_fixed b
JOIN B11CA_fixed1 c
ON b.sid = c.m_sid
JOIN view_B11CA_fixed11 d
ON c.sid = d.m_sid
) dg
ON a.pi_sid = dg.pi_sid
JOIN view_rec f
ON f.type = dg.type
AND f.rec_sid = dg.rec_sid
) m
WHERE ht_sid = 37
AND iyear = '2010'
AND imonth = '6'
AND type1 = '自营'
AND SID = 1
AND p_sid = 1
GROUP BY ht_sid, pi_sid, SID, p_sid, iyear, imonth
) mn
ON s.ht_sid = mn.ht_sid
AND l.pi_sid = mn.pi_sid
JOIN (
SELECT x.ht_sid, y.pi_sid, y.elseDirect, y.indirect, y.tax, y.elseCost, x.sid AS QT_sid
FROM B11CA_elseCost x
JOIN B11CA_elseCostDl y
ON x.sid = y.m_sid
WHERE x.sid = 1
) ef
ON l.pi_sid = ef.pi_sid
AND s.ht_sid = ef.ht_sid

支持3#

[oyctzl]

where 前面的那段
group by ht_sid,pi_sid,sid,p_sid,iyear,imonth
去掉试试