我有一张桌子出租:
rentalId int(11)
Customer_customerId int(11)
Vehicle_registrationNumber varchar(20)
startDate datetime
endDate datetime
pickUpLocation int(11)
returnLocation int(11)
booking_time timestamp
timePickedUp timestamp
timeReturned timestamp
和桌上支付:
paymentId int(11)
Rental_rentalId int(11)
amountDue decimal(10,2)
amountPaid decimal(10,2)
paymentDate timestamp
我运行两个分组函数,第一个计算预订数量并按天计算付款总额,该函数只有在省略pickUpLocation`时才能按预期工作,否则会返回不正确的值。:
SELECT COUNT(rentalId) AS number_of_rentals, MONTH(booking_time) AS month,
`YEAR(booking_time) AS year,
CONCAT(DAY(booking_time), '-', MONTH(booking_time), '-',`
YEAR(booking_time) ) AS date, SUM(amountDue) AS total_value, SUM(amountPaid) AS
total_paid, `pickUpLocation`
FROM (`rental`)
JOIN `payment` ON `payment`.`Rental_rentalId` = `rental`.`rentalId`
GROUP BY DAY(booking_time)
HAVING `month` = 2
AND `year` = 2012
AND `pickUpLocation` = 1
ORDER BY `booking_time` desc
LIMIT 31
第二个功能预计将对特定地点的整个月的预订和付款(到期和收到)进行汇总:
SELECT COUNT(rentalId) AS number_of_rentals, MONTH(booking_time) AS month,
YEAR(booking_time) AS year, SUM(amountDue) AS total_value,
SUM(amountPaid) AS total_paid,
`pickUpLocation`
FROM (`rental`)
JOIN `payment` ON `payment`.`Rental_rentalId` = `rental`.`rentalId`
GROUP BY MONTH(booking_time)
HAVING `month` = 2
AND `year` = 2012
AND `pickUpLocation` = 1
ORDER BY `booking_time` desc
它适用于某些位置,而不适用于其他位置(当有很多预订时返回正确的集合,但当只有少数预订时,它返回空集)。我使用MySQL。非常感谢您的帮助。
您正在rental
和payment
之间进行内部联接,这意味着您只能获得已支付的租金。如果你想在结果中找到没有付款信息的租金,你需要使用LEFT JOIN
,而不仅仅是(内部)JOIN
。
请注意,如果没有要计算的付款,这可能会导致结果中出现NULL,因此您可能必须使用其中一个控制流函数来调整查询的输出。
编辑:您也在条件之前GROUP
,这将把一个月的所有行GROUP
合并为一行。由于年份和拾取位置可能不同,您将在这两个字段中获得(可用的)随机值。HAVING
将过滤这些随机字段,留下一个可能为空的结果集。另一方面,WHERE
将看到GROUP
ing之前的每一行,并在逐行的基础上做正确的事情(tm),因此应该将条件放在那里。
(可能应该对您的第一个正在工作的查询进行相同的更改)
在这里演示。
您可能需要将一些条件从HAVING
推送到WHERE
子句:
WHERE YEAR(booking_time) = 2012
AND MONTH(booking_time) = 2
AND `pickUpLocation` = 1
GROUP BY DAY(booking_time)
LIMIT 31
对于特定的月份,您甚至不需要GROUP BY
:
WHERE YEAR(booking_time) = 2012
AND MONTH(booking_time) = 2
AND `pickUpLocation` = 1
关于性能,上述条件不是很好:
WHERE YEAR(booking_time) = 2012
AND MONTH(booking_time) = 2
你应该把它改成:
WHERE booking_time >= '2012-02-01'
AND booking_time < '2012-03-01'
因此查询可以在booking_time
上使用索引(如果您已经或将来添加了索引),因此它不会为表的每一行调用YEAR()
和MONTH()
函数。