时间戳

首页 > 解决方案 > SQL Query, Getting Rid of Repeat Selects

python - SQL Query, Getting Rid of Repeat Selects

问题描述

I'm using sqlite3 in python and I have a table that has the following columns:

recordid(int), username(text), locations(text), types(text), occupancy(int), time_added(datetime), token(text) and (undo).

I have the following query where I am selecting data from the table depending on what the occupancy is and the time added is between the 2 specified times the user inputs which is start_date and 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)

Is there anyway to condense this so I don't have to copy and paste the same thing multiple times just to change the occupancy? I tried using a for loop but that didn't really get me anywhere.

Cheers!

标签： pythonsqlsqlite

解决方案

我很确定可以大大简化查询：

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;

不过，我不确定这是否正是您的问题所在。

推荐阅读