示例选择SQL查询 [英] sample select SQL query

问题描述

Hii



i有桌子[员工]

它有3列

===== =====================

emp_id int pk

emp_name nvarchar（50）

薪水int

==========================

i需要查询为此：



i需要检索员工姓名以及他所属的级别



ex：

1000到2000所以它是1级

2100到3000所以它是2级

3100到4000所以它是3级

==========================



所以结果如下：



1杰克等级2

2克里斯等级3

3 jonny等级1



我该怎么办？

解决方案

您需要了解 CASE 语句 - 这是一篇关于该主题的文章阿斯使用SQL CASE表达式 [ ^ ]

您还需要了解 BETWEEN 子句 - 这是参考 [ ^ ]

根据您的预期结果，您最终会得到类似

 < span class =code-keyword>选择 emp_id，emp_name，
  CASE  何时 salary  BETWEEN   1000   AND   2000  那么  1  
  WHEN 薪水 BETWEEN   2100   AND   3000  那么  2  
  WHEN  salary  BETWEEN   3100   AND   4000  那么  3  
  ELSE   0   END   AS  level 

请注意，我已经引入了level = 0的默认情况



我个人认为你可能意味着你的范围是1000-2000， 2001 -3000和 3001 -4000 - 除非你故意在它们之间留下100个单位的间隙




  DECLARE  i  INT ; 
 
  SET  i = 1000; 
 
  SELECT  EMP_ID，
 EMP_NAME，
（SALARY / I）+  1   AS 级别
  FROM  EMPLOYEE 




试试这个：

  SELECT  emp_id，emp_name，
  CASE  当 salary<  2100  那么 '  1级 
  WHEN  salary<  3100  那么 '  2级 
  ELSE  '  level 3'  END  
  FROM  table1 

Hii

i have table [ employee ]
it has 3 columns
==========================
emp_id int pk
emp_name nvarchar(50)
salary int
==========================
i need a query for this :

i need to retrieve name of employee and in which level he belongs to

ex :
1000 to 2000 so it is level 1
2100 to 3000 so it is level 2
3100 to 4000 so it is level 3
==========================

so the result will be as below

1 Jack level2
2 chris level3
3 jonny level1

how can i do that ?

解决方案

You need to know about the CASE statement in SQL - here is an article on that subject A Simple Use of SQL CASE Expression[^]
You also need to know about the BETWEEN clause - here is the reference[^]
Based on your expected results you will end up with something like

Select emp_id, emp_name,
CASE when salary BETWEEN 1000 AND 2000 THEN 1
WHEN salary BETWEEN 2100 AND 3000 THEN 2
WHEN salary BETWEEN 3100 AND 4000 THEN 3
ELSE 0 END AS level

Note that I've introduced a default situation of level=0

Personally I think you might have meant your ranges to have been 1000-2000, 2001-3000 and 3001-4000 - unless you deliberately meant to leave a 100 unit gap between them

DECLARE i INT;

SET i=1000;

SELECT EMP_ID,
       EMP_NAME,
       ( SALARY / I ) + 1 AS level
FROM   EMPLOYEE

Try this:

SELECT emp_id, emp_name,
CASE when salary < 2100 THEN 'level 1'
WHEN salary  < 3100 THEN 'level 2'
ELSE 'level 3' END
FROM table1

这篇关于示例选择SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！