SELECT StockID,productName,lastCount,lastAmount FROM (SELECT * FROM stocks WHERE st_date < '2011-04-17' ORDER BY st_date DESC,billCode DESC,itemID DESC) a GROUP BY productName
INNER JOIN
(SELECT productName, SUM(incount),SUM(inAmount),SUM(outcount),SUM(outAmount) FROM stocks WHERE st_date >= '2011-04-17' AND st_date < = '2011-04-19' GROUP BY productname )AS c
ON a.productName = c.productName;
这两个select语句单独执行都是正常的,可是做了内连接运行就报错,今天第一次用连接,不知道哪出问题了,还请大家帮忙
INNER JOIN
(SELECT productName, SUM(incount),SUM(inAmount),SUM(outcount),SUM(outAmount) FROM stocks WHERE st_date >= '2011-04-17' AND st_date < = '2011-04-19' GROUP BY productname )AS c
ON a.productName = c.productName;
这两个select语句单独执行都是正常的,可是做了内连接运行就报错,今天第一次用连接,不知道哪出问题了,还请大家帮忙
(SELECT * FROM stocks WHERE st_date < '2011-04-17' GROUP BY productName ORDER BY st_date DESC,billCode DESC,itemID DESC) a
INNER JOIN
(SELECT productName,SUM(incount),SUM(inAmount),SUM(outcount),SUM(outAmount) FROM stocks
WHERE st_date>='2011-04-17' AND st_date<= '2011-04-19' GROUP BY productname )
AS c
ON a.productName = c.productName
有歧义不知道是咋回事?
(SELECT * FROM stocks WHERE st_date < '2011-04-17' ORDER BY st_date DESC,billCode DESC,itemID DESC) a
INNER JOIN
(SELECT productName,SUM(incount),SUM(inAmount),SUM(outcount),SUM(outAmount) FROM stocks
WHERE st_date>='2011-04-17' AND st_date<= '2011-04-19' GROUP BY productname )
AS c
ON a.productName = c.productName
GROUP BY a.productName
SELECT StockID,a.productName,lastCount,lastAmount FROM
(SELECT * FROM stocks WHERE st_date < '2011-04-17' ORDER BY st_date DESC,billCode DESC,itemID DESC) a RIGHT JOIN
(SELECT stocks.productName,SUM(incount),SUM(inAmount),SUM(outcount),SUM(outAmount) FROM stocks
WHERE st_date>='2011-04-17' AND st_date<= '2011-04-19' GROUP BY stocks.productname )AS c
ON a.productName = c.productNameGROUP BY productname
你的SQL语句不是标准的SQL语句,你要达到什么目的,举例说明
把解决代码和大家分享一下
SELECT * FROM
(SELECT StockID,a.productName,lastCount AS begincount,lastAmount AS beginAmount
FROM (SELECT * FROM stocks WHERE st_date < '2011-04-17' ORDER BY st_date DESC,billCode DESC,itemID DESC) a GROUP BY a.productname) b
JOIN
(SELECT productName,SUM(incount) AS incount,SUM(inAmount) AS inAmount,SUM(outcount) AS outCount,SUM(outAmount) AS outAmount FROM stocks
WHERE st_date>='2011-04-17' AND st_date<= '2011-04-19' GROUP BY productname) c
JOIN
(SELECT lastcount,lastamount,productName FROM (SELECT * FROM stocks WHERE st_date <= '2011-04-19' ORDER BY st_date DESC,billCode DESC,itemID DESC) atlast GROUP BY productName) d
ON b.productname=c.productname AND b.productname = d.productname