查询同一个表中某一字段值相同的记录
select * from 表名
where 字段 in(select 字段 from 表名 group by 字段 having
count(1)>1)
select * from 表名 a
where exists (select 1 from 表名 where 字段=a.字段 and
主键<> a.主键)
select(嵌套查询)
--单行子查询
select ename,sal,deptno from emp where deptno=(select deptno from emp where ename=‘SCOTT‘)
--多行子查询
1) select ename,sal,deptno,job from emp where job in (select distinct job from emp where deptno = 10)
2) select ename,sal,deptno,job from emp where sal>all(select sal from emp where deptno = 30)
3) select ename,sal from emp where deptno = 30
4) select ename,sal,deptno,job from emp where sal>any(select sal from emp where deptno = 30)
--多列子查询
1) select ename,sal,deptno,job from emp where (deptno,job)=(select deptno,job from emp where ename =‘SMITH‘)
update emp set sal=1500,comm=30 where ename = ‘CLARK‘
3) select ename,sal,comm from emp where ename = ‘CLARK‘
4) select ename,sal,comm,deptno from emp where(sal,nvl(comm,-1)) in (select sal,nvl(comm,-1) from emp where deptno =30)
5) select ename,sal,comm from emp where deptno=30
6) select ename,sal,comm,deptno from emp where sal in (select sal from emp where deptno = 30)and nvl(comm,-1) in(select nvl(comm,-1)from emp where deptno = 30)
SQL Server获取指定行(如第二行)的数据
1. ROW_NUMBER()
在SQL Server2005中有一个ROW_NUMBER函数，它将针对SELECT语句返回的每一行，从1开始编号，赋予其连续的编号。在查询时应用了一个排序标准后，只有通过编号才能够保证其顺序是一致的，当使用ROW_NUMBER函数时，也需要专门一列用于预先排序以便于进行编号。
综上，我写了一句话：
vSQL = "select top 2 CarId from TCar a where exists (select 1 from TCar where
PersonId=a.PersonId and CarId<> a.CarId and InOut<>a.InOut and
CarNum = a.CarNum and CarNum =‘" +
csVehicleNum + "‘) order by
InOutTime,CarId asc"; //查找TCar表中同一辆车前后两条记录的CarId