表:
create table score
(
sid int identity primary key,
sno varchar(3) not null,
cno varchar(5) not null,
degree numeric(10, 1) not null
)
go用sql查询:
查询score中选学一门以上课程的同学中分数并非为本门课程最高分成绩的记录。
create table score
(
sid int identity primary key,
sno varchar(3) not null,
cno varchar(5) not null,
degree numeric(10, 1) not null
)
go用sql查询:
查询score中选学一门以上课程的同学中分数并非为本门课程最高分成绩的记录。
解决方案 »
- 按品牌分组,每种品牌只显示三比数据
- 分离数据库后的mdf那去了?
- 如何返回子过程中得到的数据集?
- 如何在客户端实现SQL Sever数据导入Access数据呢?
- 请问,在XP frofessional上应该安装什么版本的SQLServer2000阿?我想自己测试用
- 如何在存储过程中执行储存在表中的SQL语句(带参数)?
- sql超难问题!!!在线等,立等可结!!!高手救命!!!!!!!!
- 如何 删除一个数据库里的每个表的数据呢?
- 为什么装完后服务器管理器只能练接本机的数据库,连其他库时提示“拒绝访问”?
- SQL分类汇总问题,各位请进
- 关于一个SQL排序问题
- |zyciis| 求简单SQL语句 在Where中做判断查询 谢谢
(
sid int identity primary key,
sno varchar(3) not null,
cno varchar(5) not null,
degree numeric(10, 1) not null
)
go
select * from score where degree not in (select max(degree) from score group by sno having count(cno) > 1) and sno in (select sno from score group by sno having count(cno) >1)
(select *,id=row_number() over(partition by cno order by degree desc) from score) t
,(select sno from score group by sno having count(cno)>=1) t1
where t.sno=t1.sno and id>1
FROM score s
WHERE 1=
(SELECT COUNT(*)
FROM score sc
WHERE s.cno=sc.cno AND sc.degree>=s.degree
GROUP BY sc.cno)
AND s.sno IN
(SELECT sno
FROM score
GROUP BY sno
HAVING count(*)>1)
(select *,id=row_number() over(partition by cno order by degree desc) from score) t
,(select sno from score group by sno having count(sno)>1) t1
where t.sno=t1.sno and id>1
SELECT *
FROM score s
WHERE 1<
(SELECT COUNT(*)
FROM score sc
WHERE s.cno=sc.cno AND sc.degree>=s.degree
GROUP BY sc.cno)
AND s.sno IN
(SELECT sno
FROM score
GROUP BY sno
HAVING count(*)>1)
create table score1
(
sid int identity primary key,
sno varchar(3) not null,
cno varchar(5) not null,
degree numeric(10, 1) not null
)
go
insert into score1 (sno,cno,degree)
select 001,001,80 union all
select 001,002,90 union all
select 001,003,100 union all
select 002,001,60 union all
select 002,002,95 union all
select 003,001,90 union all
select 004,004,70 union all
select 004,002,60select score1.* from score1
inner join (select cno,max(degree) as degree from score1 group by cno) aa
on aa.cno = score1.cno and score1.degree <> aa.degree
where sno in
(select sno from score1 group by sno having count(cno) > 1)sid sno cno degree
1 1 1 80.0
4 2 1 60.0
8 4 2 60.0
2 1 2 90.0
FROM score1 s
WHERE 1<
(SELECT COUNT(*)
FROM score1 sc
WHERE s.cno=sc.cno AND sc.degree>=s.degree
GROUP BY sc.cno)
这1代表什么意识
(select cno , max(degree) degree from score group by cno) n
where m.cno = n.cno and m.degree < n.degree and m.sno in
(select sno from score group by sno having count(1) > 1)select m.* from score m ,
(select cno , max(degree) degree from score group by cno) n,
(select sno from score group by sno having count(1) > 1) t
where m.cno = n.cno and m.degree < n.degree and m.sno = t.sno