SQL写法(累积)
1. Top n + group by <http://topic.csdn.net/u/20091013/11/077e5e03-24d2-4aa2-9771-4034f2c66084.html>
数据库里边有表
t(time, loction, index)
数据形式为
2009-01-01 01:00:00 1 10
2009-01-01 01:00:00 2 3
2009-01-01 01:00:00 3 1
2009-01-01 01:00:00 4 101
2009-01-01 01:00:00 5 102
2009-01-02 01:00:00 1 3
2009-01-02 01:00:00 2 3
2009-01-02 01:00:00 3 5
2009-01-02 01:00:00 4 10
2009-01-02 01:00:00 5 18我需要的结果是根据时间分组topn 例如我现在需要 时间分组top max 2的结果如下
2009-01-01 01:00:00 5 102
2009-01-01 01:00:00 4 101
2009-01-02 01:00:00 5 18
2009-01-02 01:00:00 4 10
请问各位大侠有什么sql可以达到这样的结果吗答：
select time, loction, index from(
select t.*,dense_rank()over(partition by time order by loction desc)dk
from t)
where dk <=2
用这个把如果是取最大的两条，如果是可以并列的，把row_number换成rank，注意他们的用户和区别
2. 求当前时间距离2010年1月1日还有多少分钟的SQL语句
select round(to_number(to_date('2010-01-01','yyyy-mm-dd')-sysdate)*1440) from dual
SELECT (TO_DATE('20100101','YYYYMMDD')-SYSDATE) * 24 * 60 FROM DUAL;
不过将to_number去掉更好，因为这样的话相当于先将to_date('2010-01-01','yyyy-mm-dd')-sysdate隐式转换为字符型，再转换到Number，这两步没有必要
3. 单行子查询返回多于一个行的问题 源文档 <http://topic.csdn.net/u/20091013/12/e29e0ef6-1458-4d00-ac86-016dbb63d96e.html?seed=64260288&r=60428742#r_60428742>
有个sc表，学生成绩表：
名称 类型
--------------------------------------------------------------
SID NUMBER 学生编号
CID NUMBER 课程编号
SCORE NUMBER 成绩
create table sc (sid number, cid number, score number);
insert into sc values(1, 1, 100);
insert into sc values( 1, 2, 90);
insert into sc values(1, 3, 76 );
insert into sc values( 2, 1, 30 );
insert into sc values(2, 2, 50 );
insert into sc values(2, 3, 70 );
insert into sc values(3, 1, 89.5 );
insert into sc values(3, 2, 89.5 );
insert into sc values(4, 1, 84.5 );现在我想求所有学生的 平均成绩及其名次，写了一个总报那个错，但我看不出怎么错了。请高手帮帮忙。
select s.sid,1+(
select count(score) from sc where sc.sid!=s.sid group by sc.sid
having avg(sc.score)<(select avg(sc.score) from sc where s.sid=sc.sid)
)
from sc s;
--一个人的回答
--用PLSQL函数
select sid,cid,
rank()over(partition by cid order by score desc)"排名",
avg(score)over(partition by cid)"本科平均成绩"
from sc--通用的SQL
select a.sid,a.cid,a.score,
(select avg(score) from sc where cid=a.cid) as avg_score,
(select count(1) from sc where cid=a.cid and score<a.score)+1 as rank
from sc a--答问题的人自己写的
select a.sid,a.avg_score ,1+(
select count(1) from (select sid,avg(score) avg_score from sc group by sid) b
where b.sid!=a.sid and b.avg_score<a.avg_score) rank
from (select sid,avg(score) avg_score from sc group by sid) a;--一个人的回答
SELECT 1+(SELECT COUNT( distinct avgs)
FROM (SELECT SID,AVG(score) AS avgs
FROM SC
GROUP BY SID
) T1
WHERE avgs > T2.avgs) as rank,
sid ,avgs
FROM (SELECT sid,AVG(score) avgs
FROM SC
GROUP BY sid
) T2
ORDER BY avgs desc;问题解决：就是升级到10g了，问题不再现了，终于ok了，没想到啊没想到。 9i的BUG！
4. 要求能够写出： 一个月内发过两次或者两次以上工资的员工的发工资次数，发工资总数，发工资月份，员工姓名和部门的sql语句
源文档<http://topic.csdn.net/u/20091019/20/19269e3f-0337-42f1-9d20-392f696fec16.html>
数据库表的结构如下：
< /blockquote>
create table workers(id int primary key,name varchar(12),department varchar(12),salary int,releaseDay date);
select name 姓名,department 部门,trunc(date,'mm')发工资月份,count(*) 发工资次数, sum(salary) 发工资总数
from workers
group by name ,department ,trunc(date,'mm')
having count(*)>=2
-- mysql仅取月份 to_char(releaseDay,'mm')5. 请问这个查询如何写？源文档 <http://topic.csdn.net/u/20091017/20/d7d29e91-c9a1-415b-86b6-1ddd432c4f73.html>
我有二个表
create table sm_user(name varchar2(100))
insert into sm_user(name) values('张三')
create table chinesename(tablename varchar2(50),tablefield varchar2(50),fieldchinese varchar2(50))
insert into chinesename (tablename,tablefield,fieldchinese) values('sm_user','name','名称')
要求查sm_user数据出来的效果是
标题 name(名称)
值 张三
也就是让sm_user的英文字段名附加上在chinesename中定义的中文字段名
答：6 请用SQL查找各个部门的男女人数分别是多少
Users表
name gender salesid
张三 女 1
李四 女 2
王五 男 3
六小龄 女 1
sales表
id name partid
1 售a 销售部
2 售b 销售部
3 售c 市场部
请用SQL查找各个部门的男女人数分别是多少，如以下信息：
Part 男 女
销售部 ?人数 ?人数
市场部 ?人数 ?人数
答：
SELECT S.PARTID,SUM(DECODE(U.GENDER,'男'，1，0)) “男”，SUM(DECODE(U.GENDER,'女'，1，0)) “女”
FROM USERS U,SALES SWHEREU.SALESID=S.IDGROUP BY S.PARTID;BODY { FONT-FAMILY:Consolas; FONT-SIZE:10pt } P { FONT-FAMILY:Consolas; FONT-SIZE:10pt } DIV { FONT-FAMILY:Consolas; FONT-SIZE:10pt } TD { FONT-FAMILY:Consolas; FONT-SIZE:10pt } 5：SQL Query
5.1: char(10)varchar(10)的差别:
1．CHAR的长度是固定的，而VARCHAR2的长度是可以变化的， 比如，存储字符串“abc"，对于CHAR (10)，表示你存储的字符将占10个字节(包括7个空字符)，而同样的VARCHAR2 (10)则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储。
2．CHAR的效率比VARCHAR2的效率稍高。
3．目前VARCHAR是VARCHAR2的同义词。工业标准的VARCHAR类型可以存储空字符串，但是oracle不这样做，尽管它保留以后 这样做的权利。Oracle自己开发了一个数据类型VARCHAR2，这个类型不是一个标准的VARCHAR，它将在数据库中varchar列可以存储空 字符串的特性改为存储NULL值。如果你想有向后兼容的能力，Oracle建议使用VARCHAR2而不是VARCHAR。
何时该用CHAR，何时该用varchar2？
CHAR与VARCHAR2是一对矛盾的统一体，两者是互补的关系.
VARCHAR2比CHAR节省空间，在效率上比CHAR会稍微差一些，即要想获得效率，就必须牺牲一定的空间，这也就是我们在数据库设计上常说的‘以空间换效率’。
VARCHAR2虽然比CHAR节省空间，但是如果一个VARCHAR2列经常被修改，而且每次被修改的数据的长度不同，这会引起‘行迁移 ’(Row Migration)现象，而这造成多余的I/O，是数据库设计和调整中要尽力避免的，在这种情况下用CHAR代替VARCHAR2会更好一些。
5.2: 存在表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示，请给出sql
create table t(a number(,b number(,c number(,d number();
/
begin
for i in 1 .. 300 loop
insert into t values(mod(i,2),i/2,dbms_random.value(1,300),i/4);
end loop;
end;
/
select * from (select c.*,rownum as rn from (select * from t order by c desc) c) where rn between 21 and 30;
/
select * from (select * from test order by c desc) x where rownum 〈 30
minus
select * from (select * from test order by c desc) y where rownum 〈20 order by 3 desc
相比之 minus性能较差
MINUS 指令是运用在两个 SQL 语句上。它先找出第一个 SQL 语句所产生的结果，然后看这些结果有没有在第二个 SQL 语句的结果中。如果有的话，那这一笔资料就被去除，而不会在最后的结果中出现。如果第二个 SQL 语句所产生的结果并没有存在于第一个 SQL 语句所产生的结果内，那这笔资料就被抛弃。
SELECT Date FROM Store_Information
MINUS
SELECT Date FROM Internet_Sales
结果:
Date
Jan-05-1999
Jan-08-1999
5.3: 为什么UNION ALL比UNION更快
你如果还记得，union操作是将两个数据集合并在一起。它不会产生 重复或者多余的行。为了完成这样的效果，需要对这两张表执行SORT操作。这个SORT操作明显是计算密集的，并且会使用大量的内存。相反，UNION ALL仅仅是将两个集合的数据任意的合并在一起，而不管行是否重复。
5.4: 在Oracle中，你怎么样才能返回查询的前N个结果?为什么明显的方法不行?
大多数人可能想到将ROWNUM伪列和ORDER BY一起使用。不幸的是，ROWNUM在ORDER BY之前就确定了，因此你无法得到你想要的结果。正确的答案是使用子查询先做ORDER BY。比如要返回工资最高的前5个雇员:
SELECT * FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM < 5;
5.5: talbe :
pub(pub_code char(2),pub_name varchar2(32))
book(book_code char(8),book_name varhcar2(128),pub_code char92),author_code
char(4),book_mem varchar2(128))
author(author_code char(4),author_name varchar2(10),sex char(1), age
number(3),dir varchar2(64))
auth_list(auth_date date,author_code char(4))
在PL/SQL中有以下两种字义
v_book_row book%rowtype;
v_book_code book.bank_code%type;
请说明v_book_row,v_book_code变量的含义以及数据类型
DECLARE
v_FirstName VARCHAR2(20);
DECLARE
v_FirstName students.first_name%TYPE;
#----
DECLARE
TYPE t_StudentRecord IS RECORD(
StudentID NUMBER(5),
FirstName VARCHAR2(20),
LastName VARCHAR2(20);
/*Declare a variable of this type.*/
v_StudentInfo t_StudentRecord;
SELECT studentID,firstName,lastName into v_StudentInfo from students where studentID=32;
DECLARE
v_RoomRecord rooms%ROWTYPE;
http://www.91duoduo.com/jishu/oracle_xx.html?noid=dklhi
5.6: 请看以下存储过程
begin
begin
<语句1>
exception
when others
dbms_output.put_line(’错误1′);
end;
begin
<语句2>
dbms_output.putline(’错误2′);
end;
exception
when others then
dbms_output.put_line(”错误3″);
end;
请问,当<语句1>,<语句2>出错时,该存储过程输出字符串分别是什么?
5.7: 找出“作者“库表中没有出现在“获奖名单“库表中的所有作者信息的SQL语句,(提示:使用NOT IN,NOT EXISTS,以及外部连接(+)三种方法),并说明那种方法是不优化的
出版社: 出版社代码 char(2),出版社名称 varchar2(32)
图书: 图书编号 char(8),图书名称 varchar2(128),出版社代码 char(2),作者代号
char(4),图书简介 varchar2(12
作者: 作者代号 char(4),作者名称 varchar2(10),性别 char(1),年龄 number(3),文学
方向 varchar2(64)
获奖名单: 获奖日期 date,获奖人员 char(4)
5.8: ORACLE数据库表METER中存在伪列Rowid,请利用ROWID来编写以下语句:从以下记录中找到唯一的任一条记录并更新该记录FLAG为“Y”.
MONTH USER_NO ASSST_NO FLAG
2004-7-1 1000009 10A0001 N
2004-7-1 1000009 10A0001 N
2004-7-1 1000009 10A0001 N
5.9: 改写函数为存储过程
create or replace function two_number(v_one number,v_two number)
return number is
begin
return v_one*v_two
end;
请将该函数改写为存储过程，注意传值参数如何设置
5.11: Group By
Store_Information 表格
store_name
Sales
Date
Los Angeles
$1500
Jan-05-1999
San Diego
$250
Jan-07-1999
Los Angeles
$300
Jan-08-1999
Boston
$700
Jan-08-1999
我们就打入，
SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name；
结果:
store_name
SUM(Sales)
Los Angeles
$1800
San Diego
$250
Boston
$700
另外一个例子：
SQL> SELECT JOB, COUNT(JOB) FROM EMP
2 GROUP BY JOB
3 ;
JOB COUNT(JOB)
--------- ----------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4
=================================================
