/* 用EXCEL中的数据更新 SQL 中的表 */
UPDATE
Dest
set
GongShang = (select [money] from YL where YL.Name=Dest.Name)
where
Dest.Name in(select Name from YL)
UPDATE
Dest
SET
YiLiao = b.Money,
ShiYe = b.Money,
YangLao = b.Money,
GongShang = b.Money
FROM
Dest a,
OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source="D:\2014\15220申报(2014).xls";
User ID=;Password=; Extended properties=Excel 5.0')...sheet1$ b
WHERE
a.Name=b.Name
/* 查询表B中与表B工号、姓名相同的人 */
SELECT
B.*
FROM
OpenRowSet('Microsoft.Jet.OLEDB.4.0','EXCEL 5.0;HDR=yes;DataBase=F:\样例\20100711.xls;',sheet1$) AS A,
OpenRowSet('Microsoft.Jet.OLEDB.4.0','dBase 5.0;DataBase=F:\样例\','select * from GZZK1007.DBF') AS B
WHERE
A.GONGHAO=B.GONGHAO AND A.XINGMING=B.XINGMING
/* 计算个人所得税查询语句 */
SELECT
*,
CASE
WHEN 应发工资 > 3500 AND 应发工资 <= 5000 THEN (应发工资-3500)*0.03
WHEN 应发工资 > 5000 AND 应发工资 <= 8000 THEN (应发工资-3500)*0.1-105
ELSE 0 END 个人所得税
FROM
Salary
/* 查询less表比more表少哪些人 */
select
m.name
from
OpenRowSet('Microsoft.Jet.OLEDB.4.0','EXCEL 5.0;HDR=yes;DataBase=D:\TEST\more.xls;',Sheet1$) m
left join /* 方向很重要，人数多的表要作为被join的表 */
OpenRowSet('Microsoft.Jet.OLEDB.4.0','EXCEL 5.0;HDR=yes;DataBase=D:\TEST\less.xls;',Sheet1$) l
on
l.name=m.name
where
l.name is NULL
