我只想要那些特定日期之间必须存在记录的记录 . 对于例如如果给出的日期在2013-7-1和2013-7-17之间，并且我在购买表中注明了日期：2013-6-5，并且在2013-7-17的销售表中输入特定产品的条目 . 它应忽略这些行，并仅选择在所需日期范围内进行销售和购买的那些行 . 我的以下查询无法正常工作 . 由于上面定义的情况除以零异常发生 . 请帮助我 .
SELECT o.Name AS Owner, c.Name AS Company, SUM(sq.PQuantity) - SUM(sq.SQuantity) AS Quantity, SUM(sq.PQuantity * sq.PRate) / SUM(sq.PQuantity) AS Rate,
(SUM(sq.PQuantity) - SUM(sq.SQuantity)) * (SUM(sq.PQuantity * sq.PRate) / SUM(sq.PQuantity)) AS Amount,sq.CompanyId AS CompanyId, o.OwnerId AS OwnerId
FROM (SELECT OwnerId, CompanyId, Quantity AS PQuantity, RatePerShare AS PRate, 0 AS SQuantity, 0 AS SRate, Date
FROM Purchase
UNION ALL
SELECT OwnerId, CompanyId, Quantity AS PQuantity, 0 AS PRate, 0 AS SQuantity, 0 AS SRate, Date
FROM Bonus
UNION ALL
SELECT OwnerId, CompanyId, Quantity AS PQuantity, CostOfShare AS PRate, 0 AS SQuantity, 0 AS SRate, Date
FROM RightShare
UNION ALL
SELECT OwnerId, CompanyId, 0 AS PQuantity, 0 AS PRate, Quantity AS SQuantity, RatePerShare AS SRate, Date
FROM Sales) AS sq INNER JOIN
Owner AS o ON sq.OwnerId = o.OwnerId INNER JOIN
Company AS c ON sq.CompanyId = c.CompanyId
WHERE (sq.OwnerId = @Param1) AND (CONVERT(DATE, sq.Date, 111) BETWEEN @Param2 AND @Param3)
GROUP BY sq.CompanyId, c.Name, o.Name, o.OwnerId
1 回答
将
HAVING SUM(sq.PQuantity) > 0添加到查询的底部 .