比如有如下数据:
ItemID ToolModule PmType SetDate T TFlag
201100201014 DWPRS07@AllModule W_P 2011-11-28 7 AY
201100201015 DWPRS07@AllModule W_P 2011-12-5 7 AY
201100201016 DWPRS07@AllModule W_P 2011-12-12 7 AY
201100201017 DWPRS07@AllModule W_P 2011-12-19 7 AY
201100201018 DWPRS07@AllModule W_P 2011-12-26 7 AY
201200201001 DWPRS07@AllModule M_P 2012-1-2 7 AY
201100212001 DWPRS07@AllModule M_P 2011-8-29 30 AY
201100212002 DWPRS07@AllModule M_P 2011-9-28 30 AY
201100212003 DWPRS07@AllModule Q_P 2011-10-28 30 AY
201100212004 DWPRS07@AllModule Q_P 2011-11-27 30 AY通过 搜索语句搜索到:
ItemID ToolModule PmType SetDate T TFlag
201100201014 DWPRS07@AllModule W_P 2011-11-28 7 AY
201200201001 DWPRS07@AllModule M_P 2012-1-2 7 AY
201100212003 DWPRS07@AllModule Q_P 2011-10-28 30 AY如何写这个语句呢...
(PmType为不同的,SetDate取 最小日期的数据,)
ItemID ToolModule PmType SetDate T TFlag
201100201014 DWPRS07@AllModule W_P 2011-11-28 7 AY
201100201015 DWPRS07@AllModule W_P 2011-12-5 7 AY
201100201016 DWPRS07@AllModule W_P 2011-12-12 7 AY
201100201017 DWPRS07@AllModule W_P 2011-12-19 7 AY
201100201018 DWPRS07@AllModule W_P 2011-12-26 7 AY
201200201001 DWPRS07@AllModule M_P 2012-1-2 7 AY
201100212001 DWPRS07@AllModule M_P 2011-8-29 30 AY
201100212002 DWPRS07@AllModule M_P 2011-9-28 30 AY
201100212003 DWPRS07@AllModule Q_P 2011-10-28 30 AY
201100212004 DWPRS07@AllModule Q_P 2011-11-27 30 AY通过 搜索语句搜索到:
ItemID ToolModule PmType SetDate T TFlag
201100201014 DWPRS07@AllModule W_P 2011-11-28 7 AY
201200201001 DWPRS07@AllModule M_P 2012-1-2 7 AY
201100212003 DWPRS07@AllModule Q_P 2011-10-28 30 AY如何写这个语句呢...
(PmType为不同的,SetDate取 最小日期的数据,)
from tablename
group by ItemID,ToolModule,PmType,T,TFlag
人家的ItemID要最小的,不是要每个都列出来
from tablename)
group by ToolModule,PmType,T,TFlag) AS A ON
tablename.ToolModule=A.ToolModule AND
tablename.PmType=A.PmType AND
tablename.T=A.T AND
tablename.TFlag=A.TFlag AND
tablename.SetDate=A.SetDateA
from tb as a
, (select min(SetDate) as MinSetDate,PmType from tb group by PmType) as b
where a.PmType=b.PmType
and a.SetDate=b.MinSetDate