第 n 个非空值的合并等效

本文介绍了第 n 个非空值的合并等效 - 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.