如何創建其條目取決于另一個表中列的內容的列？

我有一個包含多個表的資料庫，我想向一個表中添加一列，該列將根據另一個表的內容填充不同的字串。

以下是感興趣的表格。

CREATE TABLE Locations(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Location VARCHAR(17) NOT NULL,
Is_Property BOOLEAN NOT NULL
);

CREATE TABLE Players(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Player_Name VARCHAR(17) NOT NULL,
Token VARCHAR(17) NOT NULL,
FOREIGN KEY (Token) REFERENCES Tokens(Token),
P_Location VARCHAR(17) NOT NULL,
FOREIGN KEY (P_Location) REFERENCES Locations(Location),
Bank_Balance INT NOT NULL DEFAULT 200);


ALTER TABLE Locations ADD INDEX `Location` (`Location`);

CREATE TABLE Properties AS SELECT id,Location FROM Locations 
WHERE Is_Property = 1;
ALTER TABLE Properties
ADD CONSTRAINT PK_Properties PRIMARY KEY (id),
ADD COLUMN Colour VARCHAR(6),
ADD COLUMN Cost_And_Rent INT,
ADD COLUMN Owned VARCHAR(3);

CREATE TABLE Properties_Owned(
Player_id INT NOT NULL,
Prop_id INT NOT NULL,
PRIMARY KEY(Player_id, Prop_id),
FOREIGN KEY (Player_id) REFERENCES Players(id),
FOREIGN KEY (Prop_id) REFERENCES Properties(id));

Properties和Properties_Owned表在這種情況下很有趣。我想創建一個名為Ownedin的列，Properties并根據主鍵是否出現Prop_id在Properties_Owned. 因此，如果是，Properties.Owned將顯示“是”，如果不是，則顯示“否”。

我試過使用這個CASE函式，但我不確定它是否可以在不呼叫SELECT查詢的情況下使用。下面是我最后一次嘗試這樣做，但語法在某處是錯誤的，或者完全被誤導了。

CASE 
WHEN id IS IN properties_owned.Prop_id THEN Properties.Owned = "Yes"
ELSE "No" ;

它生成錯誤代碼：

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE  WHEN id IS IN properties_owned.Prop_id THEN Properties.Owned = "Yes" ELSE ' at line 1

編輯：由于有樣本資料的請求，這里是：

INSERT INTO Locations(Location,Is_Property) VALUES ("GO", 0),
("London", 1),
("Paris", 0), 
("China", 1), 
("New Zealand", 0), 
("Sydney", 1),; 

INSERT INTO Players(Player_Name,Token,P_Location,Bank_Balance) VALUES 
("Mary","Battleship","London",190),
("Bill","Dog","Paris",500),
("Jane","Car","China",150),
("Norman","Thimble","London",250);

INSERT INTO Properties_Owned(Player_id,Prop_id) VALUES 
(1,1),
(2,2),
(3,3),
(4,4),
(4,5);

因此，Properties_Owned 表將如下所示：

Player_id | Prop_id
----------|---------
1         |1
2         |2
3         |3
4         |4
4         |5

所以在下面的Properties表格中Owned，如果Properties.id出現在Prop_id上面，該Owned列應該產生“是”。

uj5u.com熱心網友回復：

該表Properties_Owned反映了(n:n)關系（多對多）。但是根據您的評論，一個屬性不能同時由多個玩家擁有。這將是一個(n:1)關系（多對一）。在這種情況下，您不需要該Properties_Owned表，只需將一列Player_id（或owned_by_player_id）添加到Properties表中作為參考該表的外鍵Players。

alter table Properties 
  add column Player_id int default null,
  add foreign key (Player_id) references Players(id);

那么該列所需的資訊Owned將已經在同一個表中。您只需要在 SELECT 陳述句中“操作”它。例如：

select 
  p.*,
  case when Player_id is null then 'No' else 'Yes' end as Owned
from Properties p;

無需冗余存盤相同的資訊。那遲早會“咬”你。盡可能避免冗余。

如果您的 MySQL 版本（需要 5.7 ）支持Generated Columns，您還可以讓資料庫保持冗余。

alter table Properties 
  drop column Owned,
  add column Owned varchar(3) 
    as (case when Player_id is null then 'No' else 'Yes' end) virtual;

現在（生成的）列依賴于Player_id列，您不需要（也不能）在那里存盤任何內容，但可以選擇它。virtual意味著它不會被存盤，而是在需要時（即時）生成。現在您可以在查詢中讀取它，就好像它是普通列一樣。

請參閱db-fiddle.com 上的示例

再說一遍：盡可能避免冗余資料。至少使用外鍵來避免資料不一致。

uj5u.com熱心網友回復：

您可以創建一個視圖，按您想要的列擴展您的表格。在那里您可以使用 CASE 陳述句。

CASE WHEN id IN SELECT Prop_id FROM properties_owned THEN 'Yes' ELSE 'No'

如果這不是您想要的，您可以在兩個表上使用觸發器，在創建/洗掉時填充/更新列

最初，您可以使用以下內容填充該列：

UPDATE Prop_id SET properties_owned = CASE WHEN id IN SELECT Prop_id FROM properties_owned THEN 'Yes' ELSE 'No' WHERE

標籤：mysqlsql选择案子

上一篇：如何使SSRS訂閱在每月的第3個作業日開始

下一篇：在SqlLite中根據月份和年份獲取兩個日期之間的資料