查詢以根據應用於同一表的比較過濾表？

案例&問題：

考慮一個有一些房間和部門需要授權才能進入的公司大樓。

員工必須掃描他們的徽章才能獲得授權。

如果員工在裡面（已經掃描了徽章），系統會相應地警告她/他並且不開門。

主管應該能夠在任何給定時間查看和/或報告特定房間內的所有員工。

在幾個 StackOverflow/StackExchange 答案的幫助下，檢查員工是否在裡面有些微不足道。

我的問題是在報告階段。

問題是如何過濾表格以高效地顯示目前在給定點內的人。

目前的情況和我到目前為止所做的事情：

我的後端程式碼正在遍歷所有記錄，並一一檢查它們是否在裡面。

然而，我懷疑這在性能方面和/或模組化/責任問題上並不理想（*儘管我可能錯了）；*將來，我需要允許其他客戶端應用程序連接到數據庫，而無需他們編寫自己的程式碼/邏輯來檢查或報告內部狀態。

樣品表：

+-----+----------+---------------+-----------+-----------------+
|  id | code     | point         | action    | timestamp       |
+-----+----------+---------------+-----------+-----------------+
|   1 | 11111111 | Department A  | pass_in   | 1561369024008   |
|   2 | 11111111 | Department A  | pass_out  | 1561369024007   |
|   3 | 11111111 | Department A  | pass_in   | 1561369024006   |
|   5 | 22222222 | Department B  | pass_in   | 1561369024005   |
|   6 | 22222222 | Department A  | pass_out  | 1561369024004   |
|   7 | 22222222 | Department A  | pass_in   | 1561369024003   |
+-----+----------+---------------+-----------+-----------------+

範常式式碼：

var point = 'Department A';
var recordsInPoint = helper
                       .fetchAllRecords()
                       .filter( record => helper.isInside( record, point ) );

查詢以檢查是否在內部：

SELECT
   ( count_compare.count_in > count_compare.count_out ) AS is_inside
FROM
(
   SELECT
       (
       SELECT 
           COUNT(`id`)
       FROM 
           scanner_log
       WHERE 
           `code`='11111111' AND 
           `point`='Department A' AND 
           `action`='pass_in'
       )  AS count_in,
       (
       SELECT 
           COUNT(`id`)
       FROM 
           scanner_log
       WHERE 
           `code`='11111111' AND 
           `point`='Department A' AND 
           `action`='pass_out'
       ) AS count_out
) AS `count_compare`;

問題：

• 如何將報告邏輯從後端移動到數據庫？

• 如果由 db server 處理，它真的會更快、更省資源嗎？

想法和選擇：

• 創建一個儲存過程並通過循環遍歷所有記錄將後端邏輯傳送到db。

老實說，我認為這沒有什麼好處。相同的循環方法，只是由數據庫伺服器執行。此外，還有一個主要缺點，因為我沒有一個用於日誌的表，每個建築物或公司都有單獨的表。而且我不能將表名作為參數傳遞，據我所知 mySQL / MariaDB 不支持這一點。我必須使用 PREPARE QUERY 和 EXECUTE QUERY 命令，這對我來說聽起來有點矯枉過正。

• 創建另一個嵌套的臨時表並以某種方式加入它們以進行過濾，但我無法理解它，真的，什麼都沒有。

感謝您的所有幫助和見解。

在這種情況下，我建議有兩個表：

• History（就像你所擁有的一樣）。這是已發生的所有事情（IN 和 OUT）的列表（a la 審計跟踪）。
• Current– 每個人最近的位置。

History行數不斷增加，可能與手頭的問題無關。

Current每人包含一行。一個簡單SELECT的帶WHERE子句應該挑選出所有“IN”（或“OUT”）的人。

“每個建築物或公司的單獨表” - 不。這是我今天第三次建議不要使用多個相同的模式。

另一方面，Current可能History有相同的列。這是上一段的一個例外。

不要按部門或建築物分開。

引用自：https://dba.stackexchange.com/questions/241262