使用多列中的总计创建一个视图 [英] Create a view with totals from multiple columns

问题描述

我需要创建一个显示各种总数的视图。

我有两个表格。一个带有UserID和一个工作应用程序的状态代码。另一个包含UserID和4个包含布尔0/1的列。这些列中的每一列都表示一项工作技能（如跑步，跳跃，抛球，爬升）。该视图只需显示每项工作技能的总计，一次显示正在进行的应用程序，一次显示已完成的任务。我无法计算出每个技能的列并为其选择列。

用户表具有ID（int）和ApplicationStatus（varchar4）。
applicationStatus是：

• 0000如果尚未启动


• 0500正在处理


• 1000完成时。

JobSkills表具有：
ID，匹配的UserID以及4个JobSkill列：JobSkill_1，JobSkill_2，JobSkill_3和JobSkill_4，每个列定义为一个整数，如果用户拥有该技能，则包含1。

该视图应该显示以下内容的摘要：

• 有多少用户的状态为0000


• 有多少人的状态为0500


• 有多少人的状态为1000


• 有多少人的状态为1000，JobSkill_1 = 1


• 有多少人的状态为1000且JobSkill_2 = 1


• 有多少人的状态为1000且JobSkill_3 = 1


• 如何 许多人的地位为1000，JobSkill_4 = 1

类似于：

 [UserID]，
 SUM（JobSKill_1）as Run，
 SUM（JobSkill_2）AS Jump，
 SUM（JobSkill_3）AS Throw ，
 SUM（JobSkill_4）AS Climb 
 FROM [dbo]。[tblJobSkills] 
 GROUP BY [UserID] WITH ROLLUP 
  

解决方案

看起来您需要有条件的汇总才能达到您需要的结果。

在SQL Server集合函数中可以使用表达式作为参数（例如 SUM ）。

因此，可以在集合函数中使用CASE WHEN表达式
SUM（CASE WHEN a = b THEN c ELSE d END）

其中a，b，c，d可以是列，常量，函数或表达式（参见

上面的内容会导致CASE表达式针对每一行进行评估，结果作为SUM函数的输入传递。
$ b

注意：以上与CA​​SE不同时SUM（）= a THEN b ELSE c END在这种情况下，SUM函数首先处理所有输入行，并仅比较SUM函数的结果。

使用上述方法，它是可能会在不同于GROUP BY中指定的条件集合上进行聚合。

解决您的问题（假设User和JobSkills之间存在一对一关系）是如下：

pre $ $ $ $ c $> $ SUM $ CASE ApplicationStatus WHEN'0500'THEN 1 ELSE 0 END）作为InProgressCnt，
SUM（CASE ApplicationStatus WHEN'1000'THEN 1 ELSE 0 END）AS CompletedCnt，
SUM（CASE ApplicationSta tus WHEN'1000'Then JobSkill_1 ELSE 0 END）As JobSkill_1Count，
SUM（CASE ApplicationStatus WHEN'1000'Then JobSkill_2 ELSE 0 END）As JobSkill_2Count，
SUM（CASE ApplicationStatus WHEN'1000'Then JobSkill_3 ELSE 0 END）AS JobSkill_3Count，
SUM（CASE ApplicationStatus WHEN'1000'Then JobSkill_4 ELSE 0 END）AS JobSkill_4Count
FROM User AS
INNER JOIN JobSkills AS JS ON U.ID = JS。 UserID

I need to create a view that displays various totals.

I have two tables. One with UserIDs and a status code of a job application. The other has the UserID and 4 columns that contain Boolean 0/1. Each of those columns indicate a job skill (like run, jump, throw, climb). The view just needs to show the totals for each job skill, once for applications in progress, and once for those that are completed. I'm having trouble figuring out the group by and selecting columns for each skill.

The User table has ID(int) and ApplicationStatus(varchar4).
The applicationStatus is:

• 0000 if it hasn't been started
• 0500 when it is in progress
• 1000 when finished.

The JobSkills table has an: ID, a matching UserID for the usertable, and 4 JobSkill columns: JobSkill_1, JobSkill_2, JobSkill_3 and JobSkill_4, each defined as an integer and contain a 1 if the user has that skill.
The view is supposed to show a summary of:

• how many users have status of 0000
• how many have status of 0500
• how many have status of 1000
• how many have status of 1000 and JobSkill_1 =1
• how many have status of 1000 and JobSkill_2 =1
• how many have status of 1000 and JobSkill_3 =1
• and how many have status of 1000 and JobSkill_4 =1

Something like:

SELECT 
    [UserID],
    SUM(JobSKill_1) as Run,
    SUM(JobSkill_2) AS Jump,
    SUM(JobSkill_3) AS Throw, 
    SUM(JobSkill_4) AS Climb
FROM [dbo].[tblJobSkills]
GROUP BY [UserID] WITH ROLLUP

解决方案

It looks like you need conditional aggregation to achieve the results you need.

In SQL Server aggregate functions can take expression as the argument (for example SUM).

Therefore it is possible to have CASE WHEN expressions inside aggregate functions
SUM( CASE WHEN a = b THEN c ELSE d END )
where a, b, c, d can be columns, constants, functions or expressions (see CASE )

The above will cause CASE expression to be evaluated for each row and the result to be passed as input to SUM function.

Note: the above is different to CASE WHEN SUM() = a THEN b ELSE c END as in this case SUM function processes all input rows first and only results of the SUM function are compared.

Using the above approach it is possible to aggregate on a different set of conditions than what is specified in the GROUP BY.

The solution to your problem (assuming one to one relationship between User and JobSkills) is below:

SELECT SUM( CASE ApplicationStatus WHEN '0000' THEN 1 ELSE 0 END ) AS NotStartedCnt,
    SUM( CASE ApplicationStatus WHEN '0500' THEN 1 ELSE 0 END ) AS InProgressCnt,
    SUM( CASE ApplicationStatus WHEN '1000' THEN 1 ELSE 0 END ) AS CompletedCnt,
    SUM( CASE ApplicationStatus WHEN '1000' THEN JobSkill_1 ELSE 0 END ) AS JobSkill_1Count,
    SUM( CASE ApplicationStatus WHEN '1000' THEN JobSkill_2 ELSE 0 END ) AS JobSkill_2Count,
    SUM( CASE ApplicationStatus WHEN '1000' THEN JobSkill_3 ELSE 0 END ) AS JobSkill_3Count,
    SUM( CASE ApplicationStatus WHEN '1000' THEN JobSkill_4 ELSE 0 END ) AS JobSkill_4Count
FROM User AS U
    INNER JOIN JobSkills AS JS ON U.ID = JS.UserID

这篇关于使用多列中的总计创建一个视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！