sql²éѯÓï¾ä

ÎÒÏÖÔÚÓÐÁ½¸ö±í
SA£ºnumber date
  1 2010-1-4
  2 2010-2-8
  3 2010-5-9
  4 2008-5-6
  5 2009-6-5
  6 2008-4-6 
  7 2008-4-6
  ¡­¡­ ¡­¡­
JA£ºnumber date
  1 2010-3-4
  2 2010-4-8
  3 2010-5-9
  4 2008-7-6
  5 2009-8-5
  6 2008-9-6 
  7 2008-6-6
  ¡­¡­ ¡­¡­
ÉÏÃæÒ»¸öÊÇÊÜ°¸±í£¬Ò»¸öÊǽ᰸±í¡£
ÏÖÔÚÎÒҪͳ¼ÆÿһÄêµÄÿ¸öÔ·ݣ¬ÊÜ°¸£¬½á°¸¸÷¶àÉÙ£¬¸ÃÔõôд²éѯÓï¾äÄØ£¿
ҪʵÏֵıíÈçÏ£º
  date SAcount JAcount
  2010-1 1 0
  2010-2 1 0
  2010-3 0 1
  ¡­¡­ ¡­¡­ ¡­¡­

---

»ù±¾ÉÏ¿ÉÒÔÕâÑùд£¬ÓÐЩϸ½Ú²»·ûÄã¿É×ÔÐе÷Õû
SELECT 
 substring(CONVERT(char(10), JA.[date], 23) ,1,7) as [date]
 ,SUM(SA.number) as SAcount 
 ,SUM(JA.number) as JAcount 
from SA
inner join JA on substring(CONVERT(char(10), SA.[date], 23) ,1,7)=substring(CONVERT(char(10), JA.[date], 23) ,1,7)
group by substring(CONVERT(char(10), JA.[date], 23) ,1,7)

---

SQL code

---

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHi