CREATE TABLE `tb_employee` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `emp_code` int unsigned NOT NULL DEFAULT '0' COMMENT '員工編碼',
  `emp_name` varchar(20) NOT NULL DEFAULT '' COMMENT '員工姓名',
  `gender` char(1) NOT NULL DEFAULT '1' COMMENT '性別(1:男0:女)',
  `dep_code` int NOT NULL DEFAULT '0' COMMENT '部門(mén)',
  `job` varchar(20) NOT NULL DEFAULT '' COMMENT '工作',
  `age` tinyint NOT NULL DEFAULT '0' COMMENT '年齡',
  `salary` double(8,2) NOT NULL DEFAULT '0.00' COMMENT '工資',
  `hire_date` date DEFAULT NULL COMMENT '入職時(shí)間',
  `manage_code` int DEFAULT NULL COMMENT '所屬領(lǐng)導(dǎo)',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
  PRIMARY KEY (`id`),
  KEY `idx_emp_code` (`emp_code`),
  KEY `idx_manage_code` (`manage_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='員工信息表';

CASE '字段名' 
	 WHEN '字段值1' THEN '結(jié)果1' 
     WHEN '字段值2' THEN '結(jié)果2'
     WHEN '字段值3' THEN '結(jié)果3'
     ELSE '其他結(jié)果'
END 

SELECT 
	emp_code AS '員工編號(hào)',
	emp_name AS '員工姓名',
	(CASE gender
		WHEN 1 THEN '男'
	    WHEN 0 THEN '女'
	    ELSE '未知'
	END) AS '性別',
	salary AS '工資'
FROM tb_employee;

CASE WHEN '表達(dá)式1' THEN '結(jié)果1' 
     WHEN '表達(dá)式2' THEN '結(jié)果2'
     WHEN '表達(dá)式3' THEN '結(jié)果3'
     ELSE '其他結(jié)果'
END

SELECT 
	emp_code AS '員工編號(hào)',
	emp_name AS '員工姓名',
	(CASE 
		WHEN gender=1 THEN '男'
	    WHEN gender=0 THEN '女'
	    ELSE '未知'
	END) AS '性別',
	salary AS '工資'
FROM tb_employee;

 SELECT 
	emp_code AS '員工編號(hào)',
	emp_name AS '員工姓名',
    salary AS '員工工資',
	(CASE 
		WHEN salary < 3000 THEN '入門(mén)級(jí)'
		WHEN salary >= 3000 AND salary < 15000 THEN '初級(jí)'
        WHEN salary >= 15000 AND salary < 25000 THEN '中級(jí)'
	    WHEN salary >= 25000 AND salary < 50000 THEN '高級(jí)'
	    ELSE '特級(jí)'
	END) AS '工資級(jí)別'
FROM tb_employee;

SELECT 
	(CASE 
		WHEN salary < 3000 THEN '入門(mén)級(jí)'
		WHEN salary >= 3000 AND salary < 15000 THEN '初級(jí)'
        WHEN salary >= 15000 AND salary < 25000 THEN '中級(jí)'
	    WHEN salary >= 25000 AND salary < 50000 THEN '高級(jí)'
	    ELSE '特級(jí)'
	END) as 'levels',
    count(*) AS '總?cè)藬?shù)'
FROM tb_employee
GROUP BY levels;

SELECT 
	dep_code AS '部門(mén)編號(hào)',
	(CASE 
		WHEN dep_code=10 THEN '總經(jīng)辦'
		WHEN dep_code=11 THEN '財(cái)務(wù)'
		WHEN dep_code=12 THEN '技術(shù)'
		WHEN dep_code=13 THEN '測(cè)試'
		WHEN dep_code=14 THEN '運(yùn)維'
		ELSE '其他'
		END) AS '部門(mén)',
	(CASE 
		WHEN salary < 3000 THEN '入門(mén)級(jí)'
		WHEN salary >= 3000 AND salary < 15000 THEN '初級(jí)'
        WHEN salary >= 15000 AND salary < 25000 THEN '中級(jí)'
	    WHEN salary >= 25000 AND salary < 50000 THEN '高級(jí)'
	    ELSE '特級(jí)'
	END) AS 'levels',
    count(*) as '總?cè)藬?shù)'
FROM tb_employee
GROUP BY dep_code,levels;

SELECT 
	dep_code AS '部門(mén)編號(hào)',
	(CASE 
		WHEN dep_code=10 THEN '總經(jīng)辦'
		WHEN dep_code=11 THEN '財(cái)務(wù)'
		WHEN dep_code=12 THEN '技術(shù)'
		WHEN dep_code=13 THEN '測(cè)試'
		WHEN dep_code=14 THEN '運(yùn)維'
		ELSE '其他'
	END) AS '部門(mén)',
    SUM((CASE WHEN gender = 1 THEN 1 ELSE 0 END)) AS '男生人數(shù)',
    SUM((CASE WHEN gender = 0 THEN 1 ELSE 0 END)) AS '女生人數(shù)',
    COUNT(*) AS '部門(mén)總?cè)藬?shù)'
FROM
    tb_employee
GROUP BY dep_code;

SELECT 
    dep_code AS '部門(mén)編號(hào)',
	(CASE 
		WHEN dep_code=10 THEN '總經(jīng)辦'
		WHEN dep_code=11 THEN '財(cái)務(wù)'
		WHEN dep_code=12 THEN '技術(shù)'
		WHEN dep_code=13 THEN '測(cè)試'
		WHEN dep_code=14 THEN '運(yùn)維'
		ELSE '其他'
	END) AS '部門(mén)',
	SUM(salary) AS '總工資',
    SUM((CASE WHEN salary <= 3000 THEN salary ELSE 0 END)) AS '入門(mén)總工資',
    SUM((CASE WHEN salary > 3000 AND salary < 15000 THEN salary ELSE 0 END)) AS '初級(jí)總工資',
    SUM((CASE WHEN salary >= 15000 AND salary < 25000 THEN salary ELSE 0 END)) AS '中級(jí)總工資',
    SUM((CASE WHEN salary >= 25000 AND salary <= 50000 THEN salary ELSE 0 END)) AS '高級(jí)總工資',
    SUM((CASE WHEN salary > 50000 THEN salary ELSE 0 END)) AS '特級(jí)總工資'
FROM
    tb_employee
GROUP BY dep_code;

SELECT 
    dep_code AS '部門(mén)編號(hào)',
	(CASE 
		WHEN dep_code=10 THEN '總經(jīng)辦'
		WHEN dep_code=11 THEN '財(cái)務(wù)'
		WHEN dep_code=12 THEN '技術(shù)'
		WHEN dep_code=13 THEN '測(cè)試'
		WHEN dep_code=14 THEN '運(yùn)維'
		ELSE '其他'
	END) AS '部門(mén)',
	COUNT(*) AS '總?cè)藬?shù)',
	SUM(salary) AS '總工資',
    SUM((CASE WHEN salary <= 3000 THEN 1 ELSE 0 END)) AS '入門(mén)總?cè)藬?shù)',
    SUM((CASE WHEN salary > 3000 AND salary < 15000 THEN 1 ELSE 0 END)) AS '初級(jí)總?cè)藬?shù)',
    SUM((CASE WHEN salary >= 15000 AND salary < 25000 THEN 1 ELSE 0 END)) AS '中級(jí)總?cè)藬?shù)',
    SUM((CASE WHEN salary >= 25000 AND salary <= 50000 THEN 1 ELSE 0 END)) AS '高級(jí)總?cè)藬?shù)',
    SUM((CASE WHEN salary > 50000 THEN 1 ELSE 0 END)) AS '特級(jí)總?cè)藬?shù)',
    SUM((CASE WHEN salary <= 3000 THEN salary ELSE 0 END)) AS '入門(mén)總工資',
    SUM((CASE WHEN salary > 3000 AND salary < 15000 THEN salary ELSE 0 END)) AS '初級(jí)總工資',
    SUM((CASE WHEN salary >= 15000 AND salary < 25000 THEN salary ELSE 0 END)) AS '中級(jí)總工資',
    SUM((CASE WHEN salary >= 25000 AND salary <= 50000 THEN salary ELSE 0 END)) AS '高級(jí)總工資',
    SUM((CASE WHEN salary > 50000 THEN salary ELSE 0 END)) AS '特級(jí)總工資'
FROM
    tb_employee
GROUP BY dep_code;

UPDATE tb_employee 
SET 
    salary = (CASE
        WHEN salary <= 3000 THEN salary + 400
        WHEN salary > 3000 AND salary <= 15000 THEN salary * 1.2
        WHEN salary > 15000 AND salary < 25000 THEN salary * 1.1
        WHEN salary > 50000 THEN salary * 0.9
        ELSE salary
    END)
where dep_code > 10;

SELECT 
	tranSeq as '交易流水', 
	(CASE 
		WHEN tranSeq IN (SELECT tranSeq FROM tb_outside_record) THEN '匹配' 
		ELSE '未匹配' 
	END) as '是否匹配' 
FROM tb_local_record; 

SELECT 
	lr.tranSeq as '交易流水', 
	(CASE 
		WHEN EXISTS (SELECT osr.tranSeq FROM tb_outside_record osr 
					WHERE  osr.tranSeq = lr.tranSeq) THEN '匹配' 
		ELSE '未匹配' 
	END) as '是否匹配' 
FROM tb_local_record lr;