關於SQL的相關筆記【長期更新，只發一帖】

場景【1】多表聯查時，主表與關聯表同時與同一張（第三張表）有關聯，類似三角戀關係- -

涉及表：

• HOUSE：記錄了房屋信息
• ROOMS：記錄了房間信息
• HOUSE_STATUS：記錄了狀態信息的中文欄位

房屋信息HOUSE表中有一個關於記錄房屋狀態的欄位，status_id,與HOUSE_STATUS的主鍵表關聯，而房間信息ROOMS表中，也有一個關於記錄房屋狀態的欄位，也是status_id，當HOUSE與ROOMS建立關聯查詢時，兩張表又分別需要與HOUSE_STATUS這張表建立關聯查詢。則出現了標題說的三角戀關係，查詢方式只需要利用別名查詢即可：

SELECT
  HOUSE.ID,
  HOUSE.NAME,
  HOUSE.FLOOR,
  HOUSE.EXPECT_PRICE,
  HOUSE.AREA_NUMBER,
  HOUSE.HAVE_KITCKEN,
  HOUSE.HAVE_BALCONY,
  PROVINCE.provinceName,
  CITY.cityName,
  AREA.areaName,
  COMMUNITY.ID             community_ID,
  COMMUNITY.NAME           community_Name,
  HOUSE_LAYOUT.NAME        layout_Name,
  DECORATION_TYPE.NAME     decorationType_Name,
  HOUSE_STATUS.STATUS_NAME houseStatus_Name,
  PAYMENT.PAYMENT_NAME     payment_Name,
  RENT_MODE.ID             rentMode_ID,
  RENT_MODE.NAME           rentMode_Name,
  ROOMS.ID,
  ROOMS.ROOM_NO,
  ROOMS.HOUSE_STATUS_ID,
  ROOMS.AREA_NUMBER,
  ROOMS.EXPECT_PRICE,
  ROOMS.HAVE_WASHROOM,
  ROOMS.HAVE_KITCKEN,
  ROOMS.HOUSE_STATUS_ID,
  ROOMS.HAVE_DESK,
  ROOMS.HAVE_AIRCONDITIONING,
  ROOMS.HAVE_WARDROBE,
  ROOMS.HAVE_WATERHEATER,
  ROOMS.HAVE_WLAN,
  rooms_Status.ID          rooms_StatusID,
  rooms_Status.STATUS_NAME rooms_StatusName
FROM HOUSE
  JOIN PROVINCE ON HOUSE.PROVINCE_ID = PROVINCE.provinceID
  JOIN CITY ON HOUSE.CITY_ID = CITY.cityID
  JOIN AREA ON HOUSE.AREA_ID = AREA.areaID
  JOIN COMMUNITY ON HOUSE.COMMUNITY_ID = COMMUNITY.ID
  JOIN HOUSE_LAYOUT ON HOUSE.LAYOUT_ID = HOUSE_LAYOUT.ID
  JOIN DECORATION_TYPE ON HOUSE.DECORATION_TYPE_ID = DECORATION_TYPE.ID
  JOIN HOUSE_STATUS ON HOUSE.HOUSE_STATUS_ID = HOUSE_STATUS.ID
  JOIN PAYMENT ON HOUSE.EXPECT_PAYMENT_ID = PAYMENT.ID
  JOIN HOUSE_RENT_MODE ON HOUSE.ID = HOUSE_RENT_MODE.HOUSE_ID
  JOIN RENT_MODE ON HOUSE_RENT_MODE.RENT_MODE_ID = RENT_MODE.ID
  JOIN ROOMS ON HOUSE.ID = ROOMS.HOUSE_ID
  JOIN HOUSE_STATUS rooms_Status ON ROOMS.HOUSE_STATUS_ID = rooms_Status.ID
WHERE HOUSE.ID = 1