sql group by

最新推荐文章于 2023-08-30 13:42:20 发布

原创最新推荐文章于 2023-08-30 13:42:20 发布 · 889 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #group by

sql server 专栏收录该内容

77 篇文章

订阅专栏

本文介绍了一个SQL技巧，通过特定查询来获取图书表中最新年份的数据，并确保能够正确展示每本书最新的库存数量。该方法避免了仅使用聚合函数而导致部分数据丢失的问题。

create table Book
(
	BookID int primary key identity(1,1),
	BookNO nvarchar(50),
	BookName nvarchar(50),
	[Year] int,
	Number int
)

insert into Book(BookNO,BookName,[Year],Number) values('NO001','指环王1',2012,1000);
insert into Book(BookNO,BookName,[Year],Number) values('NO001','指环王1',2014,2000);
insert into Book(BookNO,BookName,[Year],Number) values('NO002','指环王2',2013,1000);
insert into Book(BookNO,BookName,[Year],Number) values('NO002','指环王2',2014,2000);
insert into Book(BookNO,BookName,[Year],Number) values('NO003','指环王3',2015,2000);

select * from Book;

--此方法不能获取Number数据
select BookNO,BookName,max([Year])as [Year] from Book 
group by BookNO,BookName

--此方法能显示Number数据
select * from Book b
where [Year]=(select max([Year]) from Book where BookNO=b.BookNO)
order by BookNO asc;