SQL Oracle 实体属性值 (EAV) 数据库表中带有 WHERE 子句的 LEFT JOIN 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8302491/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LEFT JOIN Columns With WHERE Clause in Oracle Entity Attribute Value(EAV) Database Tables
提问by Mike
I have a USERS table like this
我有一个像这样的 USERS 表
USERS
用户
user_id | username
1 tom
2 sam
I also have a USER_META table like this
我也有一个像这样的 USER_META 表
USER_META
USER_META
user_meta_id | user_id | meta_key | meta_value
1 1 active 1
2 1 car dodge
3 2 active 0
4 2 car honda
My problem is that I need to select the meta_keys activeand and carbut only for users who have a value of 1 for activeso my result set should be something like this
我的问题是，我需要选择 meta_keys active和car，但仅适用于active值为 1 的用户，因此我的结果集应该是这样的
user_id | user_name | user_meta_id | meta_key | meta_value
1 tom 1 active 1
1 tom 2 car dodge
I have tried a few things but I cannot get this type of result set. Here is what I thought might work
我已经尝试了一些东西，但我无法得到这种类型的结果集。这是我认为可能有效的方法
SELECT * FROM USERS
LEFT JOIN USER_META
ON USERS."user_id" = USER_META
AND (USER_META."meta_key" = 'active' OR USER_META."meta_key" = 'car')
WHERE (USER_META."meta_key" = 'active'
AND USER_META."meta_value" = 1)
the problem with this is that I get a result set that is missing the car meta_key/meta_value
问题是我得到的结果集缺少汽车元键/元值
user_id | user_name | user_meta_id | meta_key | meta_value
1 tom 1 active 1
How can I modify the query to get all of the meta information? thanks.
如何修改查询以获取所有元信息？谢谢。
回答by Justin Cave
First off, this sort of entity-attribute-value data model is generally a very poor idea. You're basically re-implementing what a relational database gives you out of the box and queries get very unwieldy very quickly. Generally, you'll need to join in the USER_METAtable N times in order to either get N attributes as columns or in order to have N predicates on your data.
首先，这种实体-属性-值数据模型通常是一个非常糟糕的主意。您基本上是在重新实现关系数据库为您提供的开箱即用的功能，并且查询很快就会变得非常笨拙。通常，您需要在USER_META表中加入N 次，以便将 N 个属性作为列或为了在您的数据上有 N 个谓词。
On the data modeling front, future developers will generally be grateful if you avoid creating case-sensitive column names so that they don't have to double-quote every identifier every time.
在数据建模方面，如果您避免创建区分大小写的列名，以便他们不必每次都双引号每个标识符，那么未来的开发人员通常会很感激。
Since you need two keys, you should be able to do something like this (I assume that meta_valueis always stored as a character string even if it represents a numeric or boolean value)
由于您需要两个键，因此您应该能够执行以下操作（我假设它meta_value始终存储为字符串，即使它表示数字或布尔值）
SELECT usr."user_id",
usr."user_name",
meta."user_meta_id",
meta."meta_key",
meta."meta_value"
FROM users usr
JOIN user_meta meta ON (usr."user_id" = meta."user_id")
WHERE meta."meta_key" in ('active', 'car')
AND usr."user_id" IN (SELECT active."user_id"
FROM user_meta active
WHERE active."meta_key" = 'active'
AND active."meta_value" = '1' )
which returns the expected results for the data you posted
它返回您发布的数据的预期结果
SQL> ed
Wrote file afiedt.buf
1 with users as (
2 select 1 "user_id", 'tom' "user_name" from dual union all
3 select 2, 'sam' from dual
4 ),
5 user_meta as (
6 select 1 "user_meta_id",
7 1 "user_id",
8 'active' "meta_key",
9 '1' "meta_value" from dual union all
10 select 2, 1, 'car', 'dodge' from dual union all
11 select 3, 2, 'active', '0' from dual union all
12 select 4, 2, 'car', 'honda' from dual
13 )
14 SELECT usr."user_id",
15 usr."user_name",
16 meta."user_meta_id",
17 meta."meta_key",
18 meta."meta_value"
19 FROM users usr
20 JOIN user_meta meta ON (usr."user_id" = meta."user_id")
21 WHERE meta."meta_key" in ('active', 'car')
22 AND usr."user_id" IN (SELECT active."user_id"
23 FROM user_meta active
24 WHERE active."meta_key" = 'active'
25* AND active."meta_value" = '1' )
SQL> /
user_id use user_meta_id meta_k meta_
---------- --- ------------ ------ -----
1 tom 1 active 1
1 tom 2 car dodge
回答by Adriano Carneiro
SELECT * FROM USERS
LEFT JOIN USER_META ON
(USERS."user_id" = USER_META.”user_id”
AND (USER_META."meta_key" in ('active', 'car')
WHERE USERS."user_id" IN (select USER_META."user_id" from USER_META
where USER_META."meta_key" = 'active'
AND USER_META."meta_value" = 1
)
回答by Snipe656
This is off the top of my head since I do not have an Oracle instance to test with but something along these lines:
这是我的头绪，因为我没有要测试的 Oracle 实例，但有一些类似的东西：
SELECT T1.user_ID, T1.user_name, T2.user_meta_id, T2.meta_key, T2.meta_value
FROM USERS T1 JOIN USER_META T2 ON T1.user_id = T2.user_id
AND ((T2.meta_key = 'active' AND meta_value = 1) OR (T2.meta_key = 'car'))
回答by Galled
Try with this:
试试这个：
SELECT
um1.user_id, um1.user_name, um.user_meta_id,
um.meta_key, um.meta_value
FROM
USER_META um,
(
SELECT
um0.*, u0.user_name
FROM
USER_META um0, USERS u0
WHERE
u0.user_id = um0.user_id
and um0.meta_key = 'active'
and um0.meta_value= '1'
) um1
WHERE
um.meta_key in ('active','car')
and um.user_id = um1.user_id
