在 MySQL 表中查找 EMPTY 或 NULL 列的计数 [英] Find the count of EMPTY or NULL columns in a MySQL table

问题描述

我在一个 MySQL 表中有大约 30 列.我想计算特定行有多少列字段为空.该表用于存储用户信息.我想知道有多少个人资料字段(例如姓名"、年龄"、位置"——所有这些都存储在一个单独的列中)是空的/没有被用户填写.

I have around 30 columns in a MySQL table. I want to calculate how many column fields for a particular row are empty. This table is for storing user information. I want to find out how many profile fields (such as 'Name', 'Age', 'Location' - all of which are stored in a separate column) are empty/haven't been filled in by the user.

我想要细化的列是最后 20 列(因为前 10 列存储用户 ID、密码等内容 - 我只想要最后 20 列存储配置文件信息).如何找出此表中空/NULL 列的数量?

The columns I want to refine down are the final 20 columns (because the first 10 columns store stuff like User IDs, passwords, etc. - I only want the final 20 columns which store profile information). How do I find out the AMOUNT of empty/NULL columns in this table?

推荐答案

如果想得到这样的结果:

If the idea is to get a result something like this:

col       emp    
------    ------    
FName        15  
LName         2  
Age          22 

..使用:

SELECT 'FName' AS col, SUM(CASE FName IS NULL || FName='' THEN 1 ELSE 0 END) as emp FROM MyTable
UNION
SELECT 'LName' AS col, SUM(CASE LName IS NULL || LName='' THEN 1 ELSE 0 END) as emp FROM MyTable
UNION
SELECT 'Age' AS col, SUM(CASE Age IS NULL || Age='' THEN 1 ELSE 0 END) as emp FROM MyTable

...或:

SELECT SUM(CASE t.fname IS NULL OR t.fname = '' THEN 1 ELSE 0 END) AS fname_count,
       SUM(CASE t.lname IS NULL OR t.lname = '' THEN 1 ELSE 0 END) AS lname_count,
       SUM(CASE t.age IS NULL OR t.age = '' THEN 1 ELSE 0 END) AS age_count
  FROM MYTABLE t

这篇关于在 MySQL 表中查找 EMPTY 或 NULL 列的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！