最新评论
崩溃 2010-07-07 16:45
我去其他公司面试也出过个类似的题目
我记得当时是这样写的：
select 购物人,count(产品) from 表
group by 购物人
having count(产品) > 2
==================================
看错了，嘿嘿！ 和你是一个道理！
天涯觅箫声 2010-04-19 10:50
加入 distinct 关键字便可以解决，如下：
select name as 购物人, count(distinct type) as 种类数
from table
group by name having count(distinct type) >= 2;
疯狂的毛豆 2010-01-05 02:13
第7题的第一个答案个人认为不对，应该用外连接才是：
在oracle的HR用户下多建一张表
create table invoice
(iid number,idate varchar(20),invoice_number varchar(4));
insert into invoice
values
(1,'2008-12-1','001');
insert into invoice
values
(2,'2008-12-2','001');
insert into invoice
values
(3,'2008-11-1','003');
insert into invoice
values
(4,'2008-11-6','003');
select * from invoice
select * from invoice where exists( select * from (select min(idate) minidate,invoice_number from invoice group by invoice_number) minvoice where minvoice.invoice_number=invoice.invoice_number and minvoice.minidate=invoice.idate)
select * from invoice where (invoice.idate ,invoice.invoice_number) in (select min(idate) minidate,invoice_number from invoice group by invoice_number)
select * from invoice invoice1 where invoice1.idate = (select min(idate) minidate from invoice where invoice_number=invoice1.invoice_number)
select * from invoice where not exists( select invoice_number from invoice minvoice where minvoice.invoice_number=invoice.invoice_number and minvoice.idate<invoice.idate)
select * from departments
create table departments_records
(year_month varchar(20),department_id number,rand number);
select * from departments_records
insert into departments_records
values
('2009-01',10,20);
insert into departments_records
values
('2009-02',10,10);
insert into departments_records
values
('2009-03',10,40);
insert into departments_records
values
('2009-01',20,10);
insert into departments_records
values
('2009-02',30,20);
insert into departments_records
values
('2009-03',40,30);
解答：
select ds.department_id,dr1.rand as "2009-01",dr2.rand as "2009-02",dr3.rand as "2009-03" ,dr4.rand as "2009-04" from departments ds
left outer join departments_records dr1 on ds.department_id=dr1.department_id and dr1.year_month='2009-01'
left outer join departments_records dr2 on ds.department_id=dr2.department_id and dr2.year_month='2009-02'
left outer join departments_records dr3 on ds.department_id=dr3.department_id and dr3.year_month='2009-03'
left outer join departments_records dr4 on ds.department_id=dr2.department_id and dr4.year_month='2009-04'
order by ds.department_id
疯狂的毛豆 2010-01-05 02:13
第7题的第一个答案个人认为不对，应该用外连接才是：
在oracle的HR用户下多建一张表
create table invoice
(iid number,idate varchar(20),invoice_number varchar(4));
insert into invoice
values
(1,'2008-12-1','001');
insert into invoice
values
(2,'2008-12-2','001');
insert into invoice
values
(3,'2008-11-1','003');
insert into invoice
values
(4,'2008-11-6','003');
select * from invoice
select * from invoice where exists( select * from (select min(idate) minidate,invoice_number from invoice group by invoice_number) minvoice where minvoice.invoice_number=invoice.invoice_number and minvoice.minidate=invoice.idate)
select * from invoice where (invoice.idate ,invoice.invoice_number) in (select min(idate) minidate,invoice_number from invoice group by invoice_number)
select * from invoice invoice1 where invoice1.idate = (select min(idate) minidate from invoice where invoice_number=invoice1.invoice_number)
select * from invoice where not exists( select invoice_number from invoice minvoice where minvoice.invoice_number=invoice.invoice_number and minvoice.idate<invoice.idate)
select * from departments
create table departments_records
(year_month varchar(20),department_id number,rand number);
select * from departments_records
insert into departments_records
values
('2009-01',10,20);
insert into departments_records
values
('2009-02',10,10);
insert into departments_records
values
('2009-03',10,40);
insert into departments_records
values
('2009-01',20,10);
insert into departments_records
values
('2009-02',30,20);
insert into departments_records
values
('2009-03',40,30);
解答：
select ds.department_id,dr1.rand as "2009-01",dr2.rand as "2009-02",dr3.rand as "2009-03" ,dr4.rand as "2009-04" from departments ds
left outer join departments_records dr1 on ds.department_id=dr1.department_id and dr1.year_month='2009-01'
left outer join departments_records dr2 on ds.department_id=dr2.department_id and dr2.year_month='2009-02'
left outer join departments_records dr3 on ds.department_id=dr3.department_id and dr3.year_month='2009-03'
left outer join departments_records dr4 on ds.department_id=dr2.department_id and dr4.year_month='2009-04'
order by ds.department_id
疯狂的毛豆 2010-01-04 11:28
第一题楼主的后两种解法不全面，如果某日只有胜，或只有负，将会漏掉该日期的赛果。
oracle的解法
create table match_records
(match_date date,match_result varchar(20));
insert into match_records
values
(to_date('2005-05-09','YYYY-MM-DD'),'胜');
insert into match_records
values
(to_date('2005-05-09','YYYY-MM-DD'),'胜');
insert into match_records
values
(to_date('2005-05-09','YYYY-MM-DD'),'负');
insert into match_records
values
(to_date('2005-05-09','YYYY-MM-DD'),'负');
insert into match_records
values
(to_date('2005-05-10','YYYY-MM-DD'),'胜');
insert into match_records
values
(to_date('2005-05-10','YYYY-MM-DD'),'胜');
insert into match_records
values
(to_date('2005-05-10','YYYY-MM-DD'),'负');
insert into match_records
values
(to_date('2005-05-11','YYYY-MM-DD'),'负');
insert into match_records
values
(to_date('2005-05-12','YYYY-MM-DD'),'胜');
select * from match_records
解法一：
select match_date ,sum(win) as "胜" ,sum(lost) as "负" from (select md.match_date, decode(md.match_result,'胜',1,0 ) win,decode(md.match_result,'负',1,0 ) lost from match_records md) group by match_date
解法二：
(select md.match_date, sum(decode(md.match_result,'胜',1,0 )) win,sum(decode(md.match_result,'负',1,0 )) lost from match_records md group by md.match_date)
解法三：
select distinct mr.match_date,nvl(mrwin.win,0) as win , nvl(mrlost.lost,0) as lost from match_records mr left outer join ( select count(1) win ,md.match_date from match_records md where md.match_result='胜' group by md.match_date) mrwin
on mr.match_date=mrwin.match_date
left outer join
(select count(1) lost ,md.match_date from match_records md where md.match_result='负' group by md.match_date) mrlost
on mrlost.match_date=mr.match_date
疯狂的毛豆 2010-01-04 07:33
Oracle的解法 取第5到第8条数据
create table test6 (tid number(6),tname varchar2(20));
insert into test6
(tid,tname)values(1,'guo');
insert into test6
(tid,tname)values(3,'liu');
insert into test6
(tid,tname)values(4,'wang');
insert into test6
(tid,tname)values(5,'ceng');
insert into test6
(tid,tname)values(7,'he');
insert into test6
(tid,tname)values(8,'mi');
insert into test6
(tid,tname)values(9,'lin');
insert into test6
(tid,tname)values(10,'xie');
insert into test6
(tid,tname)values(11,'huang');
select count(1) from test6
select * from test6
select tid,tname from (select tid,tname from test6 order by tid ) a1 where rownum < 9
minus
select tid,tname from (select tid,tname from test6 order by tid ) a1 where rownum < 4 ;
当然也用可以求max(id) 的方法来求解
疯狂的毛豆 2010-01-03 11:23
create table course
(id number,student_name varchar(30),course_name varchar(30),rand number);
insert into course
values
(1,'liming','yuwen',80);
insert into course
values
(2,'guohui','yuwen',78);
insert into course
values
(3,'liyi','shuxue',88);
insert into course
values
(4,'zhongsg','shuxue',80);
insert into course
values
(5,'jacky','waiyu',99);
insert into course
values
(6,'andy','shuxue',66);
insert into course
values
(7,'tomy','waiyu',89);
select * from course where exists ( select maxrandcourse.maxrand from (select max(rand) maxrand,course_name from course group by course_name) maxrandcourse where maxrandcourse.maxrand=course.rand and maxrandcourse.course_name=course.course_name);
疯狂的毛豆 2009-12-15 17:10
楼主的这个问题在以下情况下无法得到正确的结果，一个客户有多次购买同一商品的记录。
以下是oracle 环境下模拟数据的建立和求解方法：
create table jindie_recode(customer varchar2(5),productname varchar2(10),pcount number(5));
insert into jindie_recode(customer,productname,pcount)
values
('A','甲',2);
insert into jindie_recode(customer,productname,pcount)
values
('A','甲',4);
insert into jindie_recode(customer,productname,pcount)
values
('B','乙',4);
insert into jindie_recode(customer,productname,pcount)
values
('C','丙',1);
insert into jindie_recode(customer,productname,pcount)
values
('A','丁',2);
insert into jindie_recode(customer,productname,pcount)
values
('B','丙',5);
insert into jindie_recode(customer,productname,pcount)
values
('D','丙',1);
insert into jindie_recode(customer,productname,pcount)
values
('D','丙',1);
commit;
select * from jindie_recode
CUSTOMER PRODUCTNAME PCOUNT
A 甲 2
A 甲 4
B 乙 4
C 丙 1
A 丁 2
B 丙 5
D 丙 1
D 丙 1
select customer from
(select distinct jr.customer ,jr.productname from jindie_recode jr group by jr.customer,jr.productname) r1
group by customer having count(1)>1
楼主的方法会把D也选进来。
zongyuan 2009-08-02 20:59
你的代码对我的帮助很大，谢谢。只不过有个疑问：最后在播放时，如果intPosWrite + intRecv > memstream.Capacity,是不是就会覆盖以前写入的数据，而不播放呢？？？ 谢谢
freeliver54 2009-01-04 15:17