sql Á¬½ÓÎÊÌ⣡ - MS-SQL Server / »ù´¡Àà

1.ÏÂÃæÓбí
name birth
a 1981
b 1981
c 1980
d 1981
e 1980
2.дһsqlµÃµ½ÏÂÃæµÄ½á¹û
name1 name2 birth
a b 1981
a d 1981
b d 1981
c e 1980


ÏÂÃæÊÇÎҵļǹý£¬ÀÏÊÇÈ¥²»ÁËÖظ´µÄ£º
select * from testj;
select distinct a.name,b.name,a.year,a.year-1980 
from testj a,testj b where a.year=b.year and a.name!=b.name 
order by a.name,b.name;

---

µ«Êǵõ½µÄ½á¹ûÊÇ
a b 1981
ºÍ
b a 1981
ÊÇҪȥµôµÄ¡£¿ÒÇë¸ßÊÖ°ï棿

---

°¡£¬ÉÏÃæд´íÁË

---

¹þ¹þ£¬ÎÒ×Ô¼ºÒ²´í£¬ºÃ¶àÈ˶¼¸ã´í£¡

---

select a.name,b.name,a.birth,a.birth-1980  
from #tb a left join #tb b on a.name<b.name 
and a.birth=b.birth 
where b.name is not null

---

Óà < »ò >ÕâÑù±ÜÃâ³öÏÖ a£¬b b£¬aµÄÇé¿ö

---

»¹ÊDz»¶Ô°¡£¬ÄãÔõô²»×Ô¼ºÔËÐÐÏÂÄØ

---

½¨±íÓï¾ä

create table TESTJ
(
  NAME VARCHAR2(10),
  YEAR VARCHAR2(20),
  BIRTH VARCHAR2(10)
)

---

¶÷£¬¶ÔÁË£¬Ð»Ð»Ä㣡