sql 删除重复记录语句收藏删除重复记录的题目。现给出解决此题的答案,如下:

sql 查询最高分、最低分和平均分语句//我们要用就以学生成绩为实例吧/*结构

在sql提供了ROW_NUMBER来做大数据量的操作,很多朋友不知道是ROW_NUMBER性能好还是存储过程的性能好,下面我们来看看测试实例。

select distinct * into Temp from Studenttruncate table Studentinsert
Student select * from Tempdrop table
Temp–至此删除了重复的数据。–显示删除后的结果select * from
student思路是先查询出没有重复的数据保存到一个临时的表Temp里面,然后再用truncate关键字删除目标表的数据内容,最后使用drop命令删除Temp表即可。

学生表Student(S#,Sname,Sage,Ssex) –S# 学生编号,Sname 学生姓名,Sage
出生年月,Ssex 学生性别–2.课程表 Course(C#,Cname,T#) –C#
–课程编号,Cname 课程名称,T# 教师编号

语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)


*/

简单的说row_number()从1开始,为每一条分组记录返回一个数字,这里的ROW_NUMBER()
OVER (ORDER BY xlh DESC)
是先把xlh列降序,再为降序以后的没条xlh记录返回一个序号。 示例: xlh
row_num 1700 1 1500 2 1085 3 710 4

斐波那契数列非递归的C#代码解法如下:用数学里面的通项

查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率–及格为=60,中等为:70-80,优良为:80-90,优秀为:=90–方法1select
m.C# [课程编号], m.Cname [课程名称], max(n.score) [最高分],
min(n.score) [最低分], cast(avg(n.score) as decimal(18,2)) [平均分],
cast((select count(1) from SC where C# = m.C# and score = 60)*100.0 /
(select count(1) from SC where C# = m.C#) as decimal(18,2))
[及格率(%)], cast((select count(1) from SC where C# = m.C# and score
= 70 and score 80 )*100.0 / (select count(1) from SC where C# = m.C#)
as decimal(18,2)) [中等率(%)], cast((select count(1) from SC where C#
= m.C# and score = 80 and score 90 )*100.0 / (select count(1) from SC
where C# = m.C#) as decimal(18,2)) [优良率(%)], cast((select
count(1) from SC where C# = m.C# and score = 90)*100.0 / (select
count(1) from SC where C# = m.C#) as decimal(18,2)) [优秀率(%)]from
Course m , SC nwhere m.C# = n.C#group by m.C# , m.Cnameorder by
m.C#–方法2select m.C# [课程编号], m.Cname [课程名称], (select
max(score) from SC where C# = m.C#澳门微尼斯人手机版,) [最高分], (select min(score)
from SC where C# = m.C#) [最低分], (select cast(avg(score) as
decimal(18,2)) from SC where C# = m.C#) [平均分], cast((select
count(1) from SC where C# = m.C# and score = 60)*100.0 / (select
count(1) from SC where C# = m.C#) as decimal(18,2)) [及格率(%)],
cast((select count(1) from SC where C# = m.C# and score = 70 and score
80 )*100.0 / (select count(1) from SC where C# = m.C#) as
decimal(18,2)) [中等率(%)], cast((select count(1) from SC where C# =
m.C# and score = 80 and score 90 )*100.0 / (select count(1) from SC
where C# = m.C#) as decimal(18,2)) [优良率(%)], cast((select
count(1) from SC where C# = m.C# and score = 90)*100.0 / (select
count(1) from SC where C# = m.C#) as decimal(18,2)) [优秀率(%)]from
Course m order by m.C#

row_number() OVER (PARTITION BY COL1 ORDER BY COL2)
表示根据COL1分组,在分组内部根据
COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)

static Int64 Fib(Int64 n){ int first, second, third; first = second =
third = 1; if (n==1||n==2) { return 1; } for (int i = 3; i = n; i++) {
first = second + third; third = second; second = first; } return
first;}Fibonacci序列:Fib(n) = Fib(n – 1) + Fib(n – 2), n1, Fib(1) =
Fib(2) = 1
即:序列的第一和第二项是1,从第三项开始,后一项是前两项的和。序列的前8项是:1,
1, 2, 3, 5, 8, 13, 21

实例:

更多更好方法

初始化数据

若想将姓名、身份证号、住址这三个字段完全相同的记录查询出来 select p1.*
from persons p1,persons p2 where p1.idp2.id and p1.cardid = p2.cardid
and p1.pname = p2.pname and p1.address = p2.address

create table employee (empid int ,deptid int ,salary
decimal(10,2))insert into employee values(1,10,5500.00)insert into
employee values(2,10,4500.00)insert into employee
values(3,20,1900.00)insert into employee values(4,20,4800.00)insert into
employee values(5,40,6500.00)insert into employee
values(6,40,14500.00)insert into employee values(7,40,44500.00)insert
into employee values(8,50,6500.00)insert into employee
values(9,50,7500.00)

可以实现上述效果.

数据显示为

几个删除重复记录的SQL语句1.用rowid方法

empid deptid salary———– ———–
—————————————1 10 5500.002 10 4500.003 20
1900.004 20 4800.005 40 6500.006 40 14500.007 40 44500.008 50 6500.009
50 7500.00

2.用group by方法

需求:根据部门分组,显示每个部门的工资等级

3.用distinct方法

预期结果:

发表评论

电子邮件地址不会被公开。 必填项已用*标注