选择中的条件计数/总和 [英] Conditional Count/Sum in Select

问题描述

我一直在尝试不同的方法来解决这个问题，但是我没有太多的运气.

I've been trying differnt ways of approaching this, but I'm not having to much luck.

基本上，此查询返回员工内置的所有目录.我需要在此查询的选择中添加一个字段，该字段列出在目录中内置了多少个具有管理员"访问权限(u.access_level)的用户.

Basically this query returns all the directories an employee is built into. I need to add a field to the select of this query that lists how many other users with 'admin' access (u.access_level) are built into the directory).

Table:  Directories (has many users)
Table:  Users (users of the directory with different access levels)

SELECT d.id, d.name, u.employee_number, u.access_level, u.id 'user_id', 
(** count of all users of this directory where their u.access_level = 'admin) AS admins 
FROM directories d JOIN users u ON d.id = u.directory_id 
WHERE u.employee_number = '045283' 
ORDER BY d.NAME 

因此，我需要返回一个雇员内置的所有目录的记录集，包括对每个目录具有管理员"访问权限的用户总数.基本上，我需要考虑一些以后的框架逻辑，例如如果雇员是该目录的最后一个管理员-不允许他们删除自己或更改其访问级别-首先将另一个用户升级为管理员级别.

So, I need to return a recordset of all directories an employee is built into, including the total number of users that have 'admin' access to each directory. Basically I need a count for some later framework logic like 'if the employee is the last admin of this directory - don't allow them to delete themselves or change their access level - upgrade another user to admin level first).

感觉像我需要一个子查询，但是这超出了我现在的SQL技能.谢谢你的帮助！

It feels like I'm needing a sub-query, but that's a little beyond my SQL skills right now. Thanks for any help!

推荐答案

Select d.id, d.name, u.employee_number, u.access_level, u.id 'user_id'
    , (Select Count(*)
        From directories As D1
            Join users As U1
                On U1.directory_id = D1.id
        Where U1.access_level = 'admin'
            And D1.id = D.id) As AdminUsers
From directories As D
    Join users As U
        On U.directory_id = D.id
Where u.employee_number = '045283'
Order By d.Name

使用派生表的另一种解决方案(可能效果更好):

Another solution using a derived table (which might perform better):

Select d.id, d.name, u.employee_number, u.access_level, u.id 'user_id'
    , Coalesce(AdminDir.AdminCount,0) As AdminCount
From directories As D
    Join users As U
        On U.directory_id = D.id
    Left Join   (
                Select D1.id, Count(*) As AdminCount
                From directories As D1
                    Join users As U1
                        On U1.directory_id = D1.id
                Where U1.access_level = 'admin'
                Group By D1.id  
                ) As AdminDir
            On AdminDir.id = D.id
Where u.employee_number = '045283'
Order By d.Name

这篇关于选择中的条件计数/总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！