|
#
摘要: Maven，發音是[`meivin]，"專家"的意思。它是一個很好的項目管理工具，很早就進入了我的必備工具行列，但是這次為了把ABPM項目完全遷移并應用maven，所以對maven進行了一些深入的學習。寫這個學習筆記的目的，一個是為了自己備忘，二則希望能夠為其他人學習使用maven縮短一些時間。
maven概要
首先我把maven的... 閱讀全文
經常會看到這樣的SQL面試題，請用一條SQL語句統計出學生的總總成績，請用一條sql語句刪除表中重復的內容，但第一條保留。最近得閑，就試著寫了這么個demo，今天來這和大家分享下，如果大家有其他的sql題也可以拿出來大家一起討論，一起分享。 先創建一個表： CREATE TABLE [dbo].[Score]( [ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [Name] [nvarchar](50) NULL, [CID] [int] NULL, [Score] [int] NULL ） INSERT INTO [Test].[dbo].[Score]([Name],[CID],[Score])VALUES('張三',1,60)
INSERT INTO [Test].[dbo].[Score]([Name],[CID],[Score])VALUES('張三',2,70)
INSERT INTO [Test].[dbo].[Score]([Name],[CID],[Score])VALUES('張三',3,80)
INSERT INTO [Test].[dbo].[Score]([Name],[CID],[Score])VALUES('張三',4,90)
INSERT INTO [Test].[dbo].[Score]([Name],[CID],[Score])VALUES('李四',1,60)
INSERT INTO [Test].[dbo].[Score]([Name],[CID],[Score])VALUES('李四',2,70)
INSERT INTO [Test].[dbo].[Score]([Name],[CID],[Score])VALUES('李四',3,80)
INSERT INTO [Test].[dbo].[Score]([Name],[CID],[Score])VALUES('李四',4,90)
INSERT INTO [Test].[dbo].[Score]([Name],[CID],[Score])VALUES('王五',1,60)
INSERT INTO [Test].[dbo].[Score]([Name],[CID],[Score])VALUES('王五',2,70)
INSERT INTO [Test].[dbo].[Score]([Name],[CID],[Score])VALUES('王五',3,80)
INSERT INTO [Test].[dbo].[Score]([Name],[CID],[Score])VALUES('王五',4,90) 好了，準備工作做完了，下面我們來寫兩條Sql語句，解決開始提出的那兩個問題 A、統計學生的成績 select name,SUM(Score)Score from Score group by Name
B、刪除表中重復的記錄，因為這表中的name是有重復的，所以我們就直接用這表來test delete from Score where Name in (select Name from Score group by Name having COUNT(name)>0)and ID not in (select MIN(id) from Score group by Name having COUNT(Name)>0)
好了，兩條語句解決了兩個問題.
Sql常見面試題（總結）
1.用一條SQL語句 查詢出每門課都大于80分的學生姓名
name kecheng fenshu
張三 語文 81
張三 數學 75
李四 語文 76
李四 數學 90
王五 語文 81
王五 數學 100
王五 英語 90
A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)
2.學生表 如下:
自動編號 學號 姓名 課程編號 課程名稱 分數
1 2005001 張三 0001 數學 69
2 2005002 李四 0001 數學 89
3 2005001 張三 0001 數學 69
刪除除了自動編號不同,其他都相同的學生冗余信息
A: delete tablename where 自動編號 not in(select min(自動編號) from tablename group by 學號,姓名,課程編號,課程名稱,分數)
一個叫department的表，里面只有一個字段name,一共有4條紀錄，分別是a,b,c,d,對應四個球對，現在四個球對進行比賽，用一條sql語句顯示所有可能的比賽組合.
你先按你自己的想法做一下，看結果有我的這個簡單嗎？
答：select a.name, b.name
from team a, team b
where a.name < b.name
請用SQL語句實現：從TestDB數據表中查詢出所有月份的發生額都比101科目相應月份的發生額高的科目。請注意：TestDB中有很多科目，都有1－12月份的發生額。
AccID：科目代碼，Occmonth：發生額月份，DebitOccur：發生額。
數據庫名：JcyAudit，數據集：Select * from TestDB
答：select a.*
from TestDB a
,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) b
where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur
************************************************************************************
面試題：怎么把這樣一個表兒
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成這樣一個結果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
答案一、
select year,
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year
這個是ORACLE 中做的：
select * from (select name, year b1, lead(year) over
(partition by name order by year) b2, lead(m,2) over(partition by name order by year) b3,rank()over(
partition by name order by year) rk from t) where rk=1;
************************************************************************************
精妙的SQL語句！
精妙SQL語句
作者：不詳 發文時間：2003.05.29 10:55:05
說明：復制表(只復制結構,源表名：a 新表名：b)
SQL: select * into b from a where 1<>1
說明：拷貝表(拷貝數據,源表名：a 目標表名：b)
SQL: insert into b(a, b, c) select d,e,f from b;
說明：顯示文章、提交人和最后回復時間
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
說明：外連接查詢(表名1：a 表名2：b)
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
說明：日程安排提前五分鐘提醒
SQL: select * from 日程安排 where datediff('minute',f開始時間,getdate())>5
說明：兩張關聯表，刪除主表中已經在副表中沒有的信息
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
說明：--
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,
(SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,
WHERE X.NUM = Y.NUM （+）
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
WHERE A.NUM = B.NUM
說明：--
SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名稱='"&strdepartmentname&"' and 專業名稱='"&strprofessionname&"' order by 性別,生源地,高考總成績
說明：
從數據庫中去一年的各單位電話費統計(電話費定額賀電化肥清單兩個表來源）
SQL:
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')
說明：四表聯查問題：
SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
說明：得到表中最小的未使用的ID號
SQL:
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
FROM Handle
WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
*******************************************************************************
有兩個表A和B，均有key和value兩個字段，如果B的key在A中也有，就把B的value換為A中對應的value
這道題的SQL語句怎么寫？
update b set b.value=(select a.value from a where a.key=b.key) where b.id in(select b.id from b,a where b.key=a.key);
***************************************************************************
高級sql面試題
原表:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
為了便于閱讀,查詢此表后的結果顯式如下(及格分數為60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------
寫出此查詢語句
沒有裝ＯＲＡＣＬＥ，沒試過
select courseid, coursename ,score ,decode（sign(score-60),-1,'fail','pass') as mark from course
完全正確
SQL> desc course_v
Name Null? Type
----------------------------------------- -------- ----------------------------
COURSEID NUMBER
COURSENAME VARCHAR2(10)
SCORE NUMBER
SQL> select * from course_v;
COURSEID COURSENAME SCORE
---------- ---------- ----------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
SQL> select courseid, coursename ,score ,decode(sign(score-60),-1,'fail','pass') as mark from course_v;
COURSEID COURSENAME SCORE MARK
---------- ---------- ---------- ----
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
*******************************************************************************
原表:
id proid proname
1 1 M
1 2 F
2 1 N
2 2 G
3 1 B
3 2 A
查詢后的表:
id pro1 pro2
1 M F
2 N G
3 B A
寫出查詢語句
解決方案
sql求解
表a
列 a1 a2
記錄 1 a
1 b
2 x
2 y
2 z
用select能選成以下結果嗎？
1 ab
2 xyz
使用pl/sql代碼實現，但要求你組合后的長度不能超出oracle varchar2長度的限制。
下面是一個例子
create or replace type strings_table is table of varchar2(20);
/
create or replace function merge (pv in strings_table) return varchar2
is
ls varchar2(4000);
begin
for i in 1..pv.count loop
ls := ls || pv(i);
end loop;
return ls;
end;
/
create table t (id number,name varchar2(10));
insert into t values(1,'Joan');
insert into t values(1,'Jack');
insert into t values(1,'Tom');
insert into t values(2,'Rose');
insert into t values(2,'Jenny');
column names format a80;
select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names
from (select distinct id from t) t0;
drop type strings_table;
drop function merge;
drop table t;
用sql：
Well if you have a thoretical maximum, which I would assume you would given the legibility of listing hundreds of employees in the way you describe then yes. But the SQL needs to use the LAG function for each employee, hence a hundred emps a hundred LAGs, so kind of bulky.
This example uses a max of 6, and would need more cut n pasting to do more than that.
SQL> select deptno, dname, emps
2 from (
3 select d.deptno, d.dname, rtrim(e.ename ||', '||
4 lead(e.ename,1) over (partition by d.deptno
5 order by e.ename) ||', '||
6 lead(e.ename,2) over (partition by d.deptno
7 order by e.ename) ||', '||
8 lead(e.ename,3) over (partition by d.deptno
9 order by e.ename) ||', '||
10 lead(e.ename,4) over (partition by d.deptno
11 order by e.ename) ||', '||
12 lead(e.ename,5) over (partition by d.deptno
13 order by e.ename),', ') emps,
14 row_number () over (partition by d.deptno
15 order by e.ename) x
16 from emp e, dept d
17 where d.deptno = e.deptno
18 )
19 where x = 1
20 /
DEPTNO DNAME EMPS
------- ----------- ------------------------------------------
10 ACCOUNTING CLARK, KING, MILLER
20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH
30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD
also
先create function get_a2;
create or replace function get_a2( tmp_a1 number)
return varchar2
is
Col_a2 varchar2(4000);
begin
Col_a2:='';
for cur in (select a2 from unite_a where a1=tmp_a1)
loop
Col_a2=Col_a2||cur.a2;
end loop;
return Col_a2;
end get_a2;
select distinct a1 ,get_a2(a1) from unite_a
1 ABC
2 EFG
3 KMN
*******************************************************************************
一個SQL 面試題
去年應聘一個職位未果,其間被考了一個看似簡單的題,但我沒有找到好的大案.
不知各位大蝦有無好的解法?
題為:
有兩個表, t1, t2,
Table t1:
SELLER | NON_SELLER
----- -----
A B
A C
A D
B A
B C
B D
C A
C B
C D
D A
D B
D C
Table t2:
SELLER | COUPON | BAL
----- --------- ---------
A 9 100
B 9 200
C 9 300
D 9 400
A 9.5 100
B 9.5 20
A 10 80
要求用SELECT 語句列出如下結果:------如A的SUM(BAL)為B,C,D的和,B的SUM(BAL)為A,C,D的和.......
且用的方法不要增加數據庫負擔,如用臨時表等.
NON-SELLER| COUPON | SUM(BAL) ------- --------
A 9 900
B 9 800
C 9 700
D 9 600
A 9.5 20
B 9.5 100
C 9.5 120
D 9.5 120
A 10 0
B 10 80
C 10 80
D 10 80
關于論壇上那個SQL微軟面試題
問題：
一百個賬戶各有100$，某個賬戶某天如有支出則添加一條新記錄，記錄其余額。一百天后，請輸出每天所有賬戶的余額信息
這個問題的難點在于每個用戶在某天可能有多條紀錄，也可能一條紀錄也沒有（不包括第一天）
返回的記錄集是一個100天*100個用戶的紀錄集
下面是我的思路：
1.創建表并插入測試數據：我們要求username從1-100
CREATE TABLE [dbo].[TABLE2] (
[username] [varchar] (50) NOT NULL , --用戶名
[outdate] [datetime] NOT NULL , --日期
[cash] [float] NOT NULL --余額
) ON [PRIMARY
declare @i int
set @i=1
while @i<=100
begin
insert table2 values(convert(varchar(50),@i),'2001-10-1',100)
insert table2 values(convert(varchar(50),@i),'2001-11-1',50)
set @i=@i+1
end
insert table2 values(convert(varchar(50),@i),'2001-10-1',90)
select * from table2 order by outdate,convert(int,username)
2.組合查詢語句：
a.我們必須返回一個從第一天開始到100天的紀錄集：
如：2001-10-1（這個日期是任意的） 到 2002-1-8
由于第一天是任意一天，所以我們需要下面的SQL語句：
select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate
from table2
group by username
order by convert(int,username)
這里的奧妙在于：
convert(int,username)-1（記得我們指定用戶名從1-100 :-))
group by username,min(outdate):第一天就可能每個用戶有多個紀錄。
返回的結果：
outdate
------------------------------------------------------
2001-10-01 00:00:00.000
.........
2002-01-08 00:00:00.000
b.返回一個所有用戶名的紀錄集：
select distinct username from table2
返回結果：
username
--------------------------------------------------
1
10
100
......
99
c.返回一個100天記錄集和100個用戶記錄集的笛卡爾集合：
select * from
(
select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate
from table2
group by username
order by convert(int,username)
) as A
CROSS join
(
select distinct username from table2
) as B
order by outdate,convert(int,username)
返回結果100*100條紀錄：
outdate username
2001-10-01 00:00:00.000 1
......
2002-01-08 00:00:00.000 100
d.返回當前所有用戶在數據庫的有的紀錄：
select outdate,username,min(cash) as cash from table2
group by outdate,username
order by outdate,convert(int,username)
返回紀錄：
outdate username cash
2001-10-01 00:00:00.000 1 90
......
2002-01-08 00:00:00.000 100 50
e.將c中返回的笛卡爾集和d中返回的紀錄做left join:
select C.outdate,C.username,
D.cash
from
(
select * from
(
select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate
from table2
group by username
order by convert(int,username)
) as A
CROSS join
(
select distinct username from table2
) as B
) as C
left join
(
select outdate,username,min(cash) as cash from table2
group by outdate,username
) as D
on(C.username=D.username and datediff(d,C.outdate,D.outdate)=0)
order by C.outdate,convert(int,C.username)
注意：用戶在當天如果沒有紀錄，cash字段返回NULL，否則cash返回每個用戶當天的余額
outdate username cash
2001-10-01 00:00:00.000 1 90
2001-10-01 00:00:00.000 2 100
......
2001-10-02 00:00:00.000 1 90
2001-10-02 00:00:00.000 2 NULL <--注意這里
......
2002-01-08 00:00:00.000 100 50
f.好了，現在我們最后要做的就是，如果cash為NULL，我們要返回小于當前紀錄日期的第一個用戶余額(由于我們使用order by cash,所以返回top 1紀錄即可，使用min應該也可以)，這個余額即為當前的余額：
case isnull(D.cash,0)
when 0 then
(
select top 1 cash from table2 where table2.username=C.username
and datediff(d,C.outdate,table2.outdate)<0
order by table2.cash
)
else D.cash
end as cash
g.最后組合的完整語句就是
select C.outdate,C.username,
case isnull(D.cash,0)
when 0 then
(
select top 1 cash from table2 where table2.username=C.username
and datediff(d,C.outdate,table2.outdate)<0
order by table2.cash
)
else D.cash
end as cash
from
(
select * from
(
select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate
from table2
group by username
order by convert(int,username)
) as A
CROSS join
(
select distinct username from table2
) as B
) as C
left join
(
select outdate,username,min(cash) as cash from table2
group by outdate,username
) as D
on(C.username=D.username and datediff(d,C.outdate,D.outdate)=0)
order by C.outdate,convert(int,C.username)
返回結果：
outdate username cash
2001-10-01 00:00:00.000 1 90
2001-10-01 00:00:00.000 2 100
......
2002-01-08 00:00:00.000 100 50
***********************************************************************************
取出sql表中第31到40的記錄（以自動增長ID為主鍵）
*從數據表中取出第n條到第m條的記錄*/
declare @m int
declare @n int
declare @sql varchar(800)
set @m=40
set @n=31
set @sql='select top '+str(@m-@n+1) + '* from idetail where autoid not in(
select top '+ str(@n-1) + 'autoid from idetail)'
exec(@sql)
select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id
--------------------------------------------------------------------------------
select top 10 * from t where id in (select top 40 id from t order by id) order by id desc
*******************************************************************************
一道面試題,寫sql語句
有表a存儲二叉樹的節點,要用一條sql語句查出所有節點及節點所在的層.
表a
c1 c2 A ----------1
---- ---- / \
A B B C --------2
A C / / \
B D D N E ------3
C E / \ \
D F F K I ---4
E I
D K
C N
所要得到的結果如下
jd cs
----- ----
A 1
B 2
C 2
D 3
N 3
E 3
F 4
K 4
I 4
有高手指導一下,我只能用pl/sql寫出來,請教用一條sql語句的寫法
SQL> select c2, level + 1 lv
2 from test start
3 with c1 = 'A'
4 connect by c1 = prior c2
5 union
6 select 'A', 1 from dual
7 order by lv;
C2 LV
-- ----------
A 1
B 2
C 2
D 3
E 3
N 3
F 4
I 4
K 4
已選擇9行。
Student(S#,Sname,Sage,Ssex) 學生表
Course(C#,Cname,T#) 課程表
SC(S#,C#,score) 成績表
Teacher(T#,Tname) 教師表 問題：
1、查詢“001”課程比“002”課程成績高的所有學生的學號；
select a.S#
from (select s#,score from SC where C#=’001′) a,
(select s#,score from SC where C#=’002′) b
where a.score>b.score and a.s#=b.s#; 2、查詢平均成績大于60分的同學的學號和平均成績；
select S#,avg(score)
from sc
group by S# having avg(score) >60; 3、查詢所有同學的學號、姓名、選課數、總成績；
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname 4、查詢姓“李”的老師的個數；
select count(distinct(Tname))
from Teacher
where Tname like ‘李%’; 5、查詢沒學過“葉平”老師課的同學的學號、姓名；
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’葉平’); 6、查詢學過“001”并且也學過編號“002”課程的同學的學號、姓名；
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# and SC.C#=’001′and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′);
7、查詢學過“葉平”老師所教的所有課的同學的學號、姓名；
select S#,Sname
from Student
where S# in
(select S#
from SC ,Course ,Teacher
where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’葉平’ group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=’葉平’)); 8、查詢所有課程成績小于60分的同學的學號、姓名；
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60); 9、查詢沒有學全所有課的同學的學號、姓名；
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S#
group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course); 10、查詢至少有一門課與學號為“1001”的同學所學相同的同學的學號和姓名；
select S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in （select C# from SC where S#='1001'）; 11、刪除學習“葉平”老師課的SC表記錄；
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='葉平'; 12、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分
SELECT L.C# 課程ID,L.score 最高分,R.score 最低分
FROM SC L ,SC R
WHERE L.C# = R.C#
and
L.score = (SELECT MAX(IL.score)
FROM SC IL,Student IM
WHERE IL.C# = L.C# and IM.S#=IL.S#
GROUP BY IL.C#)
and
R.Score = (SELECT MIN(IR.score)
FROM SC IR
WHERE IR.C# = R.C#
GROUP BY IR.C# ); 13、查詢學生平均成績及其名次
SELECT 1+(SELECT COUNT( distinct 平均成績)
FROM (SELECT S#,AVG(score) 平均成績
FROM SC
GROUP BY S# ) T1
WHERE 平均成績 > T2.平均成績) 名次, S# 學生學號,平均成績
FROM (SELECT S#,AVG(score) 平均成績 FROM SC GROUP BY S# ) T2
ORDER BY 平均成績 desc; 14、查詢各科成績前三名的記錄:(不考慮成績并列情況)
SELECT t1.S# as 學生ID,t1.C# as 課程ID,Score as 分數
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC)
ORDER BY t1.C#; 15、查詢每門功成績最好的前兩名
SELECT t1.S# as 學生ID,t1.C# as 課程ID,Score as 分數
FROM SC t1
WHERE score IN (SELECT TOP 2 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC )
ORDER BY t1.C#; 補充：
已經知道原表
year salary
——————
2000 1000
2001 2000
2002 3000
2003 4000 解：
select b.year,sum(a.salary)
from salary a,salary b
where a.year<=b.year
group by b.year
order by b.year; 在面試過程中多次碰到一道SQL查詢的題目，查詢A(ID,Name)表中第31至40條記錄，ID作為主鍵可能是不是連續增長的列，完整的查詢語句如下：
方法一：
select top 10 *
from A
where ID >(select max(ID) from (select top 30 ID from A order by ID ) T) order by ID
方法二：
select top 10 *
from A
where ID not In (select top 30 ID from A order by ID)
order by ID
這幾天做項目，需要一個消息提醒功能，決定用 dwr實現，在 dwr官網和網上找了很多資料，也沒實現精準推送，大多數的例子具體步驟寫的不清楚，不怎么了解 dwr的人看了也未必能看懂，反正我是沒看懂，那時就決定，若自己實現了，一定將具體步驟寫下來，希望能給那些和我一樣的人一些幫助，若有不明白的，可以給小弟留言。我只寫步驟，不寫原理，下面開始。 第一、在項目中引入dwr.jar，然后在web.xml中進行配置，配置如下： <servlet> <servlet-name>dwr-invoker</servlet-name> <servlet-class> org.directwebremoting.servlet.DwrServlet </servlet-class> <init-param> <param-name>crossDomainSessionSecurity</param-name> <param-value>false</param-value> </init-param> <init-param> <param-name>allowScriptTagRemoting</param-name> <param-value>true</param-value> </init-param> <init-param> <param-name>classes</param-name> <param-value>java.lang.Object</param-value> </init-param> <init-param> <param-name>activeReverseAjaxEnabled</param-name> <param-value>true</param-value> </init-param> <init-param> <param-name>initApplicationScopeCreatorsAtStartup</param-name> <param-value>true</param-value> </init-param> <init-param> <param-name>maxWaitAfterWrite</param-name> <param-value>3000</param-value> </init-param> <init-param> <param-name>debug</param-name> <param-value>true</param-value> </init-param> <init-param> <param-name>logLevel</param-name> <param-value>WARN</param-value> </init-param> </servlet> 第二：在web.xml的同級目錄下新建dwr.xml文件，內容如下 <!DOCTYPE dwr PUBLIC "-//GetAhead Limited//DTD Direct Web Remoting 3.0//EN" "http://getahead.org/dwr/dwr30.dtd"> <dwr> <alow> <create creator="new" javascript="MessagePush"> <param name="class" value="com.huatech.messageremind.service.MessagePush"/> </create> </alow> </dwr> 這個是dwr的基本配置，MessagePush在頁面的javascript中使用，com.huatech.messageremind.service.MessagePush實現了想要調用的方法，MessagePush我覺得就相當于java類中的一個映射，在javascript中使用MessagePush.java類中實現的方法，即可在dwr中調用。 第三，要想使用dwr，還要在你想要推送的頁面中引入script， <script type="text/javascript" src="<%=basepath%>dwr/engine.js"></script> <script type="text/javascript" src="<%=basepath%>dwr/util.js"></script> <script type="text/javascript" src="<%=basepath%>dwr/interface/MessagePush.js"></script> 可以看見，也引入了dwr.xml中配置的javascript，engine.js和util.js是必須引入的。 以上三點都是基本配置，沒什么好說的，想使用dwr，就得這么做。 第四，實現消息的精準推送 消息推送簡單，但是想實現精準推送就需要做一些別的操作了 1 在任何一個用戶登錄的時候，都需要將其userId或者其他唯一性標識放入session中，我放的是userId， 這里就以 userId為唯一性標識。 2 在載入想推送的頁面時，需要onload一個我在MessagePush類中實現的方法，當然了，需要使用dwr調用 js的調用方法如下： function onPageLoad(){ var userId = '${userinfo.humanid}'; MessagePush.onPageLoad(userId); } <body onload="dwr.engine.setActiveReverseAjax(true);dwr.engine.setNotifyServerOnPageUnload(true);onPageLoad();> 在onload中的三個函數都是必須的，其中dwr.engine.setActiveReverseAjax(true);dwr.engine.setNotifyServerOnPageUnload(true);是dwr中的函數。 MessagePush類中實現的方法如下： public void onPageLoad(String userId) { ScriptSession scriptSession = WebContextFactory.get().getScriptSession(); scriptSession.setAttribute(userId, userId); DwrScriptSessionManagerUtil dwrScriptSessionManagerUtil = new DwrScriptSessionManagerUtil(); try { dwrScriptSessionManagerUtil.init(); } catch (ServletException e) { e.printStackTrace(); } } 大家注意到，onPageLoad方法中還有一個名為DwrScriptSessionManagerUtil的類，該類如下實現： public class DwrScriptSessionManagerUtil extends DwrServlet{ private static final long serialVersionUID = -7504612622407420071L; public void init() throws ServletException { Container container = ServerContextFactory.get().getContainer(); ScriptSessionManager manager = container .getBean(ScriptSessionManager.class); ScriptSessionListener listener = new ScriptSessionListener() { public void sessionCreated(ScriptSessionEvent ev) { HttpSession session = WebContextFactory.get().getSession(); String userId =((User) session.getAttribute("userinfo")).getHumanid()+""; System.out.println("a ScriptSession is created!"); ev.getSession().setAttribute("userId", userId); } public void sessionDestroyed(ScriptSessionEvent ev) { System.out.println("a ScriptSession is distroyed"); } }; manager.addScriptSessionListener(listener); } } 第四步是最最重要的，為了第四步我研究了兩天多，下面開始消息推送。 第五、消息推送 在你想要推送消息的時候，調用如下方法： public void sendMessageAuto(String userid,String message) { final String userId = userid ; final String autoMessage = message; Browser.withAllSessionsFiltered(new ScriptSessionFilter() { public boolean match(ScriptSession session) { if (session.getAttribute("userId") == null) return false; else return (session.getAttribute("userId")).equals(userId); } }, new Runnable(){ private ScriptBuffer script = new ScriptBuffer(); public void run() { script.appendCall("showMessage", autoMessage); Collection<ScriptSession> sessions = Browser .getTargetSessions(); for (ScriptSession scriptSession : sessions) { scriptSession.addScript(script); } } }); } userid即為你想推給消息的人，message為你想推送的消息，大家注意到這里script.appendCall("showMessage", autoMessage); 其中showMessage為在想推送的頁面中的javascript方法，autoMessage是這個方法的參數，這樣那個頁面就能得到推送的內容了，至于如何展現，就看你的需要了。 至此，一個dwr消息精準推送的步驟就寫完了，其實很多東西都不難，只是我們不知道該怎么用而已。
1.下載ActiveMQ
去官方網站下載：http://activemq.apache.org/
2.運行ActiveMQ
解壓縮apache-activemq-5.5.1-bin.zip，然后雙擊apache-activemq-5.5.1\bin\activemq.bat運行ActiveMQ程序。
啟動ActiveMQ以后，登陸：http://localhost:8161/admin/，創建一個Queue，命名為FirstQueue。
3.創建Eclipse項目并運行
創建project：ActiveMQ-5.5，并導入apache-activemq-5.5.1\lib目錄下需要用到的jar文件，項目結構如下圖所示：
3.1.Sender.java
package com.xuwei.activemq;
import javax.jms.Connection;
import javax.jms.ConnectionFactory;
import javax.jms.DeliveryMode;
import javax.jms.Destination;
import javax.jms.MessageProducer;
import javax.jms.Session;
import javax.jms.TextMessage;
import org.apache.activemq.ActiveMQConnection;
import org.apache.activemq.ActiveMQConnectionFactory;
public class Sender {
private static final int SEND_NUMBER = 5;
public static void main(String[] args) {
// ConnectionFactory ：連接工廠，JMS 用它創建連接
ConnectionFactory connectionFactory;
// Connection ：JMS 客戶端到JMS Provider 的連接
Connection connection = null;
// Session： 一個發送或接收消息的線程
Session session;
// Destination ：消息的目的地;消息發送給誰.
Destination destination;
// MessageProducer：消息發送者
MessageProducer producer;
// TextMessage message;
// 構造ConnectionFactory實例對象，此處采用ActiveMq的實現jar
connectionFactory = new ActiveMQConnectionFactory(
ActiveMQConnection.DEFAULT_USER,
ActiveMQConnection.DEFAULT_PASSWORD,
"tcp://localhost:61616");
try {
// 構造從工廠得到連接對象
connection = connectionFactory.createConnection();
// 啟動
connection.start();
// 獲取操作連接
session = connection.createSession(Boolean.TRUE,
Session.AUTO_ACKNOWLEDGE);
// 獲取session注意參數值xingbo.xu-queue是一個服務器的queue，須在在ActiveMq的console配置
destination = session.createQueue("FirstQueue");
// 得到消息生成者【發送者】
producer = session.createProducer(destination);
// 設置不持久化，此處學習，實際根據項目決定
producer.setDeliveryMode(DeliveryMode.NON_PERSISTENT);
// 構造消息，此處寫死，項目就是參數，或者方法獲取
sendMessage(session, producer);
session.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != connection)
connection.close();
} catch (Throwable ignore) {
}
}
}
public static void sendMessage(Session session, MessageProducer producer)
throws Exception {
for (int i = 1; i <= SEND_NUMBER; i++) {
TextMessage message = session
.createTextMessage("ActiveMq 發送的消息" + i);
// 發送消息到目的地方
System.out.println("發送消息：" + "ActiveMq 發送的消息" + i);
producer.send(message);
}
}
}
3.2.Receiver.java
package com.xuwei.activemq;
import javax.jms.Connection;
import javax.jms.ConnectionFactory;
import javax.jms.Destination;
import javax.jms.MessageConsumer;
import javax.jms.Session;
import javax.jms.TextMessage;
import org.apache.activemq.ActiveMQConnection;
import org.apache.activemq.ActiveMQConnectionFactory;
public class Receiver {
public static void main(String[] args) {
// ConnectionFactory ：連接工廠，JMS 用它創建連接
ConnectionFactory connectionFactory;
// Connection ：JMS 客戶端到JMS Provider 的連接
Connection connection = null;
// Session： 一個發送或接收消息的線程
Session session;
// Destination ：消息的目的地;消息發送給誰.
Destination destination;
// 消費者，消息接收者
MessageConsumer consumer;
connectionFactory = new ActiveMQConnectionFactory(
ActiveMQConnection.DEFAULT_USER,
ActiveMQConnection.DEFAULT_PASSWORD,
"tcp://localhost:61616");
try {
// 構造從工廠得到連接對象
connection = connectionFactory.createConnection();
// 啟動
connection.start();
// 獲取操作連接
session = connection.createSession(Boolean.FALSE,
Session.AUTO_ACKNOWLEDGE);
// 獲取session注意參數值xingbo.xu-queue是一個服務器的queue，須在在ActiveMq的console配置
destination = session.createQueue("FirstQueue");
consumer = session.createConsumer(destination);
while (true) {
//設置接收者接收消息的時間，為了便于測試，這里誰定為100s
TextMessage message = (TextMessage) consumer.receive(100000);
if (null != message) {
System.out.println("收到消息" + message.getText());
} else {
break;
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != connection)
connection.close();
} catch (Throwable ignore) {
}
}
}
}
4.注意事項
- 最后接收者跟發送者在不同的機器上測試
- 項目所引用的jar最后在ActiveMQ下的lib中找，這樣不會出現版本沖突。
5.測試過程
因為是在單機上測試，所以需要開啟兩個eclipse，每一個eclipse都有自身的workspace。我們在eclipse1中運行Receiver，在eclipse2中運行Sender。
剛開始eclipse1中運行Receiver以后console介面沒有任何信息，在eclipse2中運行Sender以后，eclipse2中的console顯示如下信息：
發送消息：ActiveMq 發送的消息1
發送消息：ActiveMq 發送的消息2
發送消息：ActiveMq 發送的消息3
發送消息：ActiveMq 發送的消息4
發送消息：ActiveMq 發送的消息5
而回到eclipse1中發現console界面出現如下信息：
收到消息ActiveMq 發送的消息1
收到消息ActiveMq 發送的消息2
收到消息ActiveMq 發送的消息3
收到消息ActiveMq 發送的消息4
收到消息ActiveMq 發送的消息5
PS:2012-2-27
今天發現測試并不需要開啟兩個eclipse，在一個eclipse下頁可以啟動多個程序，并且有多個console，在上面的Receiver.java中，設置一個較大的時間，比如receive(500000)，如下代碼所示：
TextMessage message = (TextMessage) consumer.receive(500000); 這個時候運行Receiver.java的話，會使得這個Receiver.java一直運行500秒，在eclipse中可以發現：
點擊那個紅色方塊可以手動停止運行程序。
運行玩receiver以后我們在運行sender，在運行完sender以后，我們要切換到receiver的console，如下圖所示：
http://www.cnblogs.com/xwdreamer/archive/2012/02/21/2360818.html
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>js實現checkbox全選，反選，全不選</title>
</head>
<script type="text/javascript" src="js/jquery-1.6.4.js"></script>
<script type="text/javascript">
//復選框全選
function checkAll(formvalue) {
var roomids = document.getElementsByName(formvalue);
for ( var j = 0; j < roomids.length; j++) {
if (roomids.item(j).checked == false) {
roomids.item(j).checked = true;
}
}
}
//復選框全不選
function uncheckAll(formvalue) {
var roomids = document.getElementsByName(formvalue);
for ( var j = 0; j < roomids.length; j++) {
if (roomids.item(j).checked == true) {
roomids.item(j).checked = false;
}
}
}
//復選框選擇轉換
function switchAll(formvalue) {
var roomids = document.getElementsByName(formvalue);
for ( var j = 0; j < roomids.length; j++) {
roomids.item(j).checked = !roomids.item(j).checked;
}
}
</script>
</head>
<body>
<input type="radio" name="all" id="all" onclick="checkAll('test')" />
全選
<input type="radio" name="all" id="Checkbox1"
onclick="uncheckAll('test')" /> 全不選
<input type="radio" name="all" id="Checkbox2"
onclick="switchAll('test')" /> 反選
<br />
<input name="test" value="復選框1" type="checkbox" /> 復選框1 <br />
<input name="test" value="復選框2" type="checkbox" /> 復選框2 <br />
<input name="test" value="復選框3" type="checkbox" /> 復選框3 <br />
<input name="test" value="復選框4" type="checkbox" /> 復選框4 <br />
<input name="test" value="復選框5" type="checkbox" /> 復選框5 <br />
<input name="test" value="復選框6" type="checkbox" /> 復選框6 <br />
</body>
</html>
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>js test</title>
</head>
<script type="text/javascript" src="js/jquery-1.6.4.js"></script>
<script type="text/javascript">
function selectAll(){
var name=document.getElementsByName('checkbox');
for(var i=0;i<name.length;i++){
if(name.item(i).checked==false)
name.item(i).checked=true;
}
}
</script>
<body>
<form name="myForm" method="post" action="CheckValue">
全選：<input type="radio" name="all" id="all" onclick="selectAll('checkebox')" /><br/>
表單名稱1: <input type="checkbox" name="checkbox" value="checkbox1" /><br/>
表單名稱2: <input type="checkbox" name="checkbox" value="checkbox2" /><br/>
表單名稱3: <input type="checkbox" name="checkbox" value="checkbox3" /><br/>
表單名稱4: <input type="checkbox" name="checkbox" value="checkbox4" /><br/>
表單名稱5: <input type="checkbox" name="checkbox" value="checkbox5" /><br/>
表單名稱6: <input type="checkbox" name="checkbox" value="checkbox6" /><br/>
<input type="submit" value="提交"/>
</form>
</body>
</html> <?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>abs</display-name>
<servlet>
<servlet-name>CheckValue</servlet-name>
<servlet-class>org.abin.lee.servlet.CheckValue</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>CheckValue</servlet-name>
<url-pattern>/CheckValue</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
</web-app> package org.abin.lee.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class CheckValue extends HttpServlet{
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
String[] value=req.getParameterValues("checkbox");
for(int i=0;i<value.length;i++){
System.out.println("第"+i+"個："+value[i]);
}
}
}
<script language=javascript> //第一種方法 function selectall1() { var a = document.getElementsByTagName("input"); if(a[0].checked==true){ for (var i=0; i<a.length; i++) if (a[i].type == "checkbox") a[i].checked = false; } else { for (var i=0; i<a.length; i++) if (a[i].type == "checkbox") a[i].checked = true; } } //第二種方法 function selectall2() { var tform = document.forms['form1']; for (var i=0;i<tform.length;i++) { var e = tform.elements[i]; if (e.type == "checkbox") e.checked = !e.checked; } } //第三種方法，結合上述兩種方法 function selectall3() { var a = document.getElementsByTagName("input"); for (var i=0; i<a.length; i++) if (a[i].type == "checkbox") a[i].checked =!a[i].checked; } //第四種方法 function selectall4(id){ //用id區分 var tform=document.forms['form1']; for(var i=0;i<tform.length;i++){ var e=tform.elements[i]; if(e.type=="checkbox" && e.name==id) e.checked=!e.checked; } } //第五種方法 function selectall(theform,thename){ //theform指定的form,thename是checkbox的name屬性 var tform=document.forms[theform]; document.getElementById("thewen").value='反選'; for(var i=0;i<tform.length;i++){ var e=tform.elements[i]; if(e.type=='checkbox' && e.name==thename)e.checked=!e.checked; } } </script> <form id="form1" name="form1" method="post" action=""> <input type="checkbox" name="sid" value="1" /> <input name="thes" type="button" onclick="javascript:selectall3()" value="全選" /> </form>
|