在mysql中使用多个子句操作行 [英] Manipulate rows using multiple clauses in mysql

问题描述

我有一张像这样的表:

样本

sample_id  | field    | value  | list_id
1            country    US       10
2            state      tx       10
3            country    US       11
4            state      tx       11
5            emp_size   100      11

我有一个使用 ff 代码检索 list_ids 10 和 11 的查询；

I have a query that retrieves list_ids 10 and 11 using the ff code;

select * from samples where (field='country' and value='US') OR (field='state' and value='tx')

但是后来我意识到这不是我想要的设置.假设我包含在我的子句 (field='emp_size' and value='100') 中，因为我只想获取 list_id 11，它仍然包含 list_id 10，因为我在查询中使用了 OR.现在我不确定是否有解决方法只使用普通的 mysql 或者我是否应该使用 php 操作数据.

However I realized later on that this is not the setup that I want. Let say I include in my clause (field='emp_size' and value='100') because I want to get list_id 11 only, it still includes list_id 10 because I use OR in my query. And right now I'm not sure if there's a workaround for this using plain mysql only or if I should just manipulate the data using php.

编辑

为了澄清，我想根据给定的参数获取 list_ids，比如我想要 US 和 TX，它应该返回 list_ids 10 和 11.但是如果我添加另一个参数，比如 emp_size，它应该只返回 list_id11.

For clarification, I want to get the list_ids based on the given parameters, say, I want US and TX, it should return list_ids 10 and 11. But if I add another parameter, say, emp_size, it should only return list_id 11.

推荐答案

您有一个 EAV 样式的数据结构，因此这里的最佳解决方案是为您正在搜索的每个参数/值组合自连接表.

You've got an EAV style data structure, so the best solution here is to self-join the table for each parameter/value combination that you are searching on.

SELECT s1.list_id
    FROM samples s1
        INNER JOIN samples s2
            ON s1.list_id = s2.list_id
                AND s2.field = 'state'
                AND s2.value = 'tx'
        INNER JOIN samples s3
            ON s1.list_id = s3.list_id
                AND s3.field = 'emp_size'
                AND s3.value = '100'
    WHERE s1.field = 'country'
        AND s1.value = 'US';

这篇关于在mysql中使用多个子句操作行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！