MySQL - 仅当列有值时才选择列

问题描述

我需要构建一个 MySQL 查询，其中只有在给定的 4 列中具有值(NOT NULL)时才要求选择列，first_accuracy、second_accuracy、third_accuracy、fourth_accuracy.

假设我只有 first_accuracy、second_accuracy 的值，那么查询应该是

SELECT first_accuracy, second_accuracy FROM compliance_employees CE;

更新:

我申请的查询:

SELECT first_accuracy、second_accuracy、third_accuracy、fourth_accuracy FROM compliance_employees CE；

结果:

您可以看到，对于第三准确度"，任何行都没有值，因此我根本不想选择该列.这可能吗?

有什么解决办法吗?

解决方案

使用 'UNION' 、GROUP_CONCAT() 和 'not null' 我们可以找到具有值的列的计数.然后将列传递给 Prepared statment，它会给你你想要的结果.

 选择合并 (GROUP_CONCAT(col_name),'No_Result') 到@ColumnNames从 (选择'first_accuracy'作为col_name，count(distinct first_accuracy)作为来自compliance_employees的计数其中 first_accuracy 不为空联盟选择second_accuracy"作为col_name，从compliance_employees 中计数(不同的second_accuracy)其中 second_accuracy 不为空联盟选择third_accuracy"作为col_name，从compliance_employees 中计数(不同的third_accuracy)其中third_accuracy 不为空联盟选择fourth_accuracy"作为col_name，从compliance_employees 中计数(不同的fourth_accuracy)其中fourth_accuracy 不为空)a 其中计数 >0\\-- 选择@ColumnNames\\SET @sql := CONCAT('SELECT', @ColumnNames, 'FROM compliance_employees');set @sql := REPLACE(@sql,'No_Result','"No Column has values" as No_Result')\\从@sql 准备 stmt;执行 stmt;

在

I need to frame a MySQL query in which there is a requirement to pick columns only if it has value (being NOT NULL) out of the 4 columns given, first_accuracy, second_accuracy, third_accuracy, fourth_accuracy.

Suppose I have only values for first_accuracy, second_accuracy, So the query should be

UPDATE:

Query I applied:

SELECT first_accuracy, second_accuracy, third_accuracy, fourth_accuracy FROM compliance_employees CE;

Result:

You could see that for 'Third Accuracy' there is no value for any of the rows, So I don't want to select that column at all. Is that possible?

Any solution?

解决方案

Using 'UNION' ,GROUP_CONCAT() and 'not null' we find count of column which have values.then pass columns to Prepared statment which give you result whhich you want.

        select coalesce (GROUP_CONCAT(col_name),'No_Result') into @ColumnNames
        from (
        select 'first_accuracy' as col_name, count( distinct first_accuracy) as count from compliance_employees
        where first_accuracy  is not null
        union
        select 'second_accuracy' as col_name, count( distinct second_accuracy) from compliance_employees
        where second_accuracy  is not null
        union
        select 'third_accuracy' as col_name, count( distinct third_accuracy) from compliance_employees
        where third_accuracy  is not null
        union
        select 'fourth_accuracy' as col_name, count( distinct fourth_accuracy) from compliance_employees
        where fourth_accuracy is not null
        )a where count >0\\
        -- select @ColumnNames\\


    SET @sql := CONCAT('SELECT ', @ColumnNames, ' FROM compliance_employees');
    set @sql := REPLACE(@sql,'No_Result','"No Column have values" as No_Result')\\

     PREPARE stmt FROM @sql;
     EXECUTE stmt;

Check Live demo Here.

这篇关于MySQL - 仅当列有值时才选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！