Sql 2005: 有没有什么简便方法统计下述数据，谢谢

Question

Table:(company,staff,joindate) 
如果按company统计joindate<today(e,g. last) 和 joindate>=today(e,g. future)的人数，显示结果如下示：
company / last  / future
A     /3     /4
B     /1    /3  
......
请问有什么简便有效的方法.....谢谢！

Answer 1

select company,sum(case when joindate<today(e,g. last) then 1 else 0 end) as last,
sum(case when joindate>=today(e,g. future) then 1 else 0 end) as future
from [table]
group by company

Answer 2

CREATE TABLE tb
(
  company VARCHAR(100),
  staff VARCHAR(10),
  joindate datetime
)INSERT INTO tb 
SELECT 'A','aaa','2012-1-9' union ALL
SELECT 'A','bbb','2012-1-8' union ALL
SELECT 'A','ccc','2012-1-1' union ALL
SELECT 'A','ddd','2012-1-15' union ALL
SELECT 'B','eee','2012-1-6' union ALL
SELECT 'B','fff','2012-1-6' union ALL
SELECT 'B','hhh','2012-1-20'SELECT t1.company,
       t2.last,
       t3.future
FROM   (
           SELECT DISTINCT company
           FROM   tb
       ) t1
       LEFT JOIN (
                SELECT company,
                       COUNT(staff) AS LAST
                FROM   tb
                WHERE  joindate < GETDATE()
                GROUP BY
                       company
            ) t2
            ON  t1.company = t2.company
       LEFT JOIN (
                SELECT company,
                       COUNT(staff) AS future
                FROM   tb
                WHERE  joindate >= GETDATE()
                GROUP BY
                       company
            ) t3
            ON  t1.company = t3.company

Answer 3

sorry，忘了table 中staff 的joindate 可能有很多
如：
staff / joindate
a  /2011-01-05
a  /2011-10-01
..

Answer 4

重新表述：Table(Staff/Company/department/joindate)
测试数据：
Staff/Company/department/joindate
a/001/d1/2011-01-01
a/001/d2/2011-01-01
a/002/d3/2011-01-01
b/001/d1/2010-05-01
b/003/d4/2010-05-01
c/001/d1/2011-10-01
c/002/d3/2011-05-01
......Table:(staff,company,department,joindate)  
如果按company统计AAA: joindate<2011-01-01 和 BBB: joindate>=2011-01-01的人数，显示结果如下示：
Company / AAA /BBB
001/ 1 / 2
002/ 0 / 2
003/ 1 / 0
......