在SQL Server SELECT语句中使用CASE时消除NULL [英] Eliminating NULLs when using CASE in SQL Server SELECT statement

问题描述

我要编写一个大而混乱的报告，该报告跨5个表.在一个表中有一列用于几个不同的值-本质上是一个标签"列，其中，标签根据用户想要使用的其他各种元数据以创造性的方式使用.

I have a large, messy report to write that joins across 5 tables. There is one column in one table that is being used for several different values--essentially a "tag" column, where the tags are used in creative ways depending on what kind of miscellaneous metadata the users want to use.

结果，我对报表的查询返回了3个几乎完全相同的行，仅在标记"列中有所不同；例如，我可能会得到:

As a result, my query for the report returns 3 nearly-identical rows that differ only in the "tag" column; for instance, I might get:

NAME TAG EMAIL  
BOB  A   [e-mail]  
BOB  B   [e-mail]  
BOB  C   [e-mail]  

我想做的就是将TAG列的内容拆分为三个独立的列，从查询中返回，如下所示:

What I'd like to do is split the contents of the TAG column to be returned as 3 separate columns from the query, like this:

NAME A B C EMAIL
BOB  A B C [e-mail]

因此，我尝试使用SQL SERVER CASE/WHEN功能来执行此操作；我是说，例如，当Tag列的值为"A"时，将其返回到列"A"中；如果是"B"，则将其放在"B"中；等等.我以为这会返回上面的内容，但是却给了我这个:

So I tried using the SQL SERVER CASE/WHEN functionality to do this; I'm saying, for instance, when the value of the Tag column is "A", return it in column "A"; if it's "B," put it in "B"; etc. I thought this would return the above, but instead it gives me this:

NAME A    B    C    EMAIL
BOB  A    NULL NULL [e-mail]
BOB  NULL B    NULL [e-mail]
BOB  NULL NULL C    [e-mail]

显然不尽人意.

有什么想法，堆栈溢出天才吗?

Any thoughts, geniuses of Stack Overflow?

推荐答案

尝试一下:

   Select Name,
    Min (Case When tag = 'A' Then Tag End) A,
    Min (Case When tag = 'B' Then Tag End) B,
    Min (Case When tag = 'C' Then Tag End) C,
    email
   From tableName
   Group By Name, email

解释...
每当使用Group By时，您都在告诉查询处理器您希望它将结果聚合到存储桶"中，其中每个存储桶由Group By子句中定义的列[或表达式]中的唯一值定义.这意味着对于分组依据中定义的那些列[或表达式]中的每个唯一值集，最终结果集将只有一行，并且只有一行.查询中使用的所有其他列或表达式(分组依据"中定义的列或表达式除外) 必须是基于聚合函数的表达式 (例如Count()， Sum()，Avg()，Min()，Max()等)，它们会根据计算产生一个值，该值将应用于预汇总结果集中的所有行.例如，如果我要Group By姓氏的第一个字符:

to explain...
Anytime you use a Group By, you are telling the query processor that you want it to aggregate the results into "buckets", where each bucket is defined by unique values in the columns [or expressions] defined in the Group By clause. That means that the final result set will have one and only one row for each unique set of values in those columns [or expressions] defined in the Group By. All other columns or expressions used in the query, (other than those defined in the Group By), must be an expression based on an aggregation function (like Count(), Sum(), Avg(), Min(), Max(), etc...), that produce a value based on a calculation which will be applied to all the rows in the pre-aggregated result set. If, for example, I were to Group By the first character of the last name:

Select Left(LastName, 1), Count(*), 
   Sum(Salaray, Avg(Height), 
   Min(DateOfBirth), etc.
  From Table
  Group By Left(LastName, 1)

然后，我最多将在输出中获得26行(每个字母对应一个字母)，而我的输出中的所有其他列必须基于某种聚合函数，才能应用于原始集中的所有行，其中姓氏以'A'开头，然后姓氏以qa'B'开头的所有行，等等.

then I will get at most 26 rows in the output (one for each letter in the alphabet) and all other columns in my output must be based on some aggregation function to be applied to all the rows in the original set where the last name starts with an 'A", then all the rows where the last name starts with qa 'B' etc..

在您遇到的问题中，分组依据"仅用于将输出集限制为每个不同的用户和电子邮件只能排成一行.完成此操作后，每行将只有一行.Select语句中的其他列需要有一个Min()，[Max()也可以使用)，只是为了满足中提到的语法要求> 粗体斜体 .在您的情况下，集合中将只有一个非空行，因此仅出于以下原因才需要使用Min()或Max()语法要求...

In your problem, the Group By is used simply to restrict the output set to one row per distinct user and email. Once this is done, there will be only one row per The other columns in the Select statement need to have a Min(), [Max() would work just as well), only in order to satisfy the syntacical requirement mentioned in bold italics above.. In your case, there will only be one non-null row in the set so taking the Min(), or the Max() is necessary only because of the syntax requirement...

这篇关于在SQL Server SELECT语句中使用CASE时消除NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！