我永远不会与欧洲妇联竞争,但我不得不说一下这个问题。
如果增加白色空间,我希望你看到我所说的话:
SELECT SUM( (COUNT(BookingID) * CategoryPrice) ) AS TotalAmount
FROM Booking
, Production
WHERE Booking.PerformanceID IN ( SELECT PerformanceID
FROM Performance
WHERE PerformanceID = 1 )
AND Production.ProductionID IN ( SELECT ProductionID FROM Performance
WHERE PerformanceID = 1 )
GROUP BY BookingID, CategoryPrice
UNION ALL
SELECT SUM( (COUNT(BookingID) * CategoryPrice)) AS TotalAmount
FROM Booking
, Production
WHERE Booking.PerformanceID IN ( SELECT PerformanceID
FROM Performance
WHERE PerformanceID = 2 )
AND Production.ProductionID IN ( SELECT ProductionID
FROM Performance
WHERE PerformanceID = 2 )
GROUP BY BookingID, CategoryPrice
打破了,只有<>>>>>,因为你回到两个牢房是分析职能,也是工会。
- You re doing a cartesian join between
booking and production, this means that you multiply the number of rows in each by each other.
- Your sub-selects on
performance are returning one value, which is already known. There s no reason to do them at all.
- You re implicitly converting numbers into strings and back into numbers again.
- You re scanning a table or index 8 times here!
看来,如果你想要每个业绩的合计数额,可以把你的询问简化到以下几个方面:
SELECT SUM(bookings * CategoryPrice)
FROM ( SELECT CategoryPrice , count(*) as bookings
FROM Booking b
JOIN performance per
ON p.performanceid = per.performanceid
JOIN Production p
ON p.productionid = per.productionid
WHERE p.performanceid in (1, 2)
GROUP BY CategoryPrice
)
Please note the explicit join syntax, this has been around for a few decades, makes things a lot clearer and helps stop mistakes. This query will do two range scans, one of booking and one of production, assuming you have indexes on performanceid on both tables. It ll also do a unique scan of performance assuming that performanceid is the primary key of this table.
作为对这项工作的解释,我现在最后设法使你的计划正确! 我们选择两种性能:1和2。 然后,我们选择与这些生产有关的每一种生产,并选择与这些生产相关的所有图书。 您可以进一步简化,这取决于表<代码>price。 然后,我们拿到每个<代码>price的账号,并将这些账面产品的总价值计算出来。
作为建议的一部分,我谨always建议了解您期望从<>>之前的查询中恢复哪些价值。 最好能够并且确实犯错。 由于你能够看到回归的数值不正确,能够赶上这些数值,将会有所帮助。
Further Reading:
