数据查询求教，请大侠帮忙，急

[icanplay2012]

最近在做一个项目，遇到一个麻烦的查询，不知道如何实现，请高手指教下。表结构和示例数据如下：

ID Name Action ActionDate Operater
90 aaa 1 2014/11/1 op1
90 aaa 2 2014/11/2 op2
90 aaa 3 2014/11/3 op3
90 aaa 1 2014/11/4 op4
90 aaa 2 2014/11/5 op5
90 aaa 3 2014/11/6 op6
91 bbb 1 2014/11/1 op7
91 bbb 2 2014/11/2 op8
91 bbb 3 2014/11/3 op9
91 bbb 1 2014/11/4 op10
91 bbb 2 2014/11/5 op11
91 bbb 3 2014/11/6 op12
92 ccc 1 2014/11/5 op13
93 ddd 3 2014/11/6 op14

其中Action带表一些操作，分别有1、2、3三种。现在需要查询出每个文件每种操作类型的操作日期最新的记录，期望结果如下，其中ActionDate1代表操作1的最新日期，Operater1代表最新类型1最新操作人，其他同理：

ID Name ActionDate1 ActionDate2 ActionDate3 Operater1 Operater2 Operater3
90 aaa 2014/11/4 2014/11/5 2014/11/6 op4 op5 op6
91 bbb 2014/11/4 2014/11/5 2014/11/6 op10 op11 op12
92 ccc 2014/11/5 NULL NULL op13 NULL NULL
93 ddd NULL NULL 2014/11/6 NULL NULL op14

[ACMAIN_CHM]

select ID,Name,
	(select ActionDate from 表结构和示例 where ID=t.ID and Action=1 order by ActionDate desc limit 1) as ActionDate1,
	(select ActionDate from 表结构和示例 where ID=t.ID and Action=2 order by ActionDate desc limit 1) as ActionDate2,
	(select ActionDate from 表结构和示例 where ID=t.ID and Action=3 order by ActionDate desc limit 1) as ActionDate3,
	(select Operater from 表结构和示例 where ID=t.ID and Action=1 order by ActionDate desc limit 1) as Operater1,
	(select Operater from 表结构和示例 where ID=t.ID and Action=2 order by ActionDate desc limit 1) as Operater2,
	(select Operater from 表结构和示例 where ID=t.ID and Action=3 order by ActionDate desc limit 1) as Operater3
	
from 表结构和示例 t
group by ID

[icanplay2012]

引用 1 楼 ACMAIN_CHM 的回复:

select ID,Name,
	(select ActionDate from 表结构和示例 where ID=t.ID and Action=1 order by ActionDate desc limit 1) as ActionDate1,
	(select ActionDate from 表结构和示例 where ID=t.ID and Action=2 order by ActionDate desc limit 1) as ActionDate2,
	(select ActionDate from 表结构和示例 where ID=t.ID and Action=3 order by ActionDate desc limit 1) as ActionDate3,
	(select Operater from 表结构和示例 where ID=t.ID and Action=1 order by ActionDate desc limit 1) as Operater1,
	(select Operater from 表结构和示例 where ID=t.ID and Action=2 order by ActionDate desc limit 1) as Operater2,
	(select Operater from 表结构和示例 where ID=t.ID and Action=3 order by ActionDate desc limit 1) as Operater3
	
from 表结构和示例 t
group by ID

谢谢你的回答，辛苦了o(^▽^)o 再请问下如果数据量很大上百万的时候，这个查询效率如何？

[ACMAIN_CHM]

引用 2 楼 icanplay2012 的回复:

[quote=引用 1 楼 ACMAIN_CHM 的回复:]

select ID,Name,
	(select ActionDate from 表结构和示例 where ID=t.ID and Action=1 order by ActionDate desc limit 1) as ActionDate1,
	(select ActionDate from 表结构和示例 where ID=t.ID and Action=2 order by ActionDate desc limit 1) as ActionDate2,
	(select ActionDate from 表结构和示例 where ID=t.ID and Action=3 order by ActionDate desc limit 1) as ActionDate3,
	(select Operater from 表结构和示例 where ID=t.ID and Action=1 order by ActionDate desc limit 1) as Operater1,
	(select Operater from 表结构和示例 where ID=t.ID and Action=2 order by ActionDate desc limit 1) as Operater2,
	(select Operater from 表结构和示例 where ID=t.ID and Action=3 order by ActionDate desc limit 1) as Operater3
	
from 表结构和示例 t
group by ID

谢谢你的回答，辛苦了o(^▽^)o 再请问下如果数据量很大上百万的时候，这个查询效率如何？[/quote]效率要看你表中索引的设计。

[icanplay2012]

引用 3 楼 wwwwb 的回复:

select ID, Name,max(if(Action=1,ActionDate,'')), max(if(Action=1,Operater,'')), .... from tt group by ID, Name

谢谢，这个方法可行

[wwwwb]

select ID, Name,max(if(Action=1,ActionDate,'')), max(if(Action=1,Operater,'')), .... from tt group by ID, Name