"storeinfo.produceno=#temptable.produceno就会出现错误", 是什么错误? 不是语法错误吧?对2个表全部加上 "produceno is not null" 的条件, 看看行不行? 如下:select clothno,colorno,produceno, totalenter= (SELECT SUM(enteramount) FROM storeinfo WHERE produceno is not null AND storeinfo.clothno=#temptable.clothno AND storeinfo.colorno=#temptable.colorno AND storeinfo.produceno=#temptable.produceno AND date BETWEEN date1 AND date2) FROM #temptable WHERE produceno is not null GROUP BY clothno,colorno,produceno 另外, date、date1、date2都是storeinfo的字段吗?
select clothno,colorno,produceno, totalenter= (SELECT SUM(enteramount) FROM storeinfo WHERE storeinfo.clothno=#temptable.clothno AND storeinfo.colorno=#temptable.colorno AND ( case storeinfo.produceno is null or #temptable.produceno is null when false then (storeinfo.produceno=#temptable.produceno) when true then (storeinfo.produceno is null and #temptable.produceno is null ) end ) AND date BETWEEN date1 AND date2) FROM #temptable GROUP BY clothno,colorno,produceno空的也不应该完全排除。如果都是空的应该也是符合条件。
totalenter=
(SELECT SUM(enteramount) FROM storeinfo
WHERE produceno is not null AND
storeinfo.clothno=#temptable.clothno AND
storeinfo.colorno=#temptable.colorno AND
storeinfo.produceno=#temptable.produceno AND
date BETWEEN date1 AND date2)
FROM #temptable
WHERE produceno is not null
GROUP BY clothno,colorno,produceno
另外, date、date1、date2都是storeinfo的字段吗?
totalenter=
(SELECT SUM(enteramount) FROM storeinfo
WHERE storeinfo.clothno=#temptable.clothno
AND storeinfo.colorno=#temptable.colorno
AND ( case storeinfo.produceno is null or #temptable.produceno is null
when false then (storeinfo.produceno=#temptable.produceno)
when true then (storeinfo.produceno is null and
#temptable.produceno is null )
end )
AND date BETWEEN date1 AND date2)
FROM #temptable
GROUP BY clothno,colorno,produceno空的也不应该完全排除。如果都是空的应该也是符合条件。