SQL在添加值时从多个表中选择语句

I'm having a bit of trouble figuring out a good statement to write. I am able to achieve what I want when I query a specific 'Company' but I wanting to get the values for all of the companies in the database.

我在编写一个好的陈述时遇到了一些麻烦。当我查询特定的“公司”但我想获得数据库中所有公司的价值时,我能够实现我想要的。

Basically I have 3 tables: Users, Companies, Plans_ExchangeMailbox. What I need to do is query how many plans are in use for each company. The plans are assigned at the user level in the users table.

基本上我有3个表:Users,Companies,Plans_ExchangeMailbox。我需要做的是查询每个公司使用的计划数量。计划在用户表中的用户级别分配。

Here is my table layouts:

这是我的表格布局:

USERS

DisplayName

CompanyCode (This is the ID from the CompanyCode in the Companies table)

CompanyCode(这是Companies表中CompanyCode的ID)

MailboxPlan (This is the ID from the Plans_ExchangeMailbox Table)

MailboxPlan(这是Plans_ExchangeMailbox表中的ID)

Companies

CompanyName

CompanyCode

Plans_ExchangeMailbox

MailboxPlanName

MailboxPlanID

Here is the format I am looking to generate: CompanyName, MailboxPlanName, Count (this is the number of MailboxPlanID for a company)

以下是我要生成的格式:CompanyName,MailboxPlanName,Count(这是公司的MailboxPlanID数)

I was able to get this working but the problem is it can only do one company at a time and it doesn't get the CompanyName:

我能够使这个工作,但问题是它一次只能做一个公司,它不会得到CompanyName:

SELECT
Plans_ExchangeMailbox.MailboxPlanName,
SUM(CASE WHEN Users.MailboxPlan = Plans_ExchangeMailbox.MailboxPlanId THEN 1 ELSE 0      END) AS PlanCount
FROM
Plans_ExchangeMailbox, Users
WHERE
Users.CompanyCode='CC0'
GROUP BY 
Plans_ExchangeMailbox.MailboxPlanName

The Final Format How it Should Be:

最终格式应该如何:

Headers: CompanyName, PlanName, Count

标题:CompanyName,PlanName,Count

Values:

Microsoft, Bronze Plan, 5

微软,青铜计划,5

Microsoft, Gold Plan, 20

微软,黄金计划,20

Dell, Bronze Plan, 3

戴尔,青铜计划,3

Dell, Silver Plan, 80

戴尔,银色计划,80

etc.....

1 个解决方案

#1

Try this:

SELECT
     C.CompanyName,
     E.MailboxPlanName,
     COUNT(1) Cnt
FROM Companies C
    JOIN Users U
       ON C.CompanyCode = U.CompanyCode
    JOIN Plans_ExchangeMailbox E
       ON U.MailboxPlan = E.MailboxPlanID
GROUP BY 
     C.CompanyCode,
     C.CompanyName,
     E.MailboxPlanID,
     E.MailboxPlanName

Grouped by C.CompanyCode and E.MailboxPlanID in case if there are different companies or MailboxPlan with same name. If no,you can remove them from GROUP BY clause.

如果有不同的公司或具有相同名称的MailboxPlan,则由C.CompanyCode和E.MailboxPlanID分组。如果不是,您可以从GROUP BY子句中删除它们。