在一个表中 在字段MEMBER_ID 下存在着相同的ID，只是字段的LAST_COACH 字段（类型是datetime)的值不一样。现在需要取出每个ID的情况下唯一的一个datetime 字段。我写的SQL语句是：
SELECT DISTINCT A.[COACH_MEMBER_ID],[LAST_NAME],[FIRST_NAME],[PREF_NAME],[SIGNUP_DT],[SESSION_DT],[MOBILE] FROM [COACH_MEMBER] A INNER JOIN [COACH_SESSION] B ON A.[COACH_MEMBER_ID]=B.[COACH_MEMBER_id] WHERE A.[status]='M02' and DATEDIFF(day,SIGNUP_DT,GETDATE())>31 and SESSION_DT IS NULL or DATEDIFF(day,SESSION_DT,GETDATE())<31 and A.[status]='M02'
显示结果是
如图，需要取得name为martti 的LASCOACH 字段距离最近的值，其他的不显示。
------解决思路----------------------
抛开你那where条件（业务逻辑的判断）不看，光看你的需求，用distinct应该可以的。或者你再看看，是否是你需求没说清楚。
------解决思路----------------------
select top 1 LASCOACH from table where name='martti ' order by LASCOACH desc
------解决思路----------------------
SQL和描述根本对补上，就给个思路吧：
SELECT ...
FROM ... a
JOIN ( -- 求出唯一的 MEMBER_ID、LAST_COACH，再去关联
SELECT MEMBER_ID, MAX(LAST_COACH) LAST_COACH
FROM table1
GROUP BY MEMBER_ID
) b
ON a.MEMBER_ID = b.MEMBER_ID
WHERE ...
------解决思路----------------------
不能用 DISTINCT，按照我#4的方式，对 SEESION_DT 分组求 MAX()。
------解决思路----------------------
SELECT [coach_member_id],
[last_name],
[first_name],
[pref_name],
[signup_dt],
[mobile],
Datediff(DAY,signup_dt,Getdate()) AS waiting
FROM coach_member a
JOIN ( SELECT member_id,
MAX(last_coach) AS last_coach --这里已改正
FROM coach_session
GROUP BY member_id
) b
ON a.member_id = b.member_id
WHERE b.session_dt IS NULL
AND Datediff(DAY,cm.signup_dt,Getdate()) > 30 -- cm 在哪里
OR Datediff(DAY,b.session_dt,Getdate()) < 30 -- b 现在只有两个字段，需要 session_dt 就得在子查询中选取