select p.personID,p.userID,p.P_Name,p.P_sex,p.edittime,p.P_birthday,tem.speciality,tem.edu_level, tem.School from dbo.Person p inner join
(select UserID, speciality,edu_level, School
FROM dbo.P_Education group by UserID, speciality,edu_level, School
Having edu_level = MAX(edu_level)
) tem ON p.UserID = tem.UserID
(select UserID, speciality,edu_level, School
FROM dbo.P_Education group by UserID, speciality,edu_level, School
Having edu_level = MAX(edu_level)
) tem ON p.UserID = tem.UserID
dbo.Person.P_Sex, dbo.Person.edittime, dbo.Person.P_Birthday, tem.speciality,tem.edu_level, tem.School
FROM dbo.Person INNER JOIN
(SELECT dbo.P_Education.UserID, dbo.P_Education.speciality,
dbo.P_Education.edu_level, dbo.P_Education.School
FROM dbo.P_Education
WHERE dbo.P_Education.edu_level =
(SELECT MAX(dbo.P_Education.edu_level)
FROM dbo.P_Education WHERE dbo.P_Education.UserID = dbo.Person.UserID)) ON dbo.Person.UserID = dbo.P_Education.UserID
不同学历是的用户是能显示了!
但是当一个用户有两个学历是一样的时候
结果就显示两个该用户信息!目的:要求只显示每个用户只显示一条信息
and 主键=max(主键)
P.PersonID,
P.UserID,
P.P_Name,
P.P_Sex,
P.edittime,
P.P_Birthday,
E_temp.speciality,
E_temp.edu_level,
E_temp.School
FROM
dbo.Person P INNER JOIN
(
SELECT
E.UserID,
E.speciality,
E.edu_level,
E.School
FROM
dbo.P_Education E,
(
SELECT
E_max.UserID,
MAX(E_max.edu_level) max_lv
FROM
dbo.P_Education E_max
GROUP BY
E_max.UserID
) E_temp
WHERE
E.UserID = E_temp.UserID AND
E.edu_level = E_temp.max_lv
)
ON P.UserID = E_temp.UserID由于不知道主键情况和数据匹配情况先写一个看看吧
对库表结构的要求是在dbo.P_Education中UserID和edu_level应能基本确定一条记录
重发! :)SELECT
P.PersonID,
P.UserID,
P.P_Name,
P.P_Sex,
P.edittime,
P.P_Birthday,
EDU.speciality,
EDU.edu_level,
EDU.School
FROM
dbo.Person P INNER JOIN
(
SELECT
E.UserID,
E.speciality,
E.edu_level,
E.School
FROM
dbo.P_Education E,
(
SELECT
E_max.UserID,
MAX(E_max.edu_level) max_lv
FROM
dbo.P_Education E_max
GROUP BY
E_max.UserID
) E_temp
WHERE
E.UserID = E_temp.UserID AND
E.edu_level = E_temp.max_lv
) EDU
ON P.UserID = EDU.UserID
感谢 murphyxiao给分!
结贴!