Vertica DB多列1值组合校验查询需求咨询

安全阿岳

2026-5-15

Solution for Vertica Query: Rows with At Least Two 1s

Hey there! Let's break down how to get the IDs of rows that have at least two columns with a value of 1 in your Vertica table. Here are two straightforward approaches you can use:

Method 1: Using CASE Statements (Cross-SQL Compatible)

If you want a solution that works across most SQL databases (not just Vertica), use CASE to count the number of 1s per row:

SELECT id
FROM your_table_name
WHERE 
    (CASE WHEN col1 = 1 THEN 1 ELSE 0 END) +
    (CASE WHEN col2 = 1 THEN 1 ELSE 0 END) +
    (CASE WHEN col3 = 1 THEN 1 ELSE 0 END) +
    (CASE WHEN col4 = 1 THEN 1 ELSE 0 END) +
    (CASE WHEN col5 = 1 THEN 1 ELSE 0 END) >= 2;

How it works:

Each CASE statement converts a column value of 1 to 1, and any other value (0 or 2) to 0. Adding these results gives you the total number of 1s in the row. We filter rows where this total is 2 or more, then return their IDs.

Method 2: Using Vertica's COUNT_IF (More Concise)

Vertica has a handy built-in function COUNT_IF that lets you count how many of multiple conditions are true in a row. This makes the query much cleaner:

SELECT id
FROM your_table_name
WHERE COUNT_IF(col1 = 1, col2 = 1, col3 = 1, col4 = 1, col5 = 1) >= 2;

How it works:

COUNT_IF takes multiple boolean expressions (one for each column checking if it equals 1) and returns the number of expressions that evaluate to TRUE. We just need this count to be at least 2 to get our target rows.