从SQL查询中获得更高的教育 [英] Get higher education from SQL query

问题描述

我有用户列表



其中我有用户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

首先要注意的是你没有使用两个表：你的实际数据应该是

leveltbl

The first thing to note is that you aren't using both tables: Your actual data should be
leveltbl

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查询中获得更高的教育的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！