使用 where 和排序子句連接 3 個表

我有 3 張桌子vendors，vendor_models和cameras。我試圖從數據庫中獲取一些值，vendor_models但名稱vendor也來自。

每個camera都有vendor_modelas的引用model_id。

10個攝像頭可以有一個相同vendor_model的。

我正在做這個查詢

SELECT v0."id", v0."vendor_id", v0."exid", v0."name", v0."username",
  v0."password", v0."jpg_url", v0."h264_url",  v0."mjpg_url", v0."mpeg4_url",
  v0."mobile_url", v0."lowres_url", v0."shape", v0."resolution", 
  v0."official_url", v0."more_info", v0."audio_url", v0."poe",
  v0."wifi", v0."upnp", v0."ptz", v0."infrared", v0."varifocal",
  v0."sd_card", v0."audio_io", v0."discontinued", v0."onvif",
  v0."psia", v0."channel", v0."created_at", v0."updated_at"
FROM "vendor_models" AS v0
INNER JOIN "vendors" AS v1 ON v0."vendor_id" = v1."id"
WHERE ((lower(v0."name") LIKE $1))
 OR (lower(v1."name") LIKE $2)
ORDER BY v1."name" ["%%", "%%"]

在獲得所有這些值之後。我正在做另一個查詢。如

SELECT c0."id", c0."owner_id", c0."model_id", c0."exid",
  c0."name", c0."timezone", c0."thumbnail_url",
  c0."is_online", c0."offline_reason", c0."is_public",
  c0."is_online_email_owner_notification", c0."alert_emails",
  c0."discoverable", c0."config", c0."mac_address",
  c0."location", c0."last_polled_at", c0."last_online_at",
  c0."created_at", c0."updated_at"
FROM "cameras" AS c0
WHERE (c0."model_id" = $1) [4]

然後給我使用此型號的相機總數。

我的問題是我想將此查詢合併到一個中。

我可以在其中獲得 VendorModel 以及供應商的所有值。以及使用每個供應商型號的相機數量。我現在無法做到這一點，任何幫助都是顯而易見的。

更新：

我已經到了

SELECT vendor_models.id, vendor_models.name, count(cameras.id) as count 
 FROM "vendor_models" 
 INNER JOIN "vendors" ON "vendor_models"."vendor_id" = "vendors"."id" 
 INNER JOIN "cameras" ON "vendor_models"."id" = "cameras"."model_id"
 GROUP BY vendor_models.id, vendor_models.name
 ORDER BY count desc

但我無法獲得 vendor.name .. 你能幫忙嗎？

更新：

SELECT vendor_models.id, vendor_models.name, v.name, count(cameras.id) as count 
 FROM vendor_models
 INNER JOIN vendors as v ON vendor_models.vendor_id = v.id 
 INNER JOIN cameras ON vendor_models.id = cameras.model_id
 GROUP BY vendor_models.id, vendor_models.name, v.name
 ORDER BY v.name asc

我得到了最後一個。你能幫忙讓它變得更好嗎？

仍有改進的餘地。這是您的最後一個版本，使用表別名進行了整理、格式化和縮短：

SELECT vm.id, v.name, vm.name, count(c.id) AS count 
FROM   vendors       v
JOIN   vendor_models vm ON vm.vendor_id = v.id 
JOIN   cameras       c  ON c.model_id = vm.id
GROUP  BY vm.id, v.name, vm.name
ORDER  BY v.name;

剩下的問題：

• 您name在結果中有兩次名稱，這至少可以說是無益的。

（一個光輝的例子，為什麼像“名字”這樣的非描述性名字是壞名字。）

• 排序順序不是完全確定的。
• 沒有它可能的那麼快。

我建議：

SELECT vm.id
    , v.name AS vendor, vm.name AS model  -- column aliases to disambiguate
    , c.camera_count
FROM   vendors       v
JOIN   vendor_models vm ON vm.vendor_id = v.id 
JOIN  (
  SELECT model_id, count(*) AS camera_count  -- count(*) slightly faster
  FROM   cameras
  GROUP  BY 1           -- aggregate first, join later
  ) c ON c.model_id = vm.id
ORDER  BY v.name, vm.name, vm.id; -- add "id" if the names are not unique

如果首先計算每個模型的攝像頭，則連接操作會少得多，並且根本不需要聚合外部列。應該更快。

count(*)count(expression)在沒有 NULL 值的情況下執行相同操作時比（單獨實現）快一點。

由於外部表 ( vendors, vendor_models) 中的列現在根本沒有聚合，因此您可以隨意將這些列中的任何列放在SELECT列表中 - 包括vm.*.

有關的：

• 了解聚合的多表連接
• 與使用聚合的子查詢相關的慢查詢

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