一些不错的sql语句(面试可能碰到哦！)
来源：互联网 发布：python str转化为字典 编辑：程序博客网 时间：2024/05/12 02:38
例子1) 一个表Table_1 有两个字段
id number，
name varchar2（7）
其中id是主键，name有重复记录
要求删除name字段重复的记录，保留其中id字段值最小的那条记录
如：
id name
1 test
2 test
3 test
4 test1
5 test1
6 test1
删完 了保留
1 test
4 test1
SQL: delete from Table_1 where id not in (select min(id) from Table_1 group by name)
例子2) 有三张表
1。student有两个字段 id name
2。course 也有两个字段 id cname
3 link 有三个字段 sid（关联student表的id 字段） cid（关联course表的id字段） score
要求查询出有两门以上不及格的学生的平均成绩
提示：先查询出至少2门不及格的学生
SQL: select SID, avg(score) from Link L where L.SID IN
( select sID from
(select sID, count(Sid) as num from Link WHERE Score<60 group by SID )A
where num>=2
) GROUP BY SID
例子3) 题目：现有两张表，table1和table2，两个表的内容及结构如下：
table1
pid product inputNum
1 A 20
3 C 10
5 E 30
table2
pid product outputNum
1 A 30
2 B 8
4 D 15
现在要查询得出如下结果：
pid product inputNum outputNum
1 A 20 30
2 B 0 8
3 C 10 0
4 D 0 15
5 E 30 0
请写出得到上述结果的SQL语句！
SQL: SELECT A.PID,A.PNAME,SUM(A.INPUTNUM)as INPUTNUM ,SUM(A.OUTPUTNUM) as OUTPUTNUM FROM
(SELECT P1.*,0 AS OUTPUTNUM FROM PRODUCT1 P1
UNION ALL
SELECT P2.PID,P2.PNAME, 0 AS INPUTNUM,P2.OUTPUTNUM FROM PRODUCT2 P2)A
GROUP BY A.PID,A.PNAME
例子4) 表如下:
SID(学号)
COURSEID(课程好)
SCORE(成绩)
要求:
用简单查询语句查找出1号课程成绩>2号课程成绩的学生,并列出学号、1号课成绩、2号课成绩
SQL1:
WITH A AS (select L.SID,L.COURSEID,L.SCORE AS SCORE1,0 as score2 from Link L where L.courseid =1),
B as (select L.SID,L.COURSEID,0 as score1,L.SCORE AS SCORE2 from Link L where L.courseid=2)
SELECT d.* FROM (
SELECT C.SID ,SUM(SCORE1) AS SCORE1 ,SUM(SCORE2) AS SCORE2 FROM (
select A.* FROM A
UNION
SELECT B.* FROM B)C
GROUP BY C.SID
)d WHERE d.score1>d.score2
SQL2:
select a.SID,a.SCORE as 一号成绩,b.SCORE as 二号成绩
from (select SID,SCORE from Link where courseid=1) a,
(select SID,SCORE from Link where courseid=2) b
where a.SID=b.SID and a.SCORE>b.SCORE
例子5)
A(a1,a2,a3),B(b1,b2,b3)两个表，要求将B.b2的值更新成A.a2的值 条件是A.a1=B.b1
SQL:
update B set B.b2=A.a2 from A where a.a1=B.b1
例子6)
A(a1,a2,a3,a4)表，写个sql列出表中记录，并添加一个序号字段，序号从1 开始递增
create table A(a1 int,a2 int,a3 int,a4 int)
insert into a select 1,3,2,7
insert into a select 2,3,4,9
insert into a select 6,4,7,13
insert into a select 9,7,18,23
insert into a select 3,3,5,1
SQL1:
SELECT ROW_NUMBER() OVER(ORDER BY a1 asc) as rowID,
rank() OVER(ORDER BY a1 asc) as x,
DENSE_RANK() OVER(ORDER BY a1 asc) as y,
NTILE(4) OVER(ORDER BY a1 asc) as z,
* FROM TABLE_A a ORDER BY a1 asc ;//这里a1可以替换为任何字段
SQL2)://提示：这里tmp_table1为临时表
select identity(int,1,1) as id,* into tmp_table1 from A;
select * from tmp_table1
结果：
tmp_table1
id a1 a2 a3 a4
1 1 3 2 7
2 2 3 4 9
3 6 4 7 13
4 9 7 18 23
5 3 3 5 1
例子7)
A(a1,a2,a3,a4)表内的数据为
a1 ,a2 ,a3 ,a4
1 3 2 7
2 3 4 9
6 4 7 13
9 7 18 23
3 3 5 1
要求最后输出的表排序按a2字段的值为3,3,3,7,4
SQL:
create table A(a1 int,a2 int,a3 int,a4 int)
insert into a select 1,3,2,7
insert into a select 2,3,4,9
insert into a select 6,4,7,13
insert into a select 9,7,18,23
insert into a select 3,3,5,1
select * from A order by (case when a2=9 then 4 else a2 end)
例子8):
A(a1,a2)表，写个sql填充a2的值，要求a2的值能被6整除，且与a1的差最小。
(如a1=7,则a2应为6不能为12或其它值)
SQL:
create table A(a1 int,a2 int)
insert into a(a1) select 7
insert into a(a1) select 9
insert into a(a1) select 13
insert into a(a1) select 23
insert into a(a1) select 3
update A set a2=a1/6*6
select * from A
go
drop table A
/*
a1 a2
----------- -----------
7 6
9 6
13 12
23 18
3 0
*/
例子9)：
数据库有3个表 teacher表 student表 teacher_student关系表
teacher表 teaID tname age
student表 stuID name sage
teacher_student表 teaID stuID
要求用一条sql查询出这样的结果:
1.显示的字段要有老师id tname age 每个老师所带的学生人数
2.只列出老师age为40以下 学生age为12以上的记录
SQL:
select a.teaid,b.tname,b.age as tage ,count(c.stuid) as stuNum from teadcher_students a
inner join teacher b on a.teaid=b.teaid and b.age<40
inner join students c on a.stuid=c.stuid and c.sage>12
group by a.teaid,b.tname,b.age
例子10):
查找第N条记录
SQL:
SELECT TOP 1 * FROM students WHERE STUID NOT IN (SELECT top N-1 STUID from students)
例子11):
随机查询一条记录
SQL：
select top 1 * from table1 order by newid()
例子12):
选取B表中没有而A表中有的数据
SQL:SELECT * FROM T_Teacher WHERE NOT EXISTS(SELECT * FROM T_Course WHERE T_Teacher.TeacherId=TeacherId)
优于SELECT * FROM T_Teacher WHERE TeacherId NOT IN (SELECT TeacherId FROM T_Course)
例子13：获取text字段的长度 替换Null 在text里面查找内容 从一个表选出数据插入另外一个表
datalength(字段名) isnull(字段名, '替换值') PATINDEX('值',字段名)>0 insert into 表1(col1,...)
select col1... from 表2 where 条件..
截取字段
select substring(name,1,5) as Name from Table1
例子14:)
一道SQL语句面试题，关于group by
TABLE1表内容：
DATEDAY RESULT
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
胜 负
2005-05-09 2 2
2005-05-10 1 2
SQL1: select dateday,sum(case when result='胜' then 1 else 0 end) as 胜,sum(case when result='败' then 1 else 0 end) as 败 from TABLE1 group by dateday
sql2: select N.dateday,N.胜,M.败 from (
select dateday,胜=count(*) from groupby where result='胜'group by dateday)N inner join
(select dateday,败=count(*) from groupby where result='败'group by dateday)M on N.dateday=M.dateday
SQL3: select a.dateday,a.a1 胜,b.b1 败 from
(select dateday,count(dateday) a1 from groupby where result='胜' group by dateday) a,
(select dateday,count(dateday) b1 from groupby where result='败' group by dateday) b
where a.dateday=b.dateday
例子15:)
表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
------------------------------------------
SQL: select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name
例子16:)
有1张表， Wages 表 请从上表用 “一句组合查询” 查询出工资统计表，要求检索出的内容格式如下：
------------------------------------------- -----------------------------------------------------------------
员工号码 | 基本工资 | 工龄工资 | 员工号码 | 基本工资 | 工龄工资 | 合计 | 名次
------------------------------------------- ------------------------------------------------------------------
1 | 1.00 | 1.00 | 1 | 1.00 | 1.00 |2.00 | x
------------------------------------------- ------------------------------------------------------------------
2 | 1.00 | 2.00 | 2 | 1.00 | 2.00 |3.00 | y
------------------------------------------- ------------------------------------------------------------------
3 | 1.00 | 3.00 | 3 | 1.00 | 3.00 |4.00 | ..
------------------------------------------- ------------------------------------------------------------------
4 | 1.00 | 4.00 | 4 | 1.00 | 4.00 |5.00 | ..
------------------------------------------- ------------------------------------------------------------------
SQL:
select e.*,ROW_NUMBER() OVER(ORDER BY e.合计 desc) as 名次 from (
select 员工号码,基本工资,工龄工资,(基本工资+工龄工资)as 合计 from Wages
) e
例子17:)
表名：购物信息
购物人 商品名称 数量
A 甲 2
B 乙 4
C 丙 1
A 丁 2
B 丙 5
……
（其他用户实验的记录大家可自行插入）
给出所有购入商品为两种或两种以上的购物人记录
SQL: 答：select * from 购物信息 where 购物人 in (select 购物人 from 购物信息 group by 购物人 having count(*) >= 2);
例子18:)
表名：成绩表
姓名 课程 分数
张三 语文 81
张三 数学 75
李四 语文 56
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 49
……
（其他用户实验的记录大家可自行插入）
给出成绩全部合格的学生信息（包含姓名、课程、分数），注：分数在60以上评为合格
SQL: 答：select * from 成绩表 where 姓名 not in (select distinct 姓名 from 成绩表 where 分数 < 60)
或者：
select * from 成绩表 where 姓名 in (select 姓名 from 成绩表 group by 姓名 having min(分数) >=60)
例子19:)
表名：team
ID(number型) Name(varchar2型)
1 a
2 b
3 b
4 a
5 c
6 c
要求:执行一个删除语句，当Name列上有相同时,只保留ID这列上值小的
例如：删除后的结果应如下:
ID(number型) Name(varchar2型)
1 a
2 b
5 c
请写出SQL语句。
SQL: from team where id not in (select min(id) from team group by name)
例子20:)
有三张表：
选手信息表(memberinfo)
字段：ID(自动编号), mnumber(报名号)，mname(姓名)
评审信息表(passinfo)
字段：Pnumber(评审编号),Pname(姓名) (注意：评审是由系统管理员指定的，有7人)
评审打分信息表(markinfo)
字段：mnumber(选手报名号)， detail_1(细节一的评审打分值)，detail_2(细节二...) detail_3(细节三...) detail_4(细节四...) detail_5(细节五...) passid(打分的评 审编号)
要求：
题目的背景大概就是一个关于评审网上给选手打分的，设计到的表上面已经写的很清楚了。题目的问题是：写一个SQL语句来返回一下字段：number(选手报名号)，name( 选手姓名)，mark(平均分) 要求是： mark(平均分)是7个评审打分值的平均，当且仅当7个评审全部打完分以后，mark字段才显示该选手的平均得分，否则，mark字段的值将显示“0”;
注释：round(b.record,3)为四舍五入函数，前面为操作的字段，后面一位数字表示保留几位小数
SQL1:)
select b.mnumber,round(case when b.record =7 then b.detail/7 else 0 end,3) mark ,mname from
(select mnumber,(sum(detail_1) +sum(detail_2) + sum(detail_2) +sum(detail_4)) detail,
count(mnumber)as record from markinfo group by mnumber)B
left join memberinfo m on b.mnumber=m.mnumber
SQL2:)
select b.mnumber,round(case when b.record =7 then b.avgmark else 0 end,3) mark ,mname from
(select mnumber,(avg(detail_1)+avg(detail_2) + avg(detail_2) +avg(detail_4)) avgmark,
count(mnumber)as record from markinfo group by mnumber)B
left join memberinfo m on b.mnumber=m.mnumber
- 一些不错的sql语句(面试可能碰到哦！)
- 一些不错的sql语句
- 一些不错的SQL语句
- 一些不错的sql语句
- 一些不错的sql语句
- 一些不错的sql语句
- 一些不错的sql语句
- 一些不错的SQL语句
- 一些不错的sql语句
- 一些不错的sql语句
- 大家来看一些不错的sql语句
- 碰到的一些面试问题
- 不错的SQL语句
- hibernate初学者可能碰到的一些问题
- hibernate初学者可能碰到的一些问题
- 参加面试碰到的一些面试题
- 不错的sql语句,收藏
- Torch代码书写时可能碰到的一些问题
- 第一章 C语言介绍
- keytool
- 网络工程师好找工作还是软件工程师好找工作
- CMOS 與 CCD
- strncasecmp
- 一些不错的sql语句(面试可能碰到哦！)
- 使用C#格式化字符串（转自博客园）
- 关于.c文件和.h文件
- strnicmp
- 今天收到PMI的邮件得知通过了PMP考试
- pthread_create()之前的属性设置
- JavaScript 根据两点的经纬度坐标，计算实际的直线距离
- linux pthread
- 关于Bit单位
