SQL資料表兩個欄位關聯同一張資料表，sql欄位

SQL資料表兩個欄位關聯同一張資料表，sql欄位

資料表temp結構

-- Table "temp" DDLCREATE TABLE `temp` (  `page_id` bigint(20) DEFAULT NULL,  `section_id` bigint(20) DEFAULT NULL,  `visit_category_id` bigint(20) DEFAULT NULL,  `cart_category_id` bigint(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

資料表category結構

-- Table "category" DDLCREATE TABLE `category` (  `category_id` bigint(20) DEFAULT NULL,  `category_name` varchar(128) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

業務需求

關聯temp表和category表，取出visit_category_id和cart_category_id對應的category_name

解決方案

select a.page_id,a.section_id,a.visit_category_id,b.category_name,a.cart_category_id,c.category_name from temp a left outer join category b on (a.visit_category_id=b.category_id) left outer join category c on (a.cart_category_id=c.category_id);

查詢結果