SQL查询，消除重复选择

Question

我在python中使用sqlite3，并且我有一个表，其中包含以下列：

recordid(int)、用户名(Text)、位置(Text)、类型(Text)、占用(Int)、time_added(datetime)、标记(Text)和撤消(undo)。

我有下面的查询，我从表中选择数据，取决于占用率是什么，添加的时间在用户输入的两个指定时间之间，即start_date和end_date：

('''SELECT locations, types, 
           (SELECT COUNT (occupancy) FROM traffic WHERE undo = 0 AND occupancy = 1 AND types = ? AND time_added BETWEEN ? AND ?),
           (SELECT COUNT (occupancy) FROM traffic WHERE undo = 0 AND occupancy = 2 AND types = ? AND time_added BETWEEN ? AND ?),
           (SELECT COUNT (occupancy) FROM traffic WHERE undo = 0 AND occupancy = 3 AND types = ? AND time_added BETWEEN ? AND ?),
           (SELECT COUNT (occupancy) FROM traffic WHERE undo = 0 AND occupancy = 4 AND types = ? AND time_added BETWEEN ? AND ?),
    FROM traffic WHERE types = ? GROUP BY type''', 
(vehicle, start_date, end_date, vehicle, start_date, end_date, vehicle, start_date, end_date, vehicle, start_date, end_date, vehicle)

有没有什么方法可以压缩它，这样我就不需要多次复制和粘贴相同的东西，只为了改变占用？我试着使用for循环，但这并没有真正起到什么作用。

干杯!

Answer 1

我非常确定可以大大简化查询：

SELECT type
       SUM( occupancy = 1 ) as cnt_1,
       SUM( occupancy = 2 ) as cnt_2,
       SUM( occupancy = 3 ) as cnt_3,
       SUM( occupancy = 4 ) as cnt_4
FROM traffic
WHERE undo = 0 AND
      type = ? AND
      time_added BETWEEN ? AND ?
GROUP BY type;

不过，我不确定这是否是你的问题所想的。