mysql - 连接两个表，其中一个表名在另一个表内
问题描述
我正在属性表中注册各种属性。根据物业类型，使用三个新表，即公寓、别墅、土地来存储特定数据。所以我拥有的是一个主表，其中包含属性 URL、属性类型、参考表名称（三个子表名称中的任何一个 - 公寓、别墅、土地）和 ref id，它是子表的主自动增量键。
我尝试了三个单独的选择查询和结果的联合。缺点是结果将主要来自三者中使用的第一个表名。
function getMyProperty() {
$tables = $this->getPropertyTables();
$result = array();
foreach ($tables as $key => $table) {
$this->db->select('a.main_heading, a.sub_heading, a.location, a.about_property, a.property_price, a.available_from, p.property_url');
$this->db->from('property p');
$this->db->join($table . ' a', 'p.ref_id = a.id');
$this->db->where('p.posted_by', $this->session->user_id);
$this->db->where('p.ref_table', $this->db->dbprefix($table));
$query = $this->db->get();
$res = $query->result();
$result = array_merge($result, $res);
}
return $result;
}
数据库架构
CREATE TABLE `apartment` (
`id` int(11) NOT NULL,
`main_heading` varchar(256) NOT NULL,
`sub_heading` text NOT NULL,
`build_up_area` int(11) NOT NULL,
`carpet_area` int(11) NOT NULL,
`no_of_bedrooms` int(11) NOT NULL,
`bathrooms` int(11) NOT NULL,
`available_from` date NOT NULL,
`furnishing` varchar(256) NOT NULL,
`facing` varchar(100) NOT NULL,
`flooring` varchar(256) NOT NULL,
`parking` varchar(256) NOT NULL,
`width_of_facing_road` varchar(100) NOT NULL,
`property_age` int(11) NOT NULL,
`property_price` decimal(16,2) NOT NULL DEFAULT '0.00',
`address` text NOT NULL,
`about_property` text NOT NULL,
`location` varchar(256) NOT NULL,
`amenities` text NOT NULL,
`owner_name` varchar(256) NOT NULL,
`owner_email` varchar(256) NOT NULL,
`owner_phone` varchar(100) NOT NULL,
`active` enum('Y','N') NOT NULL DEFAULT 'Y'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
INSERT INTO `apartment` (`id`, `main_heading`, `sub_heading`, `build_up_area`, `carpet_area`, `no_of_bedrooms`, `bathrooms`, `available_from`, `furnishing`, `facing`, `flooring`, `parking`, `width_of_facing_road`, `property_age`, `property_price`, `address`, `about_property`, `location`, `amenities`, `owner_name`, `owner_email`, `owner_phone`, `active`) VALUES
(2, 'My Appartment', 'Place', 255, 400, 4, 4, '2019-08-08', 'semi', 'north', 'vitrified', '2', '15', 15, '15.00', 'ghkgkgk', 'jkhjkhjk', 'Kochi', '', 'Agent', 'abc@edg.com', '9876543210', 'Y'),
(3, 'My Appartment 2', 'Test', 255, 400, 4, 4, '2019-08-08', 'semi', 'north', 'vitrified', '2', '15', 15, '15.00', 'ghkgkgk', 'jkhjkhjk', 'Kochi', '', 'Agent', 'abc@edg.com', '9876543210', 'Y');
CREATE TABLE `land` (
`id` int(11) NOT NULL,
`main_heading` varchar(256) NOT NULL,
`sub_heading` text NOT NULL,
`plot_area` int(11) NOT NULL,
`gated_colony` int(11) NOT NULL,
`open_sides` int(11) NOT NULL,
`available_from` date NOT NULL,
`dimensions` varchar(256) NOT NULL,
`facing` varchar(100) NOT NULL,
`boundary_wall` varchar(256) NOT NULL,
`parking` varchar(256) NOT NULL,
`width_of_facing_road` varchar(100) NOT NULL,
`property_age` int(11) NOT NULL,
`property_price` decimal(16,2) NOT NULL DEFAULT '0.00',
`address` text NOT NULL,
`about_property` text NOT NULL,
`location` varchar(256) NOT NULL,
`owner_name` varchar(256) NOT NULL,
`owner_email` varchar(256) NOT NULL,
`owner_phone` varchar(100) NOT NULL,
`active` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
CREATE TABLE `property` (
`property_id` int(11) NOT NULL,
`posted_by` int(11) NOT NULL,
`post_type` enum('RENT','SELL','LEASE') NOT NULL,
`property_type` enum('VILLA','APARTMENT','LAND') NOT NULL,
`property_url` varchar(50) NOT NULL,
`ref_table` varchar(50) NOT NULL DEFAULT '',
`ref_id` int(11) NOT NULL DEFAULT '0',
`posted_on` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
INSERT INTO `property` (`property_id`, `posted_by`, `post_type`, `property_type`, `property_url`, `ref_table`, `ref_id`, `posted_on`) VALUES
(4, 1, 'SELL', 'VILLA', 'lyohlp', 'villa', 2, '2019-08-05'),
(5, 1, 'SELL', 'APARTMENT', 'cvbdit', 'apartment', 2, '2019-08-05'),
(6, 2, 'SELL', 'APARTMENT', 'qwerty', 'apartment', 3, '2019-08-05'),
(7, 3, 'RENT', 'VILLA', 'asdfgh', 'villa', 3, '2019-08-05');
CREATE TABLE `villa` (
`id` int(11) NOT NULL,
`main_heading` varchar(256) NOT NULL,
`sub_heading` text NOT NULL,
`build_up_area` int(11) NOT NULL,
`carpet_area` int(11) NOT NULL,
`no_of_bedrooms` int(11) NOT NULL,
`bathrooms` int(11) NOT NULL,
`available_from` date NOT NULL,
`furnishing` varchar(256) NOT NULL,
`facing` varchar(100) NOT NULL,
`flooring` varchar(256) NOT NULL,
`total_area` varchar(256) NOT NULL,
`width_of_facing_road` varchar(100) NOT NULL,
`property_age` int(11) NOT NULL,
`property_price` decimal(16,2) NOT NULL DEFAULT '0.00',
`address` text NOT NULL,
`about_property` text NOT NULL,
`location` varchar(256) NOT NULL,
`amenities` text NOT NULL,
`owner_name` varchar(256) NOT NULL,
`owner_email` varchar(256) NOT NULL,
`owner_phone` varchar(100) NOT NULL,
`active` enum('Y','N') NOT NULL DEFAULT 'Y'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
INSERT INTO `villa` (`id`, `main_heading`, `sub_heading`, `build_up_area`, `carpet_area`, `no_of_bedrooms`, `bathrooms`, `available_from`, `furnishing`, `facing`, `flooring`, `total_area`, `width_of_facing_road`, `property_age`, `property_price`, `address`, `about_property`, `location`, `amenities`, `owner_name`, `owner_email`, `owner_phone`, `active`) VALUES
(2, 'My Villa', 'Kakkanad', 54, 5, 4, 4, '2019-08-06', 'semi', 'west', 'not-vitrified', '111', '10', 0, '12.00', 'fhgfgh', 'ghfghf', 'Kochi', 'car_parking,water_supply,garden,fitness_center,shower,fridge', 'dfdfdf', 'abc@edg.com', '9876543210', 'Y'),
(3, 'My Villa 2', 'Place', 54, 5, 4, 4, '2019-08-06', 'semi', 'west', 'not-vitrified', '111', '10', 0, '12.00', 'fhgfgh', 'ghfghf', 'Kochi', 'car_parking,water_supply,garden,fitness_center,shower,fridge', 'dfdfdf', 'abc@edg.com', '9876543210', 'Y');
ALTER TABLE `apartment`
ADD PRIMARY KEY (`id`);
ALTER TABLE `land`
ADD PRIMARY KEY (`id`);
ALTER TABLE `property`
ADD PRIMARY KEY (`property_id`),
ADD KEY `ref_id` (`ref_id`);
ALTER TABLE `villa`
ADD PRIMARY KEY (`id`);
ALTER TABLE `apartment`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
ALTER TABLE `land`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `property`
MODIFY `property_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
ALTER TABLE `villa`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
无论我提供的表格的顺序如何，我都希望结果基于发布日期的减少。
解决方案
正如我们所知，只有一种类型可以与属性进行映射。
因此数据将仅从一个表（存储该属性类型的详细信息）生成，其他表将产生空值。
如果其中只有一个不为空，则从多个列中选择数据的一种好方法是使用CONCAT_WSor COALESCE。
SELECT
P.property_id, P.property_url,
CONCAT_WS('', V.main_heading, A.main_heading, L.main_heading) AS main_heading,
CONCAT_WS('', V.sub_heading, A.sub_heading, L.sub_heading) AS sub_heading,
CONCAT_WS('', V.location, A.location, L.location) AS location,
CONCAT_WS('', V.about_property, A.about_property, L.about_property) AS about_property,
CONCAT_WS('', V.property_price, A.property_price, L.property_price) AS property_price,
CONCAT_WS('', V.available_from, A.available_from, L.available_from) AS available_from,
P.posted_on
FROM property P
LEFT JOIN villa V ON P.ref_table = 'villa' AND P.ref_id = V.id
LEFT JOIN apartment A ON P.ref_table = 'apartment' AND P.ref_id = A.id
LEFT JOIN land L ON P.ref_table = 'land' AND P.ref_id = L.id
ORDER BY P.posted_on DESC;
COALESCE在地方使用CONCAT_WS就像：
COALESCE(V.main_heading, A.main_heading, L.main_heading) AS main_heading
推荐阅读
- java - 多个片段可以共享一个 XML 布局吗
- python - 如何在机器学习模型中使用 test_proportion 数据？
- php - 如何在 php 中使用 date() 显示日期和时间
- node.js - 在 Nodejs 上下文中，为什么“不要阻塞事件循环”这句话即使在开发中也存在，它无法避免？
- java - 随机词生成器，我想限制它从 .txt 文件中读取的词的大小，最多说 3 个字符
- javascript - 缩小谷歌地图故障
- python - 如何在点击它时调用的函数仍在执行时删除 Tkinter 中的按钮？
- html - 仅通过复选框提交选择表单
- android - 如何在 Android Studio 中从 Fragment 打开 Activity
- database - Ionic React 我的组件在使用 map() 首次查看时未呈现