满足不同条件的不同值的max（）的count（） [英] count() corresponding to max() of different values satisfying some condition
问题描述
我有以下表格：
user_group
usergrp_id bigint主键
usergrp_name文本
用户
user_id bigint主键
用户名文本
user_usergrp_id bigint
user_loc_id bigint
user_usergrp_id 在user_group表
中具有其对应的ID user_loc_id 在分支中具有其对应的ID（branch_id）
分支
branch_id bigint主键
branch_name文本
branch_type smallint
branch_type 默认情况下设置为1。尽管它可能包含在1到4之间。
user_projects
proj_id bigint主键
proj_name文本
proj_branch_id smallint
proj_branch_id 具有分支表中的相应ID（branch_id）。
user_approval
appr_id bigint主键
appr_prjt_id bigint
appr_status smallint
appr_approval_by bigint
appr_approval_by 有用户表
appr_status 中对应的id（user_id）可能包含不同的状态值，例如10、20、30 ...对于单个 appr_prjt_id
user_group
<上课前= lang-none prettyprint-override>
usergrp_id | usergrp_name
-------------------------
1 |管理员
2 |经理
用户
user_id |用户名| user_usergrp_id | user_loc_id
------------------------------------------- --------
1 |约翰| 1 | 1
2 |哈里| 2 | 1
分支机构
branch_id | branch_name | branch_type
-------------------------------------
1 | location1 | 2
2 | location2 | 1
3 | location3 | 4
4 | location4 | 2
5 | location4 | 2
user_projects
proj_id | proj_name | proj_branch_id
------------------------------------
1 | test1 | 1
2 | test2 | 2
3 | test3 | 1
4 | test4 | 3
5 | test5 | 1
6 | test5 | 4
用户批准
appr_id | appr_prjt_id | appr_status | appr_approval_by
--------------------------------------------- ----------
1 | 1 | 10 | 1
2 | 1 | 20 | 1
3 | 1 | 30 | 1
4 | 2 | 10 | 2
5 | 3 | 10 | 1
6 | 3 | 20 | 2
7 | 4 | 10 | 1
8 | 4 | 20 | 1
条件：输出必须采用每个值并将其计数。 appr_prjt_id 的 appr_status 的MAX（）
即，在上表中 appr_prjt_id = 1 具有3个不同的状态：10、20、30。必须仅显示其计数对应于输出中30的状态（而不是状态10和20），对应于特定 branch_name 中的用户组。同样，对于 appr_prjt_id
所需的输出：
10 | 20 | 30
------>管理员0 | 1 | 1
|
location1
|
------>经理1 | 1 | 0
我该怎么做？
选择
branch_name，usergrp_name，
sum（（appr_status = 10）:: integer） 10，
sum（（appr_status = 20）:: integer） 20，
sum（（appr_status = 30）:: integer） 30
from
（
选择与（appr_prjt_id）
appr_prjt_id，appr_approval_by，来自user_approval
的appr_status
由1、3 desc
排序）ua
内部联接
用户u在ua.appr_approval_by = u.user_id
内部联接
user_group ug on u.user_usergrp_id = ug.usergrp_id
内部联接
u.user_loc_id上的分支b = branch_id
group by branch_name，usergrp_name
由usergrp_name
顺序$ 在大多数DBMS中有效的经典解决方案是使用案例：
选择
branch_name，usergrp_name，
sum（大小写为appr_status，当10然后1，否则0结束） 10，
但是Postgresql具有布尔类型，并且将其强制转换为整数（ boolean :: integer ）导致0或1，从而减少了冗长的代码。
在这种情况下，也可以进行计数而不是 sum ：
选择
branch_name，usergrp_name，
计数（appr_status = 10或为空） 10，
我确实更喜欢 count ，但是我有一个难以理解的印象。诀窍是要知道 count 计数不为空的任何东西，并且（<< c $ c> true 或null）为 true 和a（ false 或null）为null，因此只要条件为true就会计算在内。
I have the following tables:
user_group
usergrp_id bigint Primary Key
usergrp_name text
user
user_id bigint Primary Key
user_name text
user_usergrp_id bigint
user_loc_id bigint
user_usergrp_id has its corresponding id from the user_group table
user_loc_id has its corresponding id(branch_id) from the branch table.
branch
branch_id bigint Primary Key
branch_name text
branch_type smallint
branch_type By default is set as 1. Although it may contain any value in between 1 and 4.
user_projects
proj_id bigint Primary Key
proj_name text
proj_branch_id smallint
proj_branch_id has its corresponding id(branch_id) from the branch table.
user_approval
appr_id bigint Primary Key
appr_prjt_id bigint
appr_status smallint
appr_approval_by bigint
appr_approval_by has its corresponding id(user_id) from the user table
appr_status may contain different status values like 10,20,30... for a single appr_prjt_id
user_group
usergrp_id | usergrp_name
-------------------------
1 | Admin
2 | Manager
user
user_id | user_name | user_usergrp_id |user_loc_id
---------------------------------------------------
1 | John | 1 | 1
2 | Harry | 2 | 1
branch
branch_id | branch_name | branch_type
-------------------------------------
1 | location1 | 2
2 | location2 | 1
3 | location3 | 4
4 | location4 | 2
5 | location4 | 2
user_projects
proj_id | proj_name | proj_branch_id
------------------------------------
1 | test1 | 1
2 | test2 | 2
3 | test3 | 1
4 | test4 | 3
5 | test5 | 1
6 | test5 | 4
user_approval
appr_id | appr_prjt_id | appr_status | appr_approval_by
-------------------------------------------------------
1 | 1 | 10 | 1
2 | 1 | 20 | 1
3 | 1 | 30 | 1
4 | 2 | 10 | 2
5 | 3 | 10 | 1
6 | 3 | 20 | 2
7 | 4 | 10 | 1
8 | 4 | 20 | 1
Condition: The output must take the MAX() value of appr_status for each appr_prjt_id and count it.
I.e., in the above table appr_prjt_id=1 has 3 different status: 10, 20, 30. Its count must only be shown for status corresponding to 30 in the output (not in the statuses 10 and 20), corresponding to a user group in a particular branch_name. Similarly for each of the other id's in the field appr_prjt_id
Desired Output:
10 | 20 | 30
------> Admin 0 | 1 | 1
|
location1
|
------> Manager 1 | 1 | 0
How can I do that?
select
branch_name, usergrp_name,
sum((appr_status = 10)::integer) "10",
sum((appr_status = 20)::integer) "20",
sum((appr_status = 30)::integer) "30"
from
(
select distinct on (appr_prjt_id)
appr_prjt_id, appr_approval_by, appr_status
from user_approval
order by 1, 3 desc
) ua
inner join
users u on ua.appr_approval_by = u.user_id
inner join
user_group ug on u.user_usergrp_id = ug.usergrp_id
inner join
branch b on u.user_loc_id = b.branch_id
group by branch_name, usergrp_name
order by usergrp_name
The classic solution, that works in most DBMSs is to use a case:
select
branch_name, usergrp_name,
sum(case appr_status when 10 then 1 else 0 end) "10",
But Postgresql has the boolean type and it has a cast to integer (boolean::integer) resulting in 0 or 1 which makes for less verbose code.
In this case it is also possible to do a count in instead of a sum:
select
branch_name, usergrp_name,
count(appr_status = 10 or null) "10",
I indeed prefer the count but I have the impression that it is harder to understand. The trick is to know that count counts anything not null and that a (true or null) is true and a (false or null) is null so it will count whenever the condition is true.
这篇关于满足不同条件的不同值的max（）的count（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
