-- 查找最新停车记录
SELECT *
FROM parking_record_view
WHERE car_license = ?
ORDER BY start_time DESC
LIMIT 1;
-- 查询插入新的停车记录所需信息
SELECT *
FROM (
    SELECT *
    FROM vehicleinformation
    WHERE car_license = ?
) vi
INNER JOIN userinformation ui ON vi.user_id = ui.user_id
INNER JOIN parkingspotinformation psi ON ui.parking_spot_id = psi.parking_spot_id;
-- 选出停车费消费总额前5名的用户
SELECT user_name, total_parking_fees
FROM userparkingtotalfees
ORDER BY total_parking_fees DESC
LIMIT 5;
近一个月的日停车费收入变化
SELECT DATE(start_time) AS date, SUM(parking_fee) AS total_income
FROM parkingrecord
WHERE start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY DATE(start_time)
ORDER BY DATE(start_time);
根据停车时间进行用户画像(选出未在六点到九点有过停车行为的用户)
SELECT COUNT(*)
FROM parkingspotinformation psi
WHERE NOT EXISTS (
    SELECT *
    FROM parkingrecord pr
    WHERE pr.parking_spot_id = psi.parking_spot_id
    AND TIME(pr.start_time) BETWEEN '18:00:00' AND '21:00:00’);                                    '
-- 分析一周内一天不同时刻中停车位的平均使用时长
SELECT
    HOUR(pr.start_time) AS Hour,
    AVG(TIMESTAMPDIFF(MINUTE, pr.start_time, pr.end_time)) as AverageParkingMinutes
FROM parkingrecord pr
WHERE
    YEARWEEK(pr.start_time, 1) = YEARWEEK(CURDATE(), 1)
GROUP BY Hour
ORDER BY Hour;
-- 查看目前小区的所有车辆和其拥有者的信息
SELECT * FROM vehicleuserview
-- 根据车牌号，品牌，型号进行检索
SELECT * FROM vehicleuserview
WHERE
	car_license LIKE ‘%?%’
	AND brand=?
	AND model=?
	AND address LIKE ‘?号楼%’
-- 通过user_id连接parkingspotinformation和userinformation表对用户车位信息进行查询
SELECT *FROM parkingspotinformation INNER JOIN userinformation ON parkingspotinformation.parking_spot_id =userinformation.parking_spot_id
WHERE userinformation.user_id = ?;
-- 通过user_id连接parkingrecord和userinformation表对用户停车记录进行查询，并以start_time为基准进行排序
SELECT *
FROM parkingrecord
INNER JOIN userinformation ON parkingrecord.parking_spot_id = userinformation.parking_spot_id
WHERE userinformation.user_id = ?
ORDER BY start_time DESC;
-- 查询所有车位的情况
SELECT * FROM parkingspotinformation
-- 查看所有的停车记录及筛选所需记录
SELECT * FROM parking_record_view
WHERE car_license = ?
AND location = ?
AND start_time >= ?
AND end_time <= ?
ORDER BY start_time DESC
-- 展示用户信息
select *
from userinformation
-- 搜索相关用户
select *
from userinformation
where name like '%?'
and parking_spot_id=?
and address=?
-- 排序（按照user_id、name、parking_spot_id）
select *
from userinformation
order by user_id

select *
from userinformation
order by name

select *
from userinformation
order by parking_spot_id
-- 统计用户数量
select count(*)
as total_user
from userinformation
-- 按照性别统计
select gender, count(*)
as gender_count
from userinformation
group by gender
-- 按照用户类型统计
select user_type,count(*)
as user_count
from userinformation
group by user_type
-- 查看特定用户对应的车辆信息
select v.car_license,v.user_id,v.brand,v.model
from vehicleinformation v
join userinformation u on v.user_id=u.user_id
where u.user_id=?
order by v.car_license
-- 查看特定用户对应的车位信息
select p.parking_spot_id,p.spot_type,p.occupancy_status,p.location,p.fee_standard
from userinformation u
left join parkingspotinformation p on u.parking_spot_id=p.parking_spot_id
where p.parking_spot_id=?
-- 查看特定用户对应的IC卡信息
select i.user_id,i.card_number,i.password,i.balance
from userinformation u
inner join icinformation i on u.user_id=i.user_id
where u.user_id=?
-- 通过user_id查询用户ic卡信息
SELECT *
FROM icinformation
WHERE user_id = ?;
-- 通过user_id查询用户的车辆信息
SELECT *
-- FROM vehicleinformation
WHERE user_id = ?
-- 通过user_id查询用户信息
SELECT *
FROM userinformation
WHERE user_id = ?
-- 通过user_id 使用EXISTS语句对时间处于start 和end之间的记录进行查询，同时根据type为基准进行order排序
SELECT *
FROM parkingrecord r
WHERE EXISTS(
SELECT 1
FROM userinformation u
WHERE u.parking_spot_id = r.parking_spot_id AND u.user_id=?)
AND r.start_time>=? AND r.end_time<=?
ORDER BY "+type+" "+order+";
-- 通过spot和fee上下限对fee设定范围内的记录进行条件查询，同时对其进行排序
SELECT *
FROM parkingrecord
WHERE parking_spot_id = ? AND parking_fee Between ? AND ?
ORDER BY "+type+" "+order+";
