如何无需多个AND条件，实现多列统一WHERE过滤？以学生成绩查询为例

First off, let's cut to the chase: No, you can't use a shorthand like WHERE Student.* > 70 directly in standard SQL. The * wildcard is a shortcut to select all columns, but databases don't interpret it as "compare every single column to 70"—it doesn't work as a conditional wildcard for filtering.

That said, there are smarter ways to hit your goal (finding students with all subject scores above 70) without writing endless AND conditions. Let's break down practical solutions for your specific scenario:

Scenario: Find students with all subject scores >70 #

Assuming your Student table has columns like StudentID, Name, Maths, Physics, Chemistry, etc., here are your go-to options:

1. The straightforward (but verbose) AND approach #

If your list of subjects is small and rarely changes, this is the most readable choice—no fancy tricks required:

SELECT * 
FROM Student 
WHERE Maths > 70 
  AND Physics > 70 
  AND Chemistry > 70;

2. Use UNPIVOT (for SQL Server, Oracle, and others) #

This method turns your subject columns into rows, then checks if every score for a student meets the threshold. It's way cleaner if you add new subjects later (just update the IN clause):

SELECT s.StudentID, s.Name
FROM Student s
UNPIVOT (
  Score FOR Subject IN (Maths, Physics, Chemistry)
) AS unpivoted_scores
GROUP BY s.StudentID, s.Name
HAVING MIN(unpivoted_scores.Score) > 70;

By grouping by the student's unique ID/name, MIN(Score) confirms even their lowest subject score is above 70—meaning all scores are.

3. Dynamic SQL (for tables with frequently changing columns) #

If you need to handle new subjects without rewriting your query every time, dynamic SQL generates the AND conditions automatically. Here's an example for MySQL:

-- Grab all subject columns (adjust the WHERE clause to match your column naming rules)
SET @subject_columns = (
  SELECT GROUP_CONCAT(column_name SEPARATOR ' > 70 AND ') 
  FROM information_schema.columns 
  WHERE table_name = 'Student' 
    AND column_name IN ('Maths', 'Physics', 'Chemistry') -- Or use a pattern like `column_name LIKE '%Score'`
);

-- Build the full query string
SET @full_query = CONCAT('SELECT * FROM Student WHERE ', @subject_columns, ' > 70;');

-- Execute the dynamic query
PREPARE stmt FROM @full_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

⚠️ Heads up: Always validate column names when using dynamic SQL to avoid SQL injection risks.

4. CASE expression (database-agnostic) #

If you want a single condition without multiple ANDs, use a CASE statement to flag any student with a score ≤70, then filter those flagged records out:

SELECT *
FROM Student
WHERE CASE 
        WHEN Maths <= 70 THEN 1
        WHEN Physics <= 70 THEN 1
        WHEN Chemistry <= 70 THEN 1
        ELSE 0
      END = 0;

This works in all major databases and reads like a simple checklist—if none of the subjects fail the threshold, we keep the record.

Final Takeaway #

While there's no universal shorthand for "compare all columns to a value" in SQL, you can pick a method that fits your table's structure (fixed vs. dynamic columns) and readability needs. For most cases, UNPIVOT or dynamic SQL are the best alternatives to endless AND conditions.

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