第二十题:
怎么样抽取重复记录
表:
id name
--------
1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
2 test2
3 test3
2 test2
6 test6
查出所有有重复记录的数据,用一句sql 来实现
create table D(
id varchar (20),
name varchar (20)
)
insert into D values('1','test1')
insert into D values('2','test2')
insert into D values('3','test3')
insert into D values('4','test4')
insert into D values('6','test6')
insert into D values('5','test5')
insert into D values('2','test2')
insert into D values('3','test3')
insert into D values('4','test4')
insert into D values('7','test7')
insert into D values('4','test4')
insert into D values('6','test6')
insert into D values('5','test5')
insert into D values('2','test2')
insert into D values('3','test3')
insert into D values('4','test4')
insert into D values('8','test8')
insert into D values('10','test4')
select * from D where
--解法一:
--查询出重复的记录
select id,name from D group by id,name having count(*)>1
--查询出重复的记录及重复次数
select a.id,a.name from D a,(select id,name from D group by id,name having count(*)>1) b where a.id=b.id and a.name=b.name
--解法二:
select t1.* from D t1,
(select sum(1) as Sig,id,name from D group by id,name) as t2
where t1.name=t2.name and t1.id=t2.id and t2.Sig>1
第二十一题:
已知原表(t_salary)
year salary
2000 1000
2001 2000
2002 3000
2003 4000
先要实现显示结果(salary为以前的工资和)
year salary
2000 1000
2001 3000
请问SQL语句怎么写?
create table t_salary(
year int,
salary int
)
select * from t_salary
insert into t_salary values(2000,1000)
insert into t_salary values(2001,2000)
insert into t_salary values(2002,3000)
insert into t_salary values(2003,4000)
--解答:
select year, (select sum(salary) from t_salary b where b.year <= a.year) salary from t_salary a group by year
第二十二题:
year month total
1996 1 3000
1996 3 4000
1996 7 5000
1997 3 4000
1997 6 3000
.
要求按如下格式输出:
year m1,m2,m3,m4
year 为年,m1
通常,你需要获得当前日期和计算一些其他的日期,例如,你的程序可能需要判断一个月的第一天或者最后一天。你们大部分人大概都知道怎样把日期进行分割(年、月、日等),然后仅仅用分割出来的年、月、日等放在几个函数中计算出自己所需要的日期!
:B;wrN$EEx)[ aL0
在这篇文章里,我将告诉你如何使用DATEADD和 ......