SQLÏ°Ìâ3

µÚ¶þÊ®Ì⣺
ÔõôÑù³éÈ¡Öظ´¼Ç¼
񡜧
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