SQL Server SQL查询聚合可能不显示

deikduxw  于 2023-02-11  发布在  其他
关注(0)|答案(2)|浏览(152)

我有这两个SQL表:

create table Kids (
    id  Int Identity (1,1) primary key not null
    , firstName varchar(100)
    , lastName  varchar(100)
    , birthDate  datetime
)

create table Toys (
    id Int Identity (1,1) primary key not null
    , kidId Int
    , toyName varchar(100)
    , colour varchar(100)
)

ALTER TABLE Toys
ADD FOREIGN KEY (kidId) REFERENCES Kids(id);

我正在尝试获得:所有拥有1个以上玩具的5岁以下儿童
我有这样的疑问:

select CONCAT(Kids.firstName,' ',Kids.lastName) as FullName,
birthDate,
count(Toys.Id) as 'Number of Toys'
from Kids
inner join Toys on Kids.id = Toys.kidId
where datediff(year, birthDate, getdate()) <= 5
and count(Toys.Id) > 1

但我得到的错误:
聚合不能出现在WHERE子句中,除非它位于HAVING子句或选择列表中包含的子查询中,并且要聚合的列是外部引用。
包含预期结果的列表。

h7appiyu

h7appiyu1#

select concat(Kids.firstName,' ',Kids.lastName) as FullName, birthDate
    , count(Toys.Id) as 'Number of Toys'
from Kids 
inner join Toys on Kids.id = Toys.kidId
where datediff(year, birthDate, getdate()) <= 5
group By Kids.firstName,Kids.lastName,birthDate
having count(Toys.Id) > 1
h7appiyu

h7appiyu2#

您的错误消息清楚地表明,您不能将聚合函数仅放在having中的where子句中。
您仍然需要GROUP BY,以便每个孩子都显示正确的计数,否则您只能返回q行

select concat(Kids.firstName,' ',Kids.lastName) as FullName,
    birthDate,
    count(Toys.Id) as 'Number of Toys'
from Kids
inner join Toys on Kids.id = Toys.kidId
where datediff(year, birthDate, getdate()) <= 5
group by concat(Kids.firstName,' ',Kids.lastName), birthDate  
having count(Toys.Id) > 1

| 全名|出生日期|玩具数量|
| - ------|- ------|- ------|
fiddle

相关问题