SQL跨列统计条目：固定表结构下6列非空文本计数实现

Alright, let's tackle this step by step. You've got a SQL table in a system where you can only add a new column (not rework the existing structure), and you need to tally up how many of the six text columns (data1 to data6) have non-null entries for each row, then save that total to a new totalcount column. Here's what to do:

1. First, add the totalcount column #

First, we need to create the column that'll hold our counts. The syntax is pretty consistent across databases—just swap in your actual table name:

ALTER TABLE your_table_name
ADD totalcount INT;

2. Calculate and update the non-null totals #

The core idea here is to check each column individually: if it's not null, we count it as 1, otherwise 0. Then we sum those values for each row and assign the sum to totalcount. The exact function varies a bit by database, so here are the most common versions:

For SQL Server / Azure SQL #

Use the IIF function to quickly evaluate each column:

UPDATE your_table_name
SET totalcount = 
    IIF(data1 IS NOT NULL, 1, 0) +
    IIF(data2 IS NOT NULL, 1, 0) +
    IIF(data3 IS NOT NULL, 1, 0) +
    IIF(data4 IS NOT NULL, 1, 0) +
    IIF(data5 IS NOT NULL, 1, 0) +
    IIF(data6 IS NOT NULL, 1, 0);

For MySQL / MariaDB #

MySQL uses IF instead of IIF—same logic, just different syntax:

UPDATE your_table_name
SET totalcount = 
    IF(data1 IS NOT NULL, 1, 0) +
    IF(data2 IS NOT NULL, 1, 0) +
    IF(data3 IS NOT NULL, 1, 0) +
    IF(data4 IS NOT NULL, 1, 0) +
    IF(data5 IS NOT NULL, 1, 0) +
    IF(data6 IS NOT NULL, 1, 0);

For Oracle #

Oracle relies on CASE statements here, since it doesn't support IIF or IF in this context:

UPDATE your_table_name
SET totalcount = 
    CASE WHEN data1 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN data2 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN data3 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN data4 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN data5 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN data6 IS NOT NULL THEN 1 ELSE 0 END;

Quick notes to keep in mind: #

• If your definition of "non-empty" includes excluding blank strings ('') along with NULL, adjust the checks. For example, in SQL Server, change a line to:

  IIF(data1 IS NOT NULL AND data1 != '', 1, 0)
  

• Double-check that you have the permissions to alter the table and run UPDATE statements in your system—some locked-down environments might restrict this.
• If you're working with a huge table, consider running the update in small batches to avoid locking up the table for other users.

内容的提问来源于stack exchange，提问作者Processit