从表中仅选择具有空值/空值的列 [英] Select only columns with null/empty values from the table

问题描述

我有一个看起来像这样的数据集，但列以data4，data5到data20的形式继续显示

I have a data set that looks like this but columns goes on as data4, data5 up to data20

id      data1       data2       data3
(int)   (varchar)   (int)       (date)
-------------------------------------
1       xyz         (null)      0000-00-00
2       (empty)     321         2013-09-02
3       abc         555         2013-02-29
4       def         (null)      2013-09-02
5       lmn         678         2013-03-19

我只想选择包含具有空值或空值的列的行，以便客户可以决定需要在票证中填写哪些字段.

I only want to select the rows that has columns with null or empty values so the customer can decide which fields are needed to be filled in their tickets.

data1       data2       data3
(varchar)   (int)       (date)
-------------------------------------
2            1,4          1,5

或者如果有人能想到更好的变化?只要我正在做我想要的工作，我现在就可以接受任何东西.这有可能吗?谢谢！

Or if anyone could think of a better variation? I am open to anything at the moment as long as it is doing the work I want. Is this even possible? Thanks!

推荐答案

考虑到您有多达20个数据列，这可能不是一个好主意，但是这样可以吗?

This would probably be a bad idea considering you have up to 20 data columns, but would something like this work?

SELECT * FROM
    (SELECT GROUP_CONCAT(id) data1 FROM foo WHERE data1 IS NULL OR data1 = '') AS f1,
    (SELECT GROUP_CONCAT(id) data2 FROM foo WHERE data2 IS NULL OR data2 = '') AS f2,
    (SELECT GROUP_CONCAT(id) data3 FROM foo WHERE data3 IS NULL OR data3 = '') AS f3

这篇关于从表中仅选择具有空值/空值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！