加入多对多关系

本文介绍了加入多对多关系的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有三个表:applications、permissions和applications_permissions

I have three tables: applications, permissions and applications_permissions

|------------| |------------------------| |-----------| |applications| |applications_permissions| |permissions| |------------| |------------------------| |-----------| | id | <-| application_id | | id | | price | | permission_id |-> | name | |------------| |------------------------| |-----------|

对于应用程序，有两类:免费和商业应用程序(价格 = '0' 和价格 != '0')

For applications there are two categories: free and commercial ones (price = '0' and price != '0')

现在我想知道每个权限有多少应用程序引用它；这两个类别

Now I would like to know for every permission how many percent of total applications reference it; And this for both category

免费:

id, percentage 1 , 20.0230 2 , 0.0000 3 , 0.0312 ...

商业:

id, percentage 1 , 18.0460 2 , 0.0000 3 , 0.0402 ...

我已经计算出以下查询，但它不包括没有应用程序的权限 ID:/

I have worked out the following query, but it does not include permission ids with no application :/

SELECT (SELECT name FROM permissions WHERE id = applications_permissions.permission_id) AS "name", 100::float * COUNT(*)/(SELECT COUNT(name) FROM applications WHERE price = \'0\') AS "percent" FROM applications, applications_permissions WHERE applications.id = applications_permissions.application_id AND applications.price = \'0\' GROUP BY applications_permissions.permission_id ORDER BY percent DESC')

我该怎么做?我已经尝试了几个小时(那个查询，misc JOINs)，但它让我望而却步:/

How do I do this? I've been trying for a few hours now (that query, misc JOINs) but it eludes me :/

推荐答案

简化.初稿非常理想.在一个查询中计算所有:

Simplified. First draft was sup-optimal. To compute all in one query:

SELECT p.id ,(100 * sum((a.price > 0)::int)) / cc.ct AS commercial ,(100 * sum((a.price = 0)::int)) / cf.ct AS free FROM (SELECT count(*)::float AS ct FROM applications WHERE price > 0) AS cc ,(SELECT count(*)::float AS ct FROM applications WHERE price = 0) AS cf ,permissions p LEFT JOIN applications_permissions ap ON ap.permission_id = p.id LEFT JOIN applications a ON a.id = ap.application_id GROUP BY 1, cc.ct, cf.ct ORDER BY 2 DESC, 3 DESC, 1;

假设您的价格实际上是一个数字列 - 所以 0 而不是 '0'.

Assuming that your price is actually a numeric column - so 0 instead of '0'.

这包括根本没有附加应用程序的权限(LEFT JOIN).

This includes permissions that have no attached applications at all (LEFT JOIN).

如果可以存在未附加到任何权限的应用程序，则列表相加不会达到 100%.

If there can be applications that aren't attached to any permissions the lists will not add up to 100 %.

我做一次总计数 (ct) 并在子查询中将其转换为 float.剩下的计算可以用整数算法来完成，只有最后的/ct将数字转换为浮点数.这是最快和最精确的.

I do the total count (ct) once and cast it to float in a subquery. The rest of the calculation can be done with integer arithmetic, only the final / ct converts the number to a floating point number. This is fastest and most precise.

如果您对更多新事物持开放态度:尝试使用 CTE(通用表表达式 - WITH 查询) - 自 PostgreSQL 8.4 起可用.它更干净，可能会稍微快一点，因为我在一个 CTE 中完成了两个计数并且有一个更便宜的 GROUP BY - 这两个都可以用子查询来完成:

If your are open to yet more new stuff: Try the same with CTEs (Common Table Expressions - WITH queries) - available since PostgreSQL 8.4. It's cleaner and probably slightly faster, because I do both counts in one CTE and have a cheaper GROUP BY - both of which could be done with subqueries just as well:

WITH c AS ( SELECT sum((a.price > 0)::int) AS cc ,sum((a.price = 0)::int) AS cf FROM applications ), p AS ( SELECT id ,sum((a.price > 0)::int) AS pc ,sum((a.price = 0)::int) AS pf FROM permissions p LEFT JOIN applications_permissions ap ON ap.permission_id = p.id LEFT JOIN applications a ON a.id = ap.application_id GROUP BY 1 ) SELECT p.id ,(100 * pc) / cc::float AS commercial ,(100 * pf) / cf::float AS free FROM c, p ORDER BY 2 DESC, 3 DESC, 1;