问题描述
我有用户列表
其中我有用户ID，用户名，教育程度，级别，级别详情
leveltbl
i have user list
where userid, username , education , level ,levelid details i have
leveltbl
b.c.a 1
m.c.a 2
phd 3
用户
Users
1 jaydeep b.c.a 1
2 jaydeep m.c.a 2
3 dhrumil b.c.a 1
4 jayanti PHD 3
5 jayanti M.C.a 2
我需要的输出（仅限用户接受高等教育）
what output i required (only user higher education)
2 jaydeep m.c.a 2
3 dhrumil b.c.a 1
4 jayanti PHD 3
我的尝试：
从用户中选择*，其中max（）;（不知道我在这做什么）
What I have tried:
select * from users where max( ) ;(dont know what i do here )
推荐答案
CREATE TABLE #TEMP(userid INT, username VARCHAR(50), education VARCHAR(10) , level INT);
INSERT INTO #TEMP
VALUES(1,'jaydeep','b.c.a',1),
(2,'jaydeep','m.c.a',2),
(3,'dhrumil','b.c.a',1),
(4,'jayanti','PHD', 3),
(5,'jayanti','M.C.a',2);
WITH CTE AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY USerNAme ORDER BY LEVEL DESC) lvl
FROM #temp
)
SELECT userid, username,education,level
FROM CTE
WHERE LVL=1 ORDER BY USERID ;
--------------------------------------------
userid username education level
---------------------------------------------
2 jaydeep m.c.a 2
3 dhrumil b.c.a 1
4 jayanti PHD 3
Desc NVARCHAR
ID INT, PRIMARY KEY您拥有的是什么。
用户
Which is what you have.
Users
ID INT, probably IDENTITY, PRIMARY KEY
UName NVARCHAR使用第三个表连接它们：
UserEducation
With a third table to connect them:
UserEducation
ID INT, IDENTITY
UID INT, FOREIGN KEY to Users.ID
LID INT, FOREIGN KEY to LevelTbl.ID
Grade INT这样，你不存储重复信息 - 这既浪费空间，又使你的工作复杂化，因为它允许错误蔓延，就像它们在你的数据中一样：mca是否与MCa？
然后访问数据，你使用JOIN：
That way, you aren't storing duplicate information - which both wastes space, and complicates your job because it allows errors to creep in, as they do in your data: Is "m.c.a" the same as "M.C.a"?
Then to access the data, you use a JOIN:
SELECT u.UName, l.Desc, e.Grade FROM UserEducation e
JOIN Users u ON e.UID = u.ID
JOIN LevelTbl l ON e.LID = l.ID那给你你的原始数据，使用GROUP BY和MAX来获得你想要的结果非常简单。
That give you your "raw" data, and it's pretty simple to use GROUP BY and MAX to get exactly the result you want from that.
这篇关于从SQL查询中获得更高的教育的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！