select * from T as tmp where not exists(select 1 from T where 瀛︽牎=tmp.瀛︽牎 and 閲戦>=tmp.閲戦)
--try create table T(school nvarchar(20), class nvarchar(20), amt int) insert T select '瀛︽牎1', '鐝骇1', 500 union all select '瀛︽牎1', '鐝骇2', 600 union all select '瀛︽牎2', '鐝骇1', 700 union all select '瀛︽牎2', '鐝骇3', 800 union all select '瀛︽牎3', '鐝骇4', 500select top 2 * from T as tmp where not exists(select 1 from T where school=tmp.school and amt>tmp.amt) order by amt desc
create table t(school varchar(10),class varchar(10),[money] int) insert t select 'school1','class1',500 union all select 'school1','class2',600 union all select 'school2','class1',700 union all select 'school2','class3',800 union all select 'school3','class4',500select top 2 school,class,[money] from t a where [money]= ( select top 100 percent [money]=max([money]) from t where school=a.school order by [money] desc )drop table t/* school class money ---------- ---------- ----------- school1 class2 600 school2 class3 800 */
--luan ma,ba zhong wen zi duan gai cheng ying wen zi duan le.
select * from 表名 where 学校 in (select top 2 学校 from 表名 group by 学校 order by sum(金额) desc)
这个意思是吧?create table t(学校 varchar(10),班级 varchar(10),[money] int) insert t select '学校1','班级1',500 union all select '学校1','班级2',600 union all select '学校2','班级1',700 union all select '学校2','班级3',800 union all select '学校3','班级4',500select t.* from t, ( select top 2 学校 from ( select 学校,max([money]) as [money] from t group by 学校 ) T1 order by [money] DESC ) T2 where T.学校=t2.学校
declare @t table(学校 varchar(20), 班级 varchar(20), 金额 int) insert @t select '学校1', '班级1', 500 union all select '学校1', '班级2', 600 union all select '学校2', '班级1', 700 union all select '学校2', '班级3', 800 union all select '学校3', '班级4', 500select * from @t as a where not exists(select 1 from @t where 学校=a.学校 and 金额>a.金额)/*结果 学校 班级 金额 -------------------- -------------------- ----------- 学校1 班级2 600 学校2 班级3 800 学校3 班级4 500 */
借bill024(咖啡熊)数据一用create table t(school varchar(10),class varchar(10),[money] int) insert t select 'school1','class1',500 union all select 'school1','class2',600 union all select 'school2','class1',700 union all select 'school2','class3',800 union all select 'school3','class4',500 select top 2 * from ( select school,class,max(money)as money from t a where not exists(select 1 from t where school=a.school and t.money>a.money) group by school,class )a order by a.money descschool class money ---------- ---------- ----------- school2 class3 800 school1 class2 600(所影响的行数为 2 行)
CREATE TABLE Test(学校 nvarchar(20),班级 nvarchar(20), 金额 int) INSERT INTO Test SELECT '学校1' ,'班级1' ,500 UNION ALL SELECT '学校1' ,'班级2' ,600 UNION ALL SELECT '学校2' ,'班级1' ,700 UNION ALL SELECT '学校2' ,'班级3' ,800 UNION ALL SELECT '学校3' ,'班级4' ,500 SELECT * FROM Test AS A WHERE 学校 IN( SELECT TOP 2 学校 FROM Test AS A WHERE NOT EXISTS (SELECT 1 FROM Test AS B WHERE B.学校=A.学校 AND B.金额>A.金额) ORDER BY 金额 DESC ) --感觉题目的意思,有误解,下面的NOT EXISTS()不知道要好,还是不要好 呵呵 --AND NOT EXISTS (SELECT 1 FROM Test AS B WHERE B.学校=A.学校 AND B.金额>A.金额) DROP TABLE Test/* --不要第2个NOT EXISTS()学校1 班级1 500 学校1 班级2 600 学校2 班级1 700 学校2 班级3 800--要第2个NOT EXISTS()学校1 班级2 600 学校2 班级3 800 */
where not exists(select 1 from T where 瀛︽牎=tmp.瀛︽牎 and 閲戦>=tmp.閲戦)
create table T(school nvarchar(20), class nvarchar(20), amt int)
insert T select '瀛︽牎1', '鐝骇1', 500
union all select '瀛︽牎1', '鐝骇2', 600
union all select '瀛︽牎2', '鐝骇1', 700
union all select '瀛︽牎2', '鐝骇3', 800
union all select '瀛︽牎3', '鐝骇4', 500select top 2 * from T as tmp
where not exists(select 1 from T where school=tmp.school and amt>tmp.amt)
order by amt desc
insert t select 'school1','class1',500
union all select 'school1','class2',600
union all select 'school2','class1',700
union all select 'school2','class3',800
union all select 'school3','class4',500select top 2 school,class,[money] from t a where [money]=
(
select top 100 percent [money]=max([money]) from t where school=a.school
order by [money] desc
)drop table t/*
school class money
---------- ---------- -----------
school1 class2 600
school2 class3 800
*/
(select top 2 学校
from 表名
group by 学校
order by sum(金额) desc)
insert t select '学校1','班级1',500
union all select '学校1','班级2',600
union all select '学校2','班级1',700
union all select '学校2','班级3',800
union all select '学校3','班级4',500select t.*
from t,
( select top 2 学校
from
(
select 学校,max([money]) as [money]
from t
group by 学校
) T1
order by [money] DESC
) T2
where T.学校=t2.学校
insert @t
select '学校1', '班级1', 500 union all
select '学校1', '班级2', 600 union all
select '学校2', '班级1', 700 union all
select '学校2', '班级3', 800 union all
select '学校3', '班级4', 500select * from @t as a
where not exists(select 1 from @t where 学校=a.学校 and 金额>a.金额)/*结果
学校 班级 金额
-------------------- -------------------- -----------
学校1 班级2 600
学校2 班级3 800
学校3 班级4 500
*/
insert t select 'school1','class1',500
union all select 'school1','class2',600
union all select 'school2','class1',700
union all select 'school2','class3',800
union all select 'school3','class4',500
select top 2 * from
(
select school,class,max(money)as money from t a
where not exists(select 1 from t where school=a.school and t.money>a.money)
group by school,class
)a
order by a.money descschool class money
---------- ---------- -----------
school2 class3 800
school1 class2 600(所影响的行数为 2 行)
INSERT INTO Test
SELECT '学校1' ,'班级1' ,500 UNION ALL
SELECT '学校1' ,'班级2' ,600 UNION ALL
SELECT '学校2' ,'班级1' ,700 UNION ALL
SELECT '学校2' ,'班级3' ,800 UNION ALL
SELECT '学校3' ,'班级4' ,500
SELECT * FROM Test AS A WHERE 学校 IN(
SELECT TOP 2 学校 FROM Test AS A WHERE
NOT EXISTS (SELECT 1 FROM Test AS B WHERE B.学校=A.学校 AND B.金额>A.金额)
ORDER BY 金额 DESC
)
--感觉题目的意思,有误解,下面的NOT EXISTS()不知道要好,还是不要好 呵呵
--AND NOT EXISTS (SELECT 1 FROM Test AS B WHERE B.学校=A.学校 AND B.金额>A.金额)
DROP TABLE Test/*
--不要第2个NOT EXISTS()学校1 班级1 500
学校1 班级2 600
学校2 班级1 700
学校2 班级3 800--要第2个NOT EXISTS()学校1 班级2 600
学校2 班级3 800
*/