select t.作者,tt.最多书数目,t.书名 as 最贵的书 from ( select 作者,书名,max(单价) as from 记录 group by 作者,书名 )t inner join ( select 作者,max(书数目) as 最多书数目 from ( select 作者,count(书名) as 书数目 from 记录 group by 作者 )t0 )tt on t.作者=tt.作者
select * from 记录 as a where 编号 in(select top 1 编号 from 记录 where a.作者=作者 order by 单价 desc) and 作者 in(select top 1 作者 from 记录 group by 作者 order by count(1) desc)
--> 生成测试数据: @T DECLARE @T TABLE (编号 VARCHAR(3),作者 VARCHAR(1),书名 VARCHAR(5),类型 VARCHAR(3),单价 INT) INSERT INTO @T SELECT 'A01','A','BOOK1','LX1',1 UNION ALL SELECT 'A02','A','BOOK2','LX1',3 UNION ALL SELECT 'A03','A','BOOK3','LX2',2 UNION ALL SELECT 'A04','A','BOOK4','LX2',4 UNION ALL SELECT 'A05','B','BOOK1','LX1',5 UNION ALL SELECT 'A06','B','BOOK2','LX2',6--SQL查询如下:SELECT * FROM @T AS T WHERE 作者=(SELECT TOP 1 作者 FROM @T GROUP BY 作者 ORDER BY COUNT(*) DESC) AND NOT EXISTS( SELECT * FROM @T WHERE T.作者=作者 AND 单价>T.单价 )/* 编号 作者 书名 类型 单价 ---- ---- ----- ---- ----------- A04 A BOOK4 LX2 4(1 行受影响)*/
create table #T (ID VARCHAR(3),Author VARCHAR(1),BookName VARCHAR(5),BookType VARCHAR(3),BookValue INT) INSERT INTO #T SELECT 'A01','A','BOOK1','LX1',1 UNION ALL SELECT 'A02','A','BOOK2','LX1',3 UNION ALL SELECT 'A03','A','BOOK3','LX2',2 UNION ALL SELECT 'A04','A','BOOK4','LX2',4 UNION ALL SELECT 'A05','B','BOOK1','LX1',5 UNION ALL SELECT 'A06','B','BOOK2','LX2',6select * from (select T.author,T.BookName,max(T.BookValue) vv from #T T join (select top 1 author,count(*) as cc from #T group by author order by cc desc)CC on CC.Author=T.author group by T.author,T.BookName) FF where not exists (select * from #T where FF.Author=Author and FF.vv<BookValue)
select top 1 s.* from @t s join ( select top 1 作者,总数=count(*) from @t t group by 作者 ) K on s.作者=k.作者 order by 单价 desc
AND NOT EXISTS( SELECT * FROM @T WHERE T.作者=作者 AND 单价>T.单价 这段代码是什么意思啊?
DECLARE @T TABLE (编号 VARCHAR(3),作者 VARCHAR(1),书名 VARCHAR(5),类型 VARCHAR(3),单价 INT) INSERT INTO @T SELECT 'A01','A','BOOK1','LX1',1 UNION ALL SELECT 'A02','A','BOOK2','LX1',3 UNION ALL SELECT 'A03','A','BOOK3','LX2',2 UNION ALL SELECT 'A04','A','BOOK4','LX2',4 UNION ALL SELECT 'A05','B','BOOK1','LX1',5 UNION ALL SELECT 'A06','B','BOOK2','LX2',6 select top 1 a.编号, a.作者,a.书名,a.类型 ,max(单价) as 单价 from @t as a, ( select top 1 作者,count(书名) as c from @t group by 作者 order by c desc) as b where a.作者 = b.作者 group by a.作者,a.书名,a.类型 ,a.编号 order by 单价 desc
select * from 记录 as a where
编号 in(select top 1 编号 from 记录 where a.作者=作者 order by 单价 desc)
and
作者 in(select top 1 作者 from 记录 group by 作者 order by count(1) desc)
-- Author: liangCK 小梁
-- Date : 2008-11-15 17:31:57
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (编号 VARCHAR(3),作者 VARCHAR(1),书名 VARCHAR(5),类型 VARCHAR(3),单价 INT)
INSERT INTO @T
SELECT 'A01','A','BOOK1','LX1',1 UNION ALL
SELECT 'A02','A','BOOK2','LX1',3 UNION ALL
SELECT 'A03','A','BOOK3','LX2',2 UNION ALL
SELECT 'A04','A','BOOK4','LX2',4 UNION ALL
SELECT 'A05','B','BOOK1','LX1',5 UNION ALL
SELECT 'A06','B','BOOK2','LX2',6--SQL查询如下:SELECT *
FROM @T AS T
WHERE 作者=(SELECT TOP 1 作者
FROM @T
GROUP BY 作者
ORDER BY COUNT(*) DESC)
AND NOT EXISTS(
SELECT *
FROM @T
WHERE T.作者=作者
AND 单价>T.单价
)/*
编号 作者 书名 类型 单价
---- ---- ----- ---- -----------
A04 A BOOK4 LX2 4(1 行受影响)*/
INSERT INTO #T
SELECT 'A01','A','BOOK1','LX1',1 UNION ALL
SELECT 'A02','A','BOOK2','LX1',3 UNION ALL
SELECT 'A03','A','BOOK3','LX2',2 UNION ALL
SELECT 'A04','A','BOOK4','LX2',4 UNION ALL
SELECT 'A05','B','BOOK1','LX1',5 UNION ALL
SELECT 'A06','B','BOOK2','LX2',6select * from (select T.author,T.BookName,max(T.BookValue) vv from #T T join
(select top 1 author,count(*) as cc from #T group by author order by cc desc)CC on CC.Author=T.author
group by T.author,T.BookName) FF where not exists (select * from #T where FF.Author=Author and FF.vv<BookValue)
select top 1 s.*
from @t s join ( select top 1 作者,总数=count(*) from @t t group by 作者 ) K
on s.作者=k.作者 order by 单价 desc
SELECT *
FROM @T
WHERE T.作者=作者
AND 单价>T.单价
这段代码是什么意思啊?
INSERT INTO @T
SELECT 'A01','A','BOOK1','LX1',1 UNION ALL
SELECT 'A02','A','BOOK2','LX1',3 UNION ALL
SELECT 'A03','A','BOOK3','LX2',2 UNION ALL
SELECT 'A04','A','BOOK4','LX2',4 UNION ALL
SELECT 'A05','B','BOOK1','LX1',5 UNION ALL
SELECT 'A06','B','BOOK2','LX2',6
select top 1 a.编号, a.作者,a.书名,a.类型 ,max(单价) as 单价
from @t as a, ( select top 1 作者,count(书名) as c
from @t
group by 作者
order by c desc) as b
where a.作者 = b.作者
group by a.作者,a.书名,a.类型 ,a.编号
order by 单价 desc