多列上的 SELECT COUNT(DISTINCT...) 错误?

2021-09-10 00:00:00 sql tsql sql-server

我有一个表 VehicleModelYear，包含列 id、year、make 和 model.

I have a table, VehicleModelYear, containing columns id, year, make, and model.

以下两个查询按预期工作:

The following two queries work as expected:

SELECT DISTINCT make, model
FROM VehicleModelYear

SELECT COUNT(DISTINCT make)
FROM VehicleModelYear

但是，此查询不起作用

SELECT COUNT(DISTINCT make, model)
FROM VehicleModelYear

很明显答案是第一个查询返回的结果数量，但只是想知道这个语法有什么问题或者为什么它不起作用.

It's clear the answer is the number of results returned by the first query, but just wondering what is wrong with this syntax or why it doesn't work.

推荐答案

COUNT() 在 SQL Server 接受以下语法

COUNT(*)
COUNT(colName)
COUNT(DISTINCT colName)

你可以有一个子查询，它返回你可以计算的唯一的 make 和 model 集合.

You can have a subquery which returns unique set of make and model that you can count with.

SELECT  COUNT(*)
FROM
        (
            SELECT  DISTINCT make, model
            FROM    VehicleModelYear
        ) a

末尾的a"不是拼写错误.这是一个别名，如果没有它，SQL 将给出错误 ERROR 1248 (42000): 每个派生表必须有自己的别名.

The "a" at the end is not a typo. It's an alias without which SQL will give an error ERROR 1248 (42000): Every derived table must have its own alias.

相关文章