62,074
社区成员
发帖
与我相关
我的任务
分享
create table T_Book_Sale(
BookName nvarchar(50) null,
SaleNum int null,
Class nvarchar(10) null
)
insert into T_Book_Sale select N'红岩',16009,N'小说'
insert into T_Book_Sale select N'朝花夕拾',25410,N'散文集'
insert into T_Book_Sale select N'庐山文化评传',45410,N'散文集'
insert into T_Book_Sale select N'暴风骤雨',17069,N'小说'
insert into T_Book_Sale select N'糖果',45410,N'散文集'
select Class,max(SaleNum) SaleNum into #Result from T_Book_Sale group by Class
select a.* from T_Book_Sale a inner join #Result b
on a.Class=b.Class and a.SaleNum=b.SaleNum
drop table #Result
/*Execute in SQL Server 2000*/
create table T_Book_Sale(
BookName nvarchar(50) null,
SaleNum int null,
Class nvarchar(10) null
)
insert into T_Book_Saleselect N'红岩',16009,N'小说'
insert into T_Book_Saleselect N'朝花夕拾',25410,N'散文集'
insert into T_Book_Sale select N'庐山文化评传',45410,N'散文集'
insert into T_Book_Saleselect N'暴风骤雨',17069,N'小说'
insert into T_Book_Saleselect N'糖果',45410,N'散文集'
select Class,max(SaleNum) SaleNum into #Result from T_Book_Sale group by Class
select a.* from T_Book_Sale a inner join #Result b
on a.Class=b.Class and a.SaleNum=b.SaleNum
drop table #Result
select max(书名),max(销售量),类别 from Table group by 类别 order by 销售量 desc
create table BookData
(
BookName varchar(100),
SellNum int,
BookType varchar(100)
)
--测试数据
insert into BookData values('春哥自传',100,'自传')
insert into BookData values('我的奋斗',99,'自传')
insert into BookData values('一座城池',100,'小说')
insert into BookData values('他的国',99,'小说')
insert into BookData values('我的奋斗',399,'自传')
insert into BookData values('他的国',997,'小说')
insert into BookData values('我的奋斗',399,'自传2')
insert into BookData values('他的国',97,'小说2')
--
WITH a AS
(
SELECT MAX(SellNum) AS maxMun, BookType FROM dbo.BookData GROUP BY BookType
)
SELECT b.BookName,a.* FROM a INNER JOIN dbo.BookData AS b ON a.BookType = b.BookType AND a.maxMun=b.SellNum
create table BookData
(
BookName varchar(100),
SellNum int,
BookType varchar(100)
)
--测试数据
insert into BookData values('春哥自传',100,'自传')
insert into BookData values('我的奋斗',99,'自传')
insert into BookData values('一座城池',100,'小说')
insert into BookData values('他的国',99,'小说')
--查询语句
select * from BookData A
where BookName in
(
select top 1 BookName from BookData B
where A.BookType=B.BookType
)
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([书名] varchar(8),[销售量] int,[类别] varchar(4))
insert [TB]
select '春哥自传',100,'自传' union all
select '我的奋斗',99,'自传' union all
select '一座城池',100,'小说' union all
select '他的国',99,'小说'
select * from TB T
where not exists(select 1 from TB where [销售量]>t.[销售量] and [类别]=t.[类别])
/*
书名 销售量 类别
-------- ----------- ----
春哥自传 100 自传
一座城池 100 小说
(2 行受影响)
*/
drop table [TB]
select * from tb t
where 销售量=(top max(销售量) from tb where 类别= t.类别)