我有 2 張桌子
小販
ID | userid| address | Country
1 | 10 | NY | US
2 | 20 | Mumbai | INDIA
events_todo
ID | events_id| vendor| status
1 | 1 | 10 | Completed
2 | 2 | 20 | Inprogress
所以我想加入這兩個表并獲取所有供應商表資料。我想根據表中是否存在userid表來加入。如果狀態已完成，我還想顯示 Count 列。例如：vendorevent_todovendor
ID | userid | address | Country | events_id | status | Count
1 | 10 | NY | US | 1 | Completed | 1
2 | 20 | Mumbai | INDIA | 2 | Inprogress| 0
我已經應用了以下查詢，我得到了結果，但無法獲得事件狀態的計數
SELECT `vendors`.`id`, `vendors`.`userid`, `vendors`.`address`,`vendors`.`country` AS `updatedAt`, `vendors`.`userid` IN (SELECT sum(events_todo.status) AS completed FROM events_todo ) AS `completed`, `events_todo`.`id` AS `events_todo.id`, `events_todo`.`events_id` AS `events_todo.events_id`, `events_todo`.`category` `events_todo.vendor`, `events_todo`.`created_by` `events_todo.status` FROM `vendors` AS `vendors` LEFT OUTER JOIN `events_todo` AS `events_todo` ON `vendors`.`userid` = `events_todo`.`vendor` WHERE (`vendors`.`city` LIKE '%%' AND `vendors`.`state` LIKE '%%' AND `vendors`.`country` LIKE '%%') AND events_todo.status IS NOT NULL
我的狀態計數為 0 似乎總和無法正常作業。請提出需要做什么。
uj5u.com熱心網友回復：
您可以使用這樣的簡單查詢
SELECT v.id, v.user_id, v.address, v.country, e.events_id, e.status, IF(e.status = 'Completed', 1, 0) AS count
FROM vendors AS v
LEFT JOIN events_todo AS e ON e.vendor = v.user_id
輸出
| ID | 用戶身份 | 地址 | 國家 | events_id | 地位 | 數數 |
|---|---|---|---|---|---|---|
| 1 | 10 | 紐約 | 我們 | 1 | 完全的 | 1 |
| 2 | 20 | 孟買 | 印度 | 2 | 進行中 | 0 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/515068.html
下一篇：內部連接??2個表的問題