MYSQL查询帮助（EAV表）

发布于 2024-09-09 20:43:50 字数 435 浏览 12 评论 0原文

我有以下查询来检索对特定问题回答“是”“或”对另一个问题回答“否”的客户。

SELECT customers.id
FROM customers, responses
WHERE (
(
responses.question_id = 5
AND responses.value_enum = 'YES'
)
OR (
responses.question_id = 9
AND responses.value_enum = 'NO'
)
)
GROUP BY customers.id

效果很好。但是，我希望更改查询以检索对特定问题回答“是”“AND”对另一个问题回答“否”的客户。

关于如何实现这一目标有什么想法吗？

PS - 上表的响应采用 EAV 格式，即。行代表属性而不是列。

原文

I have the following query to retrieve customers who answer YES to a particular question "OR" NO to another question.

SELECT customers.id
FROM customers, responses
WHERE (
(
responses.question_id = 5
AND responses.value_enum = 'YES'
)
OR (
responses.question_id = 9
AND responses.value_enum = 'NO'
)
)
GROUP BY customers.id

Which works fine. However I wish to change the query to retrieve customers who answer YES to a particular question "AND" answer NO to another question.

Any ideas on how I can achieve this?

PS - The responses above table is in an EAV format ie. a row represents an attribute rather than a column.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

潦草背影 2024-09-16 20:43:50

我假设您的响应表中有一个名为 customer_id 的列。尝试将响应表连接到自身：

SELECT Q5.customer_id
FROM responses Q5
JOIN responses Q9 ON Q5.customer_id = Q9.customer_id AND Q9.question_id = 9
WHERE Q5.question_id = 5
AND Q5.value_enum = 'YES'
AND Q9.value_enum = 'NO'

I'm assuming that you have a column called customer_id in your responses table. Try joining the responses table to itself:

SELECT Q5.customer_id
FROM responses Q5
JOIN responses Q9 ON Q5.customer_id = Q9.customer_id AND Q9.question_id = 9
WHERE Q5.question_id = 5
AND Q5.value_enum = 'YES'
AND Q9.value_enum = 'NO'

回复收藏 0 原文

浅听莫相离 2024-09-16 20:43:50

大约是这样的：

SELECT distinct
  c.id
FROM 
  customers c
WHERE
  exists (select 1 from responses r where r.customer_id = c.id and r.response_id = 5 and r.value_enum = 'YES')
  and exists (select 1 from responses r2 where r2.customer_id = c.id and r2.response_id = 9 and r2.value_enum = 'NO')

我对缺少的 join 条件做了假设，修改为适合您的架构。

Approximately as such:

SELECT distinct
  c.id
FROM 
  customers c
WHERE
  exists (select 1 from responses r where r.customer_id = c.id and r.response_id = 5 and r.value_enum = 'YES')
  and exists (select 1 from responses r2 where r2.customer_id = c.id and r2.response_id = 9 and r2.value_enum = 'NO')

I made an assumption on the missing join condition, modify as correct for your schema.

回复收藏 0 原文

~没有更多了~