查询时生成序号 SELECT (SELECT Count([xlh].[aa]) AS AutoNum FROM xlh WHERE (((xlh.aa)<=xlh_Alias.aa));) AS 序号, xlh.aa FROM xlh AS xlh_Alias INNER JOIN xlh ON xlh_Alias.aa=xlh.aa ORDER BY xlh.aa;
多表sql查询 SELECT test.aa AS 第一个字段, test1.bb AS 第二个字段, test1.cc FROM test, test1 WHERE test.aa=test1.aa;
多表sql查询1 SELECT a.aa, b.bb, b.cc, b.cc*100 AS 合计 FROM test AS a, test1 AS b WHERE a.aa=b.aa;
多表sql查询排序 SELECT a.aa, b.bb, b.cc AS 第三个字段 FROM test AS a, test1 AS b WHERE a.aa=b.aa ORDER BY b.cc;
查询例子 SELECT a.dhhm FROM xl11a AS a, xl919 AS b WHERE a.dhhm=b.dhhm and aa<>"1"; 日期时间分隔符是#而不是引号 Select * From Tab1 Where [Date]>#2002-1-1#;
两个表关联修改多个字段 UPDATE chhl AS a, jbsj AS b SET a.fzr = b.fzr, a.gh = b.gh WHERE a.dhhm=b.dhhm;
update chhl set (fzr,gh)= (SELECT b.fzr, b.gh FROM chhl AS a, jbsj AS b WHERE a.dhhm=b.dhhm);
如果Tab2可以不是一个表,而是一个查询 UPDATE Tab1 a,(Select ID,Name From Tab2) b SET a.Name = b.Name WHERE a.ID = b.ID;
UPDATE Tab1 a,Tab2 b SET a.Name = b.Name WHERE a.ID = b.ID;
访问多个不同的ACCESS数据库-在SQL中使用In子句,外部数据库不能带密码 Select a.*,b.* From Tab1 a,Tab2 b In 'db2.mdb' Where a.ID=b.ID;
将一个表的某个字段置空 UPDATE chhl AS a SET a.fzr = null, a.gh = null;
删除两个表中字段一样的记录 delete from xl11 where dhhm in(SELECT a.dhhm FROM xl11 AS a, xl919 AS b WHERE a.dhhm=b.dhhm;);
完成后ACCESS中字段Name索引属性显示为--有(无重复) Create Unique Index iName ON Tab1 (Name);
下面的语句删除刚才建立的两个索引 Drop Index iDate ON Tab1; Drop Index iName ON Tab1;