求助：SQL Server中按动物名称统计各状态数量的查询实现

To get the exact output you're looking for—grouping animals by name and counting how many times each status (0 and 1) appears—conditional aggregation is the simplest and most efficient approach in SQL Server. Here's how to implement it:

The Query #

SELECT 
    name,
    SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS status0,
    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS status1
FROM 
    your_table_name -- Replace this with your actual table name
GROUP BY 
    name
ORDER BY 
    name; -- Optional, but sorts results alphabetically for readability

Breakdown of How It Works #

• GROUP BY name: Clusters all rows by the animal's name, so each result row represents one unique animal.
• SUM(CASE ...): For each row in the group, the CASE statement checks if the status matches the target value (0 or 1). If it does, it returns 1; otherwise 0. Summing these values gives the total count of that status for the animal.
• ORDER BY name: This is optional but helps organize the results neatly.

Testing with Your Sample Data #

When run against your provided sample data:

The query will output:

Just remember to swap your_table_name with the actual name of your table in the database!

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