针对特定问题的复杂MySQL查询

Question

我已经搜索并浏览了与我类似的现有主题。但没能满足我的要求。因此，张贴在这里。

我有四张表如下：

"Organization" table:
--------------------------------
| org_id       | org_name      |
| 1            | A             |
| 2            | B             |
| 3            | C             |

"Members" table:
----------------------------------------------
| mem_id       | mem_name     | org_id       |
| 1            | mem1         | 1            |
| 2            | mem2         | 1            |
| 3            | mem3         | 2            |
| 4            | mem4         | 3            |

"Resource" table:
--------------------------------
| res_id       | res_name      | res_prop    |
| 1            | resource1     | prop-1      |
| 2            | resource2     | prop-2      |
| 3            | resource3     | prop-3      |
| 4            | resource4     | prop-4      |
| 5            | resource1     | prop-5      |
| 6            | resource2     | prop-6      |

在上表中应用了唯一索引的约束(res_name，res_prop)。

"member-resource" table:
--------------------------------------------
| sl_no        | mem_id     | res_id       |
| 1            | 1          | 1            |
| 2            | 1          | 2            |
| 3            | 2          | 1            |
| 4            | 4          | 3            |
| 5            | 3          | 4            |
| 6            | 2          | 3            |
| 7            | 4          | 3            |
| 8            | 1          | 5            |
| 9            | 1          | 6            |

我想找出与特定组织有多个res_name的Resource表不同的res_prop。例如，组织A的预期输出如下：

| res_name       | res_prop_count       |
| resource1      | 2                    |
| resource2      | 2                    |

在这方面的任何帮助都将受到高度赞赏。

致以问候。

Answer 1

在这样做时，您应该逻辑地构建您的查询，以便从组织A开始获取您的所有资源和支持

SELECT  r.res_name,
        r.res_prop
FROM    Resource r
        INNER JOIN `member-resource` mr
            ON mr.res_id = r.res_id
        INNER JOIN Members m
            ON m.mem_id = mr.mem_id
        INNER JOIN Organization o
            ON o.org_id = m.org_id
WHERE   o.org_name = 'A'

然后，您可以开始考虑如何筛选它，因此您希望找到具有多个不同res_prop的资源名称，因此需要按res_name进行分组，并应用一个start子句将其限制为具有多个不同res_prop的res_names：

SELECT  r.res_name,
        COUNT(DISTINCT r.res_prop) AS res_prop_count
FROM    Resource r
        INNER JOIN `member-resource` mr
            ON mr.res_id = r.res_id
        INNER JOIN Members m
            ON m.mem_id = mr.mem_id
        INNER JOIN Organization o
            ON o.org_id = m.org_id
WHERE   o.org_name = 'A'
GROUP BY r.res_name
HAVING COUNT(DISTINCT r.res_prop) > 1;

SQL Fiddle示例

Answer 2

我不太清楚你在找什么，但我认为这应该是可行的：

SELECT          Resource.res_name,
                COUNT(DISTINCT Resource.res_prop) AS res_prop_count
FROM            Resource
INNER JOIN      member_resource
USING           (res_id)
INNER JOIN      Members
USING           (mem_id)
INNER JOIN      Organization
USING           (org_id)
WHERE           Organization.org_name = 'A'
GROUP BY        res_name
HAVING          res_prop_count > 1;

Answer 3

试试这个：

如果您有组织ID，那么使用下面的查询：

SELECT r.res_name, COUNT(DISTINCT r.res_prop) res_prop_count 
FROM member-resource mr 
INNER JOIN Resource r ON mr.res_id = r.res_id 
INNER JOIN Members m ON mr.mem_id = r.mem_id 
WHERE m.org_id = 1
GROUP BY r.res_id HAVING res_prop_count > 1;

如果您有组织名称，那么使用下面的查询：

SELECT r.res_name, COUNT(DISTINCT r.res_prop) res_prop_count 
FROM member-resource mr 
INNER JOIN Resource r ON mr.res_id = r.res_id 
INNER JOIN Members m ON mr.mem_id = r.mem_id 
INNER JOIN Organization o ON m.org_id = o.org_id
WHERE m.org_name = 'A'
GROUP BY r.res_id HAVING res_prop_count > 1