具有默认值的SQL查询 [英] sql query with default value

问题描述

我有一个包含一些列的表员工。

我想为那些Job值为Manager的记录设置Commission列的默认值为1000。或者换句话说，如果Job值是'Manager'，则将Commission的默认值设置为'1000'。我怎么能用sql查询呢？

解决方案

你好。



如果存在一个表格description

  CREATE   TABLE  TBL_EMPLOYEES（
 EMPLOYEE_ID  INT   NOT   NULL  < span class =code-keyword> IDENTITY  
，NAME  VARCHAR （ 300 ）
，JOB  VARCHAR （ 50 ）
，COMISSION  FLOAT  
， CONSTRAINT  PK_EMPLOYEE  PRIMARY   KEY （EMPLOYEE_ID）
）

和

 当运行数据选择时 获得

  SELECT  *  FROM  TBL_EMPLOYEES 

 EMPLOYEE_ID NAME JOB COMISSION 
 1 JOHN MANAGER NULL 
 2 CLARK SALES NULL 

查询具有条件的数据

  SELECT  
 EMPLOYEE_ID 
，NAME 
，JOB 
，  CASE  
  WHEN  JOB = '  MANAGER' 那么  1000  
  ELSE  COMISSION 
  END  COMISSION 
  FROM  TBL_EMPLOYEES 

这就是结果：

 EMPL OYEE_ID NAME JOB COMISSION 
 1 JOHN MANAGER 1000 
 2 CLARK SALES NULL 

问候......

I have a table employees with some columns.
I want to set the default value of Commission column to 1000 for those records whose Job value is Manager. Or in other words , if the Job value is ' Manager' then set the default value of Commission to '1000'. How can i do this using sql query?

解决方案

Hi.

If exists a table with this description

CREATE TABLE TBL_EMPLOYEES(
	EMPLOYEE_ID INT NOT NULL IDENTITY
	, NAME VARCHAR(300)
	, JOB VARCHAR(50)
	, COMISSION FLOAT
	, CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID)
)

And

when you run the data selection is obtained

SELECT * FROM TBL_EMPLOYEES

EMPLOYEE_ID	NAME	JOB	       COMISSION
1	        JOHN	MANAGER	   NULL
2	        CLARK	SALES	   NULL

The query for data with the conditions set forth

SELECT 
EMPLOYEE_ID
, NAME
, JOB
, CASE 
	WHEN JOB = 'MANAGER' THEN 1000
	ELSE COMISSION	
	END COMISSION
FROM TBL_EMPLOYEES

And this is the result:

EMPLOYEE_ID	NAME	JOB	       COMISSION
1	        JOHN	MANAGER	   1000
2	        CLARK	SALES	   NULL

Regards...

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