编写基于指定值90实现数据分区的SQL查询需求

Got it, let's work through this problem. You need to generate a Result column that categorizes each row based on whether the Status value meets or exceeds 90. Here's a complete, straightforward solution:

First, let's set up the temporary table and test data using your original statements:

-- Create temporary table
CREATE TABLE #temp(StudentID char(2), Status int)

-- Insert test data
INSERT #temp VALUES('S1',75 )
INSERT #temp VALUES('S1',85 )
INSERT #temp VALUES('S1',90)
INSERT #temp VALUES('S1',85)
INSERT #temp VALUES('S1',83)
INSERT #temp VALUES('S1',90 )
INSERT #temp VALUES('S1',85)
INSERT #temp VALUES('S1',90)
INSERT #temp VALUES('S1',93 )
INSERT #temp VALUES('S1',93 )
INSERT #temp VALUES('S1',93 )

Next, use a CASE statement to evaluate each Status against the 90 threshold and generate the Result column:

SELECT 
    StudentID AS ID,
    Status,
    CASE 
        WHEN Status < 90 THEN 0
        ELSE 1
    END AS Result
FROM #temp

Breakdown of the logic: #

• The CASE expression checks each row's Status value:
  • If Status is below 90, it returns 0
  • For any value 90 or higher, it returns 1
• The output will match your expected format, with each row showing the student ID, original Status, and the corresponding Result.

When you run this query, you'll get exactly the output you're looking for:

ID Status Result
S1 75     0
S1 85     0
S1 90     1
S1 85     0
S1 83     0
S1 90     1
S1 85     0
S1 90     1
S1 93     1
S1 93     1
S1 93     1

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