SQL应用小技巧

/* 用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