数据透视表省略具有空值的行 [英] Pivot Table Omitting Rows that Have Null values

问题描述

我正在解决与 this 非常相似的问题，我不对任何问题进行总结价值观.
我已经能够从此页面

I am solving a problem very similar to this only in my case, I am not summing any values.
I have been able to write a select that works using solution from this page

SELECT 
  id, 
  GROUP_CONCAT(if(colID = 1, value, NULL)) AS 'First Name',
  GROUP_CONCAT(if(colID = 2, value, NULL)) AS 'Last Name',
  GROUP_CONCAT(if(colID = 3, value, NULL)) AS 'Job Title'
FROM tbl
GROUP BY id;

但是，我想省略具有value为空的行

However, I want to omit rows that have the value to be null

推荐答案

如果源行的任何具有value IS NULL，我假设您要删除结果行. 您应该可以使用 <HAVING子句中的strong> bit_and() :

I assume you want to drop the result row if any of the source rows has value IS NULL.
You should be able to achieve that with bit_and() in the HAVING clause:

SELECT id
     , max(CASE WHEN colID = 1 THEN value END) AS fn
     , max(CASE WHEN colID = 2 THEN value END) AS ln
     , max(CASE WHEN colID = 3 THEN value END) AS jt
FROM   tbl 
GROUP  BY id
HAVING bit_and(value IS NOT NULL);

替代:

...
HAVING count(*) = count(value);

我没有在CASE语句中说明ELSE NULL，因为(

I didn't spell out ELSE NULL in the CASE statements because (per documentation):

如果没有匹配的结果值，则返回ELSE之后的结果；如果没有ELSE部分，则返回NULL.

If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.

这篇关于数据透视表省略具有空值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！