|
#
摘要: Maven，發(fā)音是[`meivin]，"專家"的意思。它是一個(gè)很好的項(xiàng)目管理工具，很早就進(jìn)入了我的必備工具行列，但是這次為了把ABPM項(xiàng)目完全遷移并應(yīng)用maven，所以對(duì)maven進(jìn)行了一些深入的學(xué)習(xí)。寫(xiě)這個(gè)學(xué)習(xí)筆記的目的，一個(gè)是為了自己備忘，二則希望能夠?yàn)槠渌藢W(xué)習(xí)使用maven縮短一些時(shí)間。
maven概要
首先我把maven的... 閱讀全文
經(jīng)常會(huì)看到這樣的SQL面試題，請(qǐng)用一條SQL語(yǔ)句統(tǒng)計(jì)出學(xué)生的總總成績(jī)，請(qǐng)用一條sql語(yǔ)句刪除表中重復(fù)的內(nèi)容，但第一條保留。最近得閑，就試著寫(xiě)了這么個(gè)demo，今天來(lái)這和大家分享下，如果大家有其他的sql題也可以拿出來(lái)大家一起討論，一起分享。 先創(chuàng)建一個(gè)表： 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) 好了，準(zhǔn)備工作做完了，下面我們來(lái)寫(xiě)兩條Sql語(yǔ)句，解決開(kāi)始提出的那兩個(gè)問(wèn)題 A、統(tǒng)計(jì)學(xué)生的成績(jī) select name,SUM(Score)Score from Score group by Name
B、刪除表中重復(fù)的記錄，因?yàn)檫@表中的name是有重復(fù)的，所以我們就直接用這表來(lái)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)
好了，兩條語(yǔ)句解決了兩個(gè)問(wèn)題.
Sql常見(jiàn)面試題（總結(jié)）
1.用一條SQL語(yǔ)句 查詢出每門(mén)課都大于80分的學(xué)生姓名
name kecheng fenshu
張三 語(yǔ)文 81
張三 數(shù)學(xué) 75
李四 語(yǔ)文 76
李四 數(shù)學(xué) 90
王五 語(yǔ)文 81
王五 數(shù)學(xué) 100
王五 英語(yǔ) 90
A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)
2.學(xué)生表 如下:
自動(dòng)編號(hào) 學(xué)號(hào) 姓名 課程編號(hào) 課程名稱 分?jǐn)?shù)
1 2005001 張三 0001 數(shù)學(xué) 69
2 2005002 李四 0001 數(shù)學(xué) 89
3 2005001 張三 0001 數(shù)學(xué) 69
刪除除了自動(dòng)編號(hào)不同,其他都相同的學(xué)生冗余信息
A: delete tablename where 自動(dòng)編號(hào) not in(select min(自動(dòng)編號(hào)) from tablename group by 學(xué)號(hào),姓名,課程編號(hào),課程名稱,分?jǐn)?shù))
一個(gè)叫department的表，里面只有一個(gè)字段name,一共有4條紀(jì)錄，分別是a,b,c,d,對(duì)應(yīng)四個(gè)球?qū)ΓF(xiàn)在四個(gè)球?qū)M(jìn)行比賽，用一條sql語(yǔ)句顯示所有可能的比賽組合.
你先按你自己的想法做一下，看結(jié)果有我的這個(gè)簡(jiǎn)單嗎？
答：select a.name, b.name
from team a, team b
where a.name < b.name
請(qǐng)用SQL語(yǔ)句實(shí)現(xiàn)：從TestDB數(shù)據(jù)表中查詢出所有月份的發(fā)生額都比101科目相應(yīng)月份的發(fā)生額高的科目。請(qǐng)注意：TestDB中有很多科目，都有1－12月份的發(fā)生額。
AccID：科目代碼，Occmonth：發(fā)生額月份，DebitOccur：發(fā)生額。
數(shù)據(jù)庫(kù)名：JcyAudit，數(shù)據(jù)集：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
************************************************************************************
面試題：怎么把這樣一個(gè)表兒
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
查成這樣一個(gè)結(jié)果
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
這個(gè)是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語(yǔ)句！
精妙SQL語(yǔ)句
作者：不詳 發(fā)文時(shí)間：2003.05.29 10:55:05
說(shuō)明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a 新表名：b)
SQL: select * into b from a where 1<>1
說(shuō)明：拷貝表(拷貝數(shù)據(jù),源表名：a 目標(biāo)表名：b)
SQL: insert into b(a, b, c) select d,e,f from b;
說(shuō)明：顯示文章、提交人和最后回復(fù)時(shí)間
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
說(shuō)明：外連接查詢(表名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
說(shuō)明：日程安排提前五分鐘提醒
SQL: select * from 日程安排 where datediff('minute',f開(kāi)始時(shí)間,getdate())>5
說(shuō)明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒(méi)有的信息
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
說(shuō)明：--
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
說(shuō)明：--
SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名稱='"&strdepartmentname&"' and 專業(yè)名稱='"&strprofessionname&"' order by 性別,生源地,高考總成績(jī)
說(shuō)明：
從數(shù)據(jù)庫(kù)中去一年的各單位電話費(fèi)統(tǒng)計(jì)(電話費(fèi)定額賀電化肥清單兩個(gè)表來(lái)源）
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')
說(shuō)明：四表聯(lián)查問(wèn)題：
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 .....
說(shuō)明：得到表中最小的未使用的ID號(hào)
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)
*******************************************************************************
有兩個(gè)表A和B，均有key和value兩個(gè)字段，如果B的key在A中也有，就把B的value換為A中對(duì)應(yīng)的value
這道題的SQL語(yǔ)句怎么寫(xiě)？
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);
***************************************************************************
高級(jí)sql面試題
原表:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
為了便于閱讀,查詢此表后的結(jié)果顯式如下(及格分?jǐn)?shù)為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
---------------------------------------------------
寫(xiě)出此查詢語(yǔ)句
沒(méi)有裝ＯＲＡＣＬＥ，沒(méi)試過(guò)
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
寫(xiě)出查詢語(yǔ)句
解決方案
sql求解
表a
列 a1 a2
記錄 1 a
1 b
2 x
2 y
2 z
用select能選成以下結(jié)果嗎？
1 ab
2 xyz
使用pl/sql代碼實(shí)現(xiàn)，但要求你組合后的長(zhǎng)度不能超出oracle varchar2長(zhǎng)度的限制。
下面是一個(gè)例子
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
*******************************************************************************
一個(gè)SQL 面試題
去年應(yīng)聘一個(gè)職位未果,其間被考了一個(gè)看似簡(jiǎn)單的題,但我沒(méi)有找到好的大案.
不知各位大蝦有無(wú)好的解法?
題為:
有兩個(gè)表, 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 語(yǔ)句列出如下結(jié)果:------如A的SUM(BAL)為B,C,D的和,B的SUM(BAL)為A,C,D的和.......
且用的方法不要增加數(shù)據(jù)庫(kù)負(fù)擔(dān),如用臨時(shí)表等.
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
關(guān)于論壇上那個(gè)SQL微軟面試題
問(wèn)題：
一百個(gè)賬戶各有100$，某個(gè)賬戶某天如有支出則添加一條新記錄，記錄其余額。一百天后，請(qǐng)輸出每天所有賬戶的余額信息
這個(gè)問(wèn)題的難點(diǎn)在于每個(gè)用戶在某天可能有多條紀(jì)錄，也可能一條紀(jì)錄也沒(méi)有（不包括第一天）
返回的記錄集是一個(gè)100天*100個(gè)用戶的紀(jì)錄集
下面是我的思路：
1.創(chuàng)建表并插入測(cè)試數(shù)據(jù)：我們要求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.組合查詢語(yǔ)句：
a.我們必須返回一個(gè)從第一天開(kāi)始到100天的紀(jì)錄集：
如：2001-10-1（這個(gè)日期是任意的） 到 2002-1-8
由于第一天是任意一天，所以我們需要下面的SQL語(yǔ)句：
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):第一天就可能每個(gè)用戶有多個(gè)紀(jì)錄。
返回的結(jié)果：
outdate
------------------------------------------------------
2001-10-01 00:00:00.000
.........
2002-01-08 00:00:00.000
b.返回一個(gè)所有用戶名的紀(jì)錄集：
select distinct username from table2
返回結(jié)果：
username
--------------------------------------------------
1
10
100
......
99
c.返回一個(gè)100天記錄集和100個(gè)用戶記錄集的笛卡爾集合：
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)
返回結(jié)果100*100條紀(jì)錄：
outdate username
2001-10-01 00:00:00.000 1
......
2002-01-08 00:00:00.000 100
d.返回當(dāng)前所有用戶在數(shù)據(jù)庫(kù)的有的紀(jì)錄：
select outdate,username,min(cash) as cash from table2
group by outdate,username
order by outdate,convert(int,username)
返回紀(jì)錄：
outdate username cash
2001-10-01 00:00:00.000 1 90
......
2002-01-08 00:00:00.000 100 50
e.將c中返回的笛卡爾集和d中返回的紀(jì)錄做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)
注意：用戶在當(dāng)天如果沒(méi)有紀(jì)錄，cash字段返回NULL，否則cash返回每個(gè)用戶當(dāng)天的余額
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.好了，現(xiàn)在我們最后要做的就是，如果cash為NULL，我們要返回小于當(dāng)前紀(jì)錄日期的第一個(gè)用戶余額(由于我們使用order by cash,所以返回top 1紀(jì)錄即可，使用min應(yīng)該也可以)，這個(gè)余額即為當(dāng)前的余額：
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.最后組合的完整語(yǔ)句就是
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)
返回結(jié)果：
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的記錄（以自動(dòng)增長(zhǎng)ID為主鍵）
*從數(shù)據(jù)表中取出第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
*******************************************************************************
一道面試題,寫(xiě)sql語(yǔ)句
有表a存儲(chǔ)二叉樹(shù)的節(jié)點(diǎn),要用一條sql語(yǔ)句查出所有節(jié)點(diǎn)及節(jié)點(diǎn)所在的層.
表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
所要得到的結(jié)果如下
jd cs
----- ----
A 1
B 2
C 2
D 3
N 3
E 3
F 4
K 4
I 4
有高手指導(dǎo)一下,我只能用pl/sql寫(xiě)出來(lái),請(qǐng)教用一條sql語(yǔ)句的寫(xiě)法
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) 學(xué)生表
Course(C#,Cname,T#) 課程表
SC(S#,C#,score) 成績(jī)表
Teacher(T#,Tname) 教師表 問(wèn)題：
1、查詢“001”課程比“002”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)；
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、查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)；
select S#,avg(score)
from sc
group by S# having avg(score) >60; 3、查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)；
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、查詢姓“李”的老師的個(gè)數(shù)；
select count(distinct(Tname))
from Teacher
where Tname like ‘李%’; 5、查詢沒(méi)學(xué)過(guò)“葉平”老師課的同學(xué)的學(xué)號(hào)、姓名；
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、查詢學(xué)過(guò)“001”并且也學(xué)過(guò)編號(hào)“002”課程的同學(xué)的學(xué)號(hào)、姓名；
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、查詢學(xué)過(guò)“葉平”老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名；
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、查詢所有課程成績(jī)小于60分的同學(xué)的學(xué)號(hào)、姓名；
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60); 9、查詢沒(méi)有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名；
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、查詢至少有一門(mén)課與學(xué)號(hào)為“1001”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名；
select S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in （select C# from SC where S#='1001'）; 11、刪除學(xué)習(xí)“葉平”老師課的SC表記錄；
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='葉平'; 12、查詢各科成績(jī)最高和最低的分：以如下形式顯示：課程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、查詢學(xué)生平均成績(jī)及其名次
SELECT 1+(SELECT COUNT( distinct 平均成績(jī))
FROM (SELECT S#,AVG(score) 平均成績(jī)
FROM SC
GROUP BY S# ) T1
WHERE 平均成績(jī) > T2.平均成績(jī)) 名次, S# 學(xué)生學(xué)號(hào),平均成績(jī)
FROM (SELECT S#,AVG(score) 平均成績(jī) FROM SC GROUP BY S# ) T2
ORDER BY 平均成績(jī) desc; 14、查詢各科成績(jī)前三名的記錄:(不考慮成績(jī)并列情況)
SELECT t1.S# as 學(xué)生ID,t1.C# as 課程ID,Score as 分?jǐn)?shù)
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、查詢每門(mén)功成績(jī)最好的前兩名
SELECT t1.S# as 學(xué)生ID,t1.C# as 課程ID,Score as 分?jǐn)?shù)
FROM SC t1
WHERE score IN (SELECT TOP 2 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC )
ORDER BY t1.C#; 補(bǔ)充：
已經(jīng)知道原表
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; 在面試過(guò)程中多次碰到一道SQL查詢的題目，查詢A(ID,Name)表中第31至40條記錄，ID作為主鍵可能是不是連續(xù)增長(zhǎng)的列，完整的查詢語(yǔ)句如下：
方法一：
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
這幾天做項(xiàng)目，需要一個(gè)消息提醒功能，決定用 dwr實(shí)現(xiàn)，在 dwr官網(wǎng)和網(wǎng)上找了很多資料，也沒(méi)實(shí)現(xiàn)精準(zhǔn)推送，大多數(shù)的例子具體步驟寫(xiě)的不清楚，不怎么了解 dwr的人看了也未必能看懂，反正我是沒(méi)看懂，那時(shí)就決定，若自己實(shí)現(xiàn)了，一定將具體步驟寫(xiě)下來(lái)，希望能給那些和我一樣的人一些幫助，若有不明白的，可以給小弟留言。我只寫(xiě)步驟，不寫(xiě)原理，下面開(kāi)始。 第一、在項(xiàng)目中引入dwr.jar，然后在web.xml中進(jìn)行配置，配置如下： <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的同級(jí)目錄下新建dwr.xml文件，內(nèi)容如下 <!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> 這個(gè)是dwr的基本配置，MessagePush在頁(yè)面的javascript中使用，com.huatech.messageremind.service.MessagePush實(shí)現(xiàn)了想要調(diào)用的方法，MessagePush我覺(jué)得就相當(dāng)于java類中的一個(gè)映射，在javascript中使用MessagePush.java類中實(shí)現(xiàn)的方法，即可在dwr中調(diào)用。 第三，要想使用dwr，還要在你想要推送的頁(yè)面中引入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> 可以看見(jiàn)，也引入了dwr.xml中配置的javascript，engine.js和util.js是必須引入的。 以上三點(diǎn)都是基本配置，沒(méi)什么好說(shuō)的，想使用dwr，就得這么做。 第四，實(shí)現(xiàn)消息的精準(zhǔn)推送 消息推送簡(jiǎn)單，但是想實(shí)現(xiàn)精準(zhǔn)推送就需要做一些別的操作了 1 在任何一個(gè)用戶登錄的時(shí)候，都需要將其userId或者其他唯一性標(biāo)識(shí)放入session中，我放的是userId， 這里就以 userId為唯一性標(biāo)識(shí)。 2 在載入想推送的頁(yè)面時(shí)，需要onload一個(gè)我在MessagePush類中實(shí)現(xiàn)的方法，當(dāng)然了，需要使用dwr調(diào)用 js的調(diào)用方法如下： function onPageLoad(){ var userId = '${userinfo.humanid}'; MessagePush.onPageLoad(userId); } <body onload="dwr.engine.setActiveReverseAjax(true);dwr.engine.setNotifyServerOnPageUnload(true);onPageLoad();> 在onload中的三個(gè)函數(shù)都是必須的，其中dwr.engine.setActiveReverseAjax(true);dwr.engine.setNotifyServerOnPageUnload(true);是dwr中的函數(shù)。 MessagePush類中實(shí)現(xiàn)的方法如下： 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方法中還有一個(gè)名為DwrScriptSessionManagerUtil的類，該類如下實(shí)現(xiàn)： 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); } } 第四步是最最重要的，為了第四步我研究了兩天多，下面開(kāi)始消息推送。 第五、消息推送 在你想要推送消息的時(shí)候，調(diào)用如下方法： 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為在想推送的頁(yè)面中的javascript方法，autoMessage是這個(gè)方法的參數(shù)，這樣那個(gè)頁(yè)面就能得到推送的內(nèi)容了，至于如何展現(xiàn)，就看你的需要了。 至此，一個(gè)dwr消息精準(zhǔn)推送的步驟就寫(xiě)完了，其實(shí)很多東西都不難，只是我們不知道該怎么用而已。
1.下載ActiveMQ
去官方網(wǎng)站下載：http://activemq.apache.org/
2.運(yùn)行ActiveMQ
解壓縮apache-activemq-5.5.1-bin.zip，然后雙擊apache-activemq-5.5.1\bin\activemq.bat運(yùn)行ActiveMQ程序。
啟動(dòng)ActiveMQ以后，登陸：http://localhost:8161/admin/，創(chuàng)建一個(gè)Queue，命名為FirstQueue。
3.創(chuàng)建Eclipse項(xiàng)目并運(yùn)行
創(chuàng)建project：ActiveMQ-5.5，并導(dǎo)入apache-activemq-5.5.1\lib目錄下需要用到的jar文件，項(xiàng)目結(jié)構(gòu)如下圖所示：
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 用它創(chuàng)建連接
ConnectionFactory connectionFactory;
// Connection ：JMS 客戶端到JMS Provider 的連接
Connection connection = null;
// Session： 一個(gè)發(fā)送或接收消息的線程
Session session;
// Destination ：消息的目的地;消息發(fā)送給誰(shuí).
Destination destination;
// MessageProducer：消息發(fā)送者
MessageProducer producer;
// TextMessage message;
// 構(gòu)造ConnectionFactory實(shí)例對(duì)象，此處采用ActiveMq的實(shí)現(xiàn)jar
connectionFactory = new ActiveMQConnectionFactory(
ActiveMQConnection.DEFAULT_USER,
ActiveMQConnection.DEFAULT_PASSWORD,
"tcp://localhost:61616");
try {
// 構(gòu)造從工廠得到連接對(duì)象
connection = connectionFactory.createConnection();
// 啟動(dòng)
connection.start();
// 獲取操作連接
session = connection.createSession(Boolean.TRUE,
Session.AUTO_ACKNOWLEDGE);
// 獲取session注意參數(shù)值xingbo.xu-queue是一個(gè)服務(wù)器的queue，須在在ActiveMq的console配置
destination = session.createQueue("FirstQueue");
// 得到消息生成者【發(fā)送者】
producer = session.createProducer(destination);
// 設(shè)置不持久化，此處學(xué)習(xí)，實(shí)際根據(jù)項(xiàng)目決定
producer.setDeliveryMode(DeliveryMode.NON_PERSISTENT);
// 構(gòu)造消息，此處寫(xiě)死，項(xiàng)目就是參數(shù)，或者方法獲取
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 發(fā)送的消息" + i);
// 發(fā)送消息到目的地方
System.out.println("發(fā)送消息：" + "ActiveMq 發(fā)送的消息" + 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 用它創(chuàng)建連接
ConnectionFactory connectionFactory;
// Connection ：JMS 客戶端到JMS Provider 的連接
Connection connection = null;
// Session： 一個(gè)發(fā)送或接收消息的線程
Session session;
// Destination ：消息的目的地;消息發(fā)送給誰(shuí).
Destination destination;
// 消費(fèi)者，消息接收者
MessageConsumer consumer;
connectionFactory = new ActiveMQConnectionFactory(
ActiveMQConnection.DEFAULT_USER,
ActiveMQConnection.DEFAULT_PASSWORD,
"tcp://localhost:61616");
try {
// 構(gòu)造從工廠得到連接對(duì)象
connection = connectionFactory.createConnection();
// 啟動(dòng)
connection.start();
// 獲取操作連接
session = connection.createSession(Boolean.FALSE,
Session.AUTO_ACKNOWLEDGE);
// 獲取session注意參數(shù)值xingbo.xu-queue是一個(gè)服務(wù)器的queue，須在在ActiveMq的console配置
destination = session.createQueue("FirstQueue");
consumer = session.createConsumer(destination);
while (true) {
//設(shè)置接收者接收消息的時(shí)間，為了便于測(cè)試，這里誰(shuí)定為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.注意事項(xiàng)
- 最后接收者跟發(fā)送者在不同的機(jī)器上測(cè)試
- 項(xiàng)目所引用的jar最后在ActiveMQ下的lib中找，這樣不會(huì)出現(xiàn)版本沖突。
5.測(cè)試過(guò)程
因?yàn)槭窃趩螜C(jī)上測(cè)試，所以需要開(kāi)啟兩個(gè)eclipse，每一個(gè)eclipse都有自身的workspace。我們?cè)趀clipse1中運(yùn)行Receiver，在eclipse2中運(yùn)行Sender。
剛開(kāi)始eclipse1中運(yùn)行Receiver以后console介面沒(méi)有任何信息，在eclipse2中運(yùn)行Sender以后，eclipse2中的console顯示如下信息：
發(fā)送消息：ActiveMq 發(fā)送的消息1
發(fā)送消息：ActiveMq 發(fā)送的消息2
發(fā)送消息：ActiveMq 發(fā)送的消息3
發(fā)送消息：ActiveMq 發(fā)送的消息4
發(fā)送消息：ActiveMq 發(fā)送的消息5
而回到eclipse1中發(fā)現(xiàn)console界面出現(xiàn)如下信息：
收到消息ActiveMq 發(fā)送的消息1
收到消息ActiveMq 發(fā)送的消息2
收到消息ActiveMq 發(fā)送的消息3
收到消息ActiveMq 發(fā)送的消息4
收到消息ActiveMq 發(fā)送的消息5
PS:2012-2-27
今天發(fā)現(xiàn)測(cè)試并不需要開(kāi)啟兩個(gè)eclipse，在一個(gè)eclipse下頁(yè)可以啟動(dòng)多個(gè)程序，并且有多個(gè)console，在上面的Receiver.java中，設(shè)置一個(gè)較大的時(shí)間，比如receive(500000)，如下代碼所示：
TextMessage message = (TextMessage) consumer.receive(500000); 這個(gè)時(shí)候運(yùn)行Receiver.java的話，會(huì)使得這個(gè)Receiver.java一直運(yùn)行500秒，在eclipse中可以發(fā)現(xiàn)：
點(diǎn)擊那個(gè)紅色方塊可以手動(dòng)停止運(yùn)行程序。
運(yùn)行玩receiver以后我們?cè)谶\(yùn)行sender，在運(yùn)行完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實(shí)現(xiàn)checkbox全選，反選，全不選</title>
</head>
<script type="text/javascript" src="js/jquery-1.6.4.js"></script>
<script type="text/javascript">
//復(fù)選框全選
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;
}
}
}
//復(fù)選框全不選
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;
}
}
}
//復(fù)選框選擇轉(zhuǎn)換
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="復(fù)選框1" type="checkbox" /> 復(fù)選框1 <br />
<input name="test" value="復(fù)選框2" type="checkbox" /> 復(fù)選框2 <br />
<input name="test" value="復(fù)選框3" type="checkbox" /> 復(fù)選框3 <br />
<input name="test" value="復(fù)選框4" type="checkbox" /> 復(fù)選框4 <br />
<input name="test" value="復(fù)選框5" type="checkbox" /> 復(fù)選框5 <br />
<input name="test" value="復(fù)選框6" type="checkbox" /> 復(fù)選框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+"個(gè)："+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; } } //第三種方法，結(jié)合上述兩種方法 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區(qū)分 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>
|