我有以下結構：
ChatMessages表：
Column | Type | Collation | Nullable | Default
----------- ----------------------------- ----------- ---------- ---------
Id | text | | not null |
SenderId | text | | |
訊息 | 文本 | | |
ChatId | 文本 | | |
CreatedAt | timestamp without time zone| | not null |
索引。
"PK_ChatMessages" PRIMARY KEY, btree ("Id")
"IX_ChatMessages_ChatId" btree ("ChatId")
"IX_ChatMessages_SenderId" btree ("SenderId")
Foreign-key constraints:
"FK_ChatMessages_ChatUsers_SenderId" FOREIGN KEY ("SenderId") REFERENCES "ChatUsers"("Id") ON DELETE RESTRICT
"FK_ChatMessages_Chats_ChatId" FOREIGN KEY ("ChatId") REFERENCES "Chats" ("Id") ON DELETE RESTRICT
ChatUsers表：
Column | type | Collation | Nullable | Default
----------- ----------------------------- ----------- ---------- ---------
Id | text | | not null |
用戶名 | 文本 | | |
電子郵件 | 文本 | | |
CreatedAt | timestamp without time zone| | not null |
索引。
"PK_ChatUsers" PRIMARY KEY, btree ("Id")
參考 by:
TABLE "" ChatMessages"" CONSTRAINT "FK_ChatMessages_ChatUsers_SenderId" FOREIGN KEY ("SenderId") REFERENCES "ChatUsers"("Id") ON DELETE RESTRICT
TABLE ""Chats"" CONSTRAINT "FK_Chats_ChatUsers_User1Id" FOREIGN KEY ("User1Id") REFERENCES "ChatUsers"("Id") ON DELETE RESTRICT
TABLE ""Chats"" CONSTRAINT "FK_Chats_ChatUsers_User2Id" FOREIGN KEY ("User2Id") REFERENCES "ChatUsers"("Id") ON DELETE RESTRICT
如果我執行select "SenderId", "Email", "Message" from "ChatMessages", "ChatUsers";我收到一個包含所有資訊的串列，然而每條資訊都被乘以用戶的電子郵件地址數量：
SenderId | Email | Message
-------------------------------------- --------------------- -------------------------------------------------------
2f5aca90-0599-400C- 8455-31a22c1d4dcd | user1@example. com | 這個是一個測驗資訊。
2f5aca90-0599-400C- 8455-31a22c1d4dcd | user1@example. com | 這個也是一個測驗資訊。
b3e218e6-dd41-4223-978b-a54f46fc465e | user1@example. com | okay ...
2f5aca90-0599-400C- 8455-31a22c1d4dcd | user2@example. com | 這個是一個測驗資訊。
2f5aca90-0599-400C- 8455-31a22c1d4dcd | user2@example. com | 這個也是一個測驗資訊。
b3e218e6-dd41-4223-978b-a54f46fc465e | user2@example. com | okay ...
但是，我希望它能像下面這樣顯示：
SenderId | Email | Message
-------------------------------------- --------------------- -------------------------------------------------------
2f5aca90-0599-400C- 8455-31a22c1d4dcd | user1@example. com | 這個是一個測驗資訊。
2f5aca90-0599-400C- 8455-31a22c1d4dcd | user1@example. com | 這個也是一個測驗資訊。
b3e218e6-dd41-4223-978b-a54f46fc465e | user2@example. com | okay ...
什么樣的查詢才是正確的呢？
uj5u.com熱心網友回復：
試試這個（postgres的語法）：
SELECT cm.SenderId, cu.Email, cm. FROM ChatMessages cm JOIN ChatUsers cu ON cm.SenderId = cu.Id
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/334009.html
標籤：