select am.*,ap.* from AS_Main am inner join AS_Position ap
on am.AS_PositionID=ap.ID inner join (
select min(as_id) as_id,AS_PositionID from AS_Main
)c
on am.as_id=c.as_id
order by ap.AS_Status,DATEDIFF(DD,AS_StartDate,AS_EndDate)
on am.AS_PositionID=ap.ID inner join (
select min(as_id) as_id,AS_PositionID from AS_Main
)c
on am.as_id=c.as_id
order by ap.AS_Status,DATEDIFF(DD,AS_StartDate,AS_EndDate)
相同如其他的字段的值有不同的,则不会进行过滤,你可以用聚合函数如max,min...
Select m.AS_PositionID,max(m.AS_ID) as AS_ID,....,...
From AS_Main m,AS_Position p
Where m.AS_PositionID=p.ID order By p.AS_Status,
DateDiff(dd,m.AS_StartDate,m.AS_EndDate)
group by m.AS_PositionID
提示如下:
列 'AS_Main.AS_PositionID' 在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。
GROUP BY 能放在后面吗、? 我要显示出数据有字段是 VARCHAR 怎么能用聚合函数呢?
--4 UT斯达康
应该是两个不同的行
AS_PositionID 唯一不知道有什么意义
谢谢兄弟 我的思路如下:****补充如下: 我想得到数据是 判断时间 如果 AS_EndDate > getdate() 则 取最近的一条记录
否则 则 赋值此记录 为空值··
order By max(p.AS_Status),
DateDiff(dd,max(m.AS_StartDate),max(m.AS_EndDate))---------------- group by 在order by 之前
--没测试,你试试
select AS_Position.ID,AS_Position.AS_PCode,b.*from AS_Position, (select AS_ID , AS_userID, AS_PositionID , AS_PicName , AS_StartDate , AS_EndDate ,
from AS_Main,
(select min(As_id),AS_PositionID from AS_Main group by as_positionID) a
where AS_Main.as_id = a.as_id
) b
where AS_Position.id = b.AS_PositionID
AS_PositionID ,maxAS_PicName , AS_StartDate , AS_EndDate from AS_Main group by AS_PicName , AS_StartDate , AS_EndDate
--没测试,你试试
select AS_Position.ID,AS_Position.AS_PCode,b.*from AS_Position, (select AS_ID , AS_userID, AS_PositionID , AS_PicName , AS_StartDate , AS_EndDate ,
from AS_Main,
(select min(As_id),AS_PositionID from AS_Main group by as_positionID) a
where AS_Main.as_id = a.as_id
) b
where AS_Position.id = b.AS_PositionID
and 其它条件--上面多了几行----
Select m.AS_ID,m.AS_userID,m.AS_PositionID,m.AS_PicName,
m.AS_StartDate,m.AS_EndDate,m.AS_SrcType,m.AS_CountClick,
m.AS_ShowCount,p.ID,p.AS_PCode,p.AS_Status
From AS_Main m,AS_Position p
Where m.AS_PositionID=p.ID and m.AS_ID IN
(Select AS_ID From AS_Main Where AS_EndDate IN
(Select Max(m.AS_EndDate) From AS_Main m,AS_Position p
Where m.AS_PositionID=p.ID group by m.AS_PositionID)) order By AS_Status,DateDiff(dd,AS_StartDate,AS_EndDate)