第 n 个非空值的合并等效 - MySQL [英] Coalesce equivalent for nth not null value - MySQL

问题描述

我一直在为这个问题而烦恼.我正在处理现有数据集，需要从表 A 中的列中删除所有空值并将它们分流，以便它们按表 B 中的顺序排列

I have been tearing my hair out over this issue. I am working with an existing data set and need to remove all the null values from the columns in table A and shunt them across so they are ordered like in table B

我需要一些与 Coalesce 等效的东西，但要检索第 n 个值，这样我就可以得到像表 B 一样排序的结果

I need something which is equivalent to Coalesce but to retrieve the nth value so I can get the result sorted like in table B

我有什么:

表A

Name CURRENT OCT12 SEPT12 AUG12 JUL12 JUN12  MAY12 APR12
---------------------------------------------------------
A    NULL    NULL  Aug-12 NULL  NULL  Jun-12 NULL  Apr-12
B    Nov-12  NULL  Aug-12 NULL  Jul-12Jun-12 NULL  Apr-12

我需要什么:

表B

Name Change1 Change2 Change3 Change4 Change5 Change6
----------------------------------------------------
A    Aug-12  Jun-12  Apr-12  NULL    NULL    NULL   
B    Nov-12  Aug-12  Jul-12  Jun-12  Apr-12  NULL

代码方面，它会是这样的:

Code-wise, it would be something like:

Select

first non-null value as Change1  
,second non-null value as Change2  
,third non-null value as Change3  
,fourth non-null value as Change4  
,fifth non-null value as Change5...etc..  

from Table_A

我正在使用 MySQL，但我不知道如何引用第 n 个非空值以将它们调用到 Table_B 中

I am using MySQL and i have no idea how to reference the nth non null value in order to call them into Table_B

有人有什么想法吗?

推荐答案

我不确定我是否会推荐使用此解决方案...数据规范化始终是更好的选择，但我想使用普通 SQL 来回答一些字符串函数.此查询应返回您要查找的内容:

I am not sure if I would reccommend using this solution... normalization of your data is always a better choice, but I wanted to answer using plain SQL with some strings functions. This query should return what you are looking for:

SELECT
  Name,
  Changes,
  REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(Changes, ',', 1)), ',', 1)) as Change1,
  REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(Changes, ',', 2)), ',', 1)) as Change2,
  REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(Changes, ',', 3)), ',', 1)) as Change3,
  REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(Changes, ',', 4)), ',', 1)) as Change4,
  REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(Changes, ',', 5)), ',', 1)) as Change5,
  REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(Changes, ',', 6)), ',', 1)) as Change6
FROM (
  SELECT
    Name,
    CONCAT_WS(',', CURRENT, OCT12, SEPT12, AUG12, JUL12, JUN12, MAY12, APR12, ',') as Changes
  FROM
    TableA
) s

我将所有值连接在一个逗号分隔的字符串中，字符串末尾有两个逗号(一个逗号就足够了，但放置两个更容易，而忽略最后一个......)，并且因为我使用的是 CONCAT_WS，它会自动跳过空值，结果字符串将类似于 Aug-12,Jun-12,Apr-12,,.

I'm concatenating all values in a comma separated string, with two commas at the end of the string (one comma would be enough anyway, but it's easier to put two and just ignore the last one...), and since I'm using CONCAT_WS it will automatically skip null values, and the resulting string will be something like Aug-12,Jun-12,Apr-12,,.

然后在外部查询中，我使用 SUBSTRIG_INDEX 提取字符串的第 n 个元素.我建议对您的数据库进行规范化，但如果您需要快速修复，此解决方案可能是一个很好的起点.

Then in the outer query I'm extracting the n-th element of the string, using SUBSTRIG_INDEX. I would recommend to normalize your database, but if you need a quick fix this solution might be a good starting point.

在此处查看它是否有效.

请注意，我没有在没有更改的情况下返回 NULL 值，而是返回空字符串.如果需要，可以更改此设置.

Please notice that I am not returning NULL values where there are no changes, but I am returning empty strings instead. This can be changed if you need.

这篇关于第 n 个非空值的合并等效 - MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！