无法获取具有特定条件的所有记录

Question

我有两个表property和property_meta，property表包含所有记录，而property_meta包含每个属性的所有特征和深度细节。

我想使用以下可选参数搜索房产：

• 标题
• 区域id
• 区域id
• 城市id

property表具有以下结构：

| id | title | deleted
1     test    0
2     test2   0

和CCD_ 6具有这样的结构：

| id | property_id | meta_key        | meta_value  
1       1           property_area      1208
2       1           property_region    1207
3       1           property_city      1237
4       2           property_area      1208
5       2           property_region    1207
6       2           property_city      1237

我创建了以下查询：

SELECT p.* 
FROM property p
INNER JOIN property_meta pm ON p.id = pm.property_id 
WHERE LOWER(p.name) LIKE concat("%%") AND p.deleted = 0 
AND (pm.meta_key = 'property_area' AND pm.meta_value IN (1208)) 
AND (pm.meta_key = 'property_region' AND pm.meta_value IN (1207)) 
AND (pm.meta_key = 'property_city' AND pm.meta_value IN (1237))
GROUP BY p.id

不幸的是，这个查询返回一个空结果，但如果我删除以下条件：

AND (pm.meta_key = 'property_region' AND pm.meta_value IN (1207)) 
AND (pm.meta_key = 'property_city' AND pm.meta_value IN (1237))

我两排都有，我犯了哪个错误？

Answer 1

它们不可能通过一行全部有效，因为在运行查询时和子句是互斥的。

你必须检查这三个是否同时有效，例如通过总和，比如我的

所以使用

SELECT 
p.id
FROM
property p
INNER JOIN
(SELECT 
`property_id`
FROM
property_meta pm
GROUP BY `property_id`
HAVING SUM(pm.meta_key = 'property_area'
AND pm.meta_value IN (1208)) + SUM(pm.meta_key = 'property_region'
AND pm.meta_value IN (1207)) + SUM(pm.meta_key = 'property_city'
AND pm.meta_value IN (1237)) = 3) pm ON p.id = pm.property_id
WHERE
LOWER(p.title) LIKE CONCAT('%%')
AND p.deleted = 0
GROUP BY p.id

CREATE TABLE property  (
`id` INTEGER,
`title` VARCHAR(5),
`deleted` INTEGER
);
INSERT INTO property 
(`id`, `title`, `deleted`)
VALUES
('1', 'test', '0'),
('2', 'test2', '0');
CREATE TABLE property_meta  (
`id` INTEGER,
`property_id` INTEGER,
`meta_key` VARCHAR(15),
`meta_value` INTEGER
);
INSERT INTO property_meta 
(`id`, `property_id`, `meta_key`, `meta_value`)
VALUES
('1', '1', 'property_area', '1208'),
('2', '1', 'property_region', '1207'),
('3', '1', 'property_city', '1237'),
('4', '2', 'property_area', '1208'),
('5', '2', 'property_region', '1207'),
('6', '2', 'property_city', '1237');

✓✓✓✓

SELECT 
p.id
FROM
property p
INNER JOIN
(SELECT 
`property_id`
FROM
property_meta pm
GROUP BY `property_id`
HAVING SUM(pm.meta_key = 'property_area'
AND pm.meta_value IN (1208)) + SUM(pm.meta_key = 'property_region'
AND pm.meta_value IN (1207)) + SUM(pm.meta_key = 'property_city'
AND pm.meta_value IN (1237)) = 3) pm ON p.id = pm.property_id
WHERE
LOWER(p.title) LIKE CONCAT('%%')
AND p.deleted = 0
GROUP BY p.id

|id||-：||1||2|

<blockquote\

SELECT 
`property_id`
FROM 
property_meta pm
GROUP BY  `property_id`
HAVING  SUM(pm.meta_key = 'property_area' AND pm.meta_value IN (1208)) +
SUM(pm.meta_key = 'property_region' AND pm.meta_value IN (1207)) +
SUM(pm.meta_key = 'property_city' AND pm.meta_value IN (1237)) = 3

>

|property_id||----------------||1||2|

db<gt；小提琴这里

Answer 2

您的一行不可能满足所有条件。您应该使用"OR"而不是"AND"：

SELECT p.* 
FROM property p
INNER JOIN property_meta pm ON p.id = pm.property_id 
WHERE LOWER(p.name) LIKE concat("%%") AND p.deleted = 0 
AND ((pm.meta_key = 'property_area' AND pm.meta_value IN (1208)) 
OR (pm.meta_key = 'property_region' AND pm.meta_value IN (1207)) 
OR (pm.meta_key = 'property_city' AND pm.meta_value IN (1237)))
GROUP BY p.id

Answer 3

您需要在可选参数之间使用OR而不是AND。行的元值不可能是1207 AND 1237

SELECT p.* 
FROM property p
INNER JOIN property_meta pm ON p.id = pm.property_id 
WHERE LOWER(p.name) LIKE concat("%%") AND p.deleted = 0 
AND 
(
(pm.meta_key = 'property_area' AND pm.meta_value IN (1208)) 
OR (pm.meta_key = 'property_region' AND pm.meta_value IN (1207)) 
OR (pm.meta_key = 'property_city' AND pm.meta_value IN (1237))
)
GROUP BY p.id