如何得到我所要求的结果呢？多几种方式或者多条语句也行
select Job_id,Job_name,Com_id,Com_name,startdate
from ed_job order by startdate desc
------------------------------------
947文员/前台接待4517aaaaaaaaa公司2007-04-26 11:10:04.000
949电脑员4517aaaaaaaaa公司2007-04-26 11:10:04.000
950储备干部4517aaaaaaaaa公司2007-04-26 11:10:04.000
941文员4509bbbbbbb公司2007-04-26 10:05:19.000
987营业员4509bbbbbbb公司2007-04-26 10:05:19.000
988电脑员4509bbbbbbb公司2007-04-26 10:05:19.000
989储干4509bbbbbbb公司2007-04-26 10:05:19.000
990送货员4509bbbbbbb公司2007-04-26 10:05:19.000
991手机学徒4509bbbbbbb公司2007-04-26 10:05:19.000
960客服经理4536cccccccccccc集团2007-04-26 09:39:52.000
961客服专员4536cccccccccccc集团2007-04-26 09:39:52.000
998采购文员/跟单文员4604 eeeeeeeeee会社2007-04-25 15:31:54.000
993商务、外贸专员4571fffffffffff公司2007-04-22 17:55:25.000
994储备干部4571fffffffffff公司2007-04-22 17:55:25.000
995营销业务专员4571fffffffffff公司2007-04-22 17:55:25.000
996文员4571fffffffffff公司2007-04-22 17:55:25.000
997各部门助理4571fffffffffff公司2007-04-22 17:55:25.000
986平面设计3999wwwwwwwww公司2007-04-19 22:44:55.000
331网页设计3999wwwwwwwww公司2007-04-19 22:44:55.000
362兼职商务代表3999wwwwwwwww公司2007-04-19 22:44:55.000
979仓管4540rrrrrrrrrrrr公司2007-04-17 10:29:05.000
980普工4540rrrrrrrrrrrr公司2007-04-17 10:29:05.000
不清楚前任谁这么设计的表，为什么要使用order by DateTime类型的字段，这样速度会很慢。而且，搞不懂为什么微秒都是0呢？
在这个职位表里面，先插入的数据（从日期可以看出来），但是它的Job_id这个主键值居然比后插入的数据的Job_id值还大。搞不清楚。
我希望得到下面的结果(每个公司查前三条，按照日期排序)：
Com_id Com_name Job_id1 Job_name1 Job_id2 Job_name2 Job_id3 Job_name3 startDate
4517 aaaaaaaaa公司 947 文员/前台接待 949 电脑员 950 储备干部 2007-04-26
... ...
4536 cccccccccccc集团 960 客服经理 961 客服专员 null null 2007-04-26
... ...
4540 rrrrrrrrrrrr公司 979 仓管 980 普工 null null 2007-04-17
通过不管多少条语句，存储过程，函数等，能够得到这种结果也行。
谢谢！
[解决办法]
select
Job_id,Job_name,Com_id,Com_name,startdate
from
ed_job t
where
t.Job_id in (select top 3 Job_id from ed_job where Com_id=t.Com_id order by startdate desc)
order by
t.startdate desc
------解决方案--------------------
create table t(Job_id varchar(20),Job_name varchar(100),Com_id varchar(20),Com_name varchar(100),startdate datetime)
insert t select '947 ', '文员/前台接待 ', '4517 ', 'aaaaaaaaa公司 ', '2007-04-26 11:10:04.000 '
union all select '949 ', '电脑员 ', '4517 ', 'aaaaaaaaa公司 ', '2007-04-26 11:10:04.000 '
union all select '950 ', '储备干部 ', '4517 ', 'aaaaaaaaa公司 ', '2007-04-26 11:10:04.000 '
union all select '951 ', '保安 ', '4517 ', 'aaaaaaaaa公司 ', '2007-04-26 11:10:04.000 '
union all select '960 ', '客服经理 ', '4536 ', 'cccccccccccc集团 ', '2007-04-26 09:39:52.000 '
union all select '961 ', '客服专员 ', '4536 ', 'cccccccccccc集团 ', '2007-04-26 09:39:52.000 '
union all select '950 ', '仓管 ', '4540 ', 'rrrrrrrrrrrr公司 ', '2007-04-17 10:29:05.000 '
union all select '980 ', '普工 ', '4540 ', 'rrrrrrrrrrrr公司 ', '2007-04-17 10:29:05.000 '
declare @s varchar(8000)
set @s= 'select Com_id,Com_name '
select @s=@s+ ',max(case bh when ' ' '+cast(bh as varchar)+ ' ' ' then Job_id end) as Job_id_ '+cast(bh as varchar)+ ',
max(case bh when ' ' '+cast(bh as varchar)+ ' ' ' then Job_name end) as Job_name_ '+cast(bh as varchar)+ ' ' from
(select bh=(select count(*) from t where Com_id=a.Com_id and Job_id <=a.Job_id),* from t a
)b where bh <=3 group by bh
select @s
select @s=@s+ ' ,startDate=convert(varchar(10),startDate,120) from (select bh=(select count(*) from t where Com_id=a.Com_id and Job_id <=a.Job_id),* from t a
)b group by Com_id,Com_name,startDate order by startDate desc '
exec(@s)
4517aaaaaaaaa公司947文员/前台接待949电脑员950储备干部2007-04-26
4536cccccccccccc集团960客服经理961客服专员NULLNULL2007-04-26
4540rrrrrrrrrrrr公司950仓管980普工NULLNULL2007-04-17
