用SQL语句对两个表的数据进行的检查：
人员基础信息表：
SQL语句
说明
select 记录总数=count(*) from memberbaseinfo
20353条记录
|
鉴定结果表:
SQL语句
说明
select 记录总数=count(*) from jdjg
总记录数：48581
select distinct c02 as "姓名" from jdjg
不同的姓名：19383
select distinct c01 as 身份证号码 from jdjg
不同的身份证号码：18104
select distinct c01,c02 from jdjg
C01和c02组合不同：21920
select c01 from jdjg group by c01 having count(*)>1
有重复［身份证号码］记录数：12319
select c02 from jdjg group by c02 having count(c02)>1
有重复［姓名］的记录数：11758
select c01,count(distinct c02) from jdjg group by c01 having count(c01)>1 and count(distinct c02)>1
身份证号码相同而姓名不同的身份证号码个数：2697
select c01 身份证号码,c02 姓名 from jdjg where c01 in ( select c01 from jdjg group by c01 having count(c01)>1 and count(distinct c02)>1) order by c01
列出身份证号码相同而姓名不同的记录明细：12093
select c02,count(distinct c01) from jdjg group by c02 having count(c02)>1 and count(distinct c01)>1
姓名相同而身份证号码不同的记录：1262
select c01,max(c23) from jdjg group by c01 order by c01
找出每个人最新的鉴定时间：18104
select c01 姓名,c19 考试分数,c22 鉴定结果,c23 鉴定时间 from jdjg where c23>'2012-1-1'
找出今年鉴定的记录：4630
select c01 身份证号码,c02 姓名,c19 鉴定成绩,c22 鉴定结果,c23 鉴定时间 from jdjg where c23>'2012-1-1' and c19 is null
找出今年以来没有填写成绩的记录：524
select c01 身份证号码,c02 姓名,c19 鉴定成绩,c22 鉴定结果,c23 鉴定时间 from jdjg where c23>'2012-1-1' and c19'2012-1-1' and c19>=60
列出今年以来合格的人员名单：2899
select c02 from jdjg where c01 in ( select top 100 c01 from jdjg where c23>'2012-1-1' group by c01 having count(*)>=2 order by count(*) DESC) and c23>'2012-1-1' group by c02
今年以来考过两次以上的人员：32
|
1、检查在鉴定结果表中存在的身份证号码在人员基础信息表中不存在的记录数
SQL语句
说明
select count(*) from jdjg where c01 not in ( select c01 from memberbaseinfo )
4866条记录
select c01,c02 from jdjg where c01 not in ( select c01 from memberbaseinfo ) order by c01
列出明细记录
2、检查在鉴定结果表中存在的姓名在人员基础信息表中不存在的记录数
select count(*) from jdjg where c02 not in ( select c02 from memberbaseinfo )
9878条记录
select c01,c02 from jdjg where c02 not in ( select c02 from memberbaseinfo ) order by c01
列出明细
3、检查在鉴定结果表和人员基础信息表中单位名称不一致的情况
select a.c01,a.c02,a.c03+a.c04+a.c05 "人事信息中的单位",b.c03+b.c04+b.c05 "鉴定结果表中的单位" from memberbaseinfo a ,jdjg b where a.c03+a.c04+a.c05b.c03+b.c04+b.c05 and a.c01=b.c01
13350条记录
4、对身份证号码的检查
select c01,c02 from jdjg where patindex('%[吖-座]%',c01)>0
包含汉字
Select c01,c02 from jdjg where patindex('%[a-zA-Z]%',c01)>0
包含字符
5、对姓名的检查
select c01,c02 from jdjg where patindex('%[0-9]%',c02)>0
包含数字
Select c01,c02 from jdjg where patindex('%[a-zA-Z]%',c02)>0
包含字符
|
Select语句:
SELECT Select_list
[WITH ]
[INTO New_table]
FROM Table_source
[WHERE Search_condition]
[GROUP BY Group_by Expression]
[HAVING Search_condition]
[ORDER BY Order_expression[ASC|DESC]]
关键词
说明
注意点
Select_list
查询结果输出列，可以指定FROM指定表中的一个或多个字段，还可以是由字段、常量与函数所组成的表达式。
DISTINCT：用于选择数据表中的唯一记录，每个SELECT语句只能有一个DISTINCT关键字。如果DISTINCT后面跟有多个字段，则要多个字段的组合相同才被看作重复记录；每个SELECT语句只能包含一个DISTINCT关键字。
INTO
查询结果输出，可用于建新表。
输出列中不能包含计算列
FROM
查询的表名。
为了使语句清晰而使用表的别名
WHERE
查询条件。
GROUP BY
显示group by 子句所产生组的全部结果，没有的值用NULL填充。
HAVING
为Group by子句产生的分组提供细分条件，having子句用来搜索分组数据。
Having 是组内而Where是针对表
ORDER BY
ORDER BY 语句用于在输出的结果集中对指定的列进行排序。默认是按照升序对记录进行排序，可以是一个字段或表达式。
Asc：升序；Desc:降序
|
运算符
符号
说明
举例
IN
判断数据记录是否出现在指定的各个数据值中
select c01 身份证号码,c02 姓名 from memberbaseinfo where c07 IN ('哈萨克族','维吾尔族','藏族');
BETWEEN
判断数据在指定范围内，可判断日期；
选择出年龄在30-35岁之间并且本星期过生日的人：
select c02 姓名,c09 出生日期 from memberbaseinfo where month(c09)=month(getdate())and ( day(c09) BETWEEN 26 and 30 ) and ( datediff(year,c09,getdate()) between 30 and 35 );
LIKE
用于在WHERE 子句中搜索指定条件。
|
通配符：
*
表示源表中所有字段
%
替代一个或多个字符，只能用在where子句中，表示0个或0个以上的字符，通常和运算符like配合使用。
_
仅替代一个字符，只能用在where子句中，通常和运算符like配合使用。
查找姓名第三字为“军”或为“丽”、身份证号码第一位在0~3之间且第4位在6~9之间、姓“李”或“张”的记录：
select c01 身份证号码,c02 姓名 from memberbaseinfo where ( c02 like '__华%' or c02 like '__丽%' ) and c02 like '[李张]%' and ( c01 like '[0-3]__[6-9]%' );
[]
字符列中的任何单一字符，只能用在where子句中，用来限定单个字符介定于指定的范围或集合中，通常和运算符like配合使用。
下面的语句找出名字中第一个字符为P—Z之间，后面字符为inger的人名：
[^]
只能用在where子句中，用来限定任何单个字符不介定于指定的范围或集合中，通常和运算符like配合使用。
[!]
不在字符列中的任何单一字符
|
常用聚集函数 {[all|distinct]expression}|*
函数名
说明
举例
Count()
统计记录数
统计各个工龄的人数：
SELECT count(*) 人数,datediff(yyyy,C10,getdate()) 工龄 from memberbaseinfo group by c10 order by datediff(yyyy,C10,getdate());
统计性别的人数：
select count(*),CASE C06
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
from memberbaseinfo group by C06
min()
计算一列中expression的最小值。
计算人员中的最大年龄：
select year(getdate())-year(min(c09)) as 最大年龄 from memberbaseinfo
或：
select datediff(yyyy,min(c09),getdate()) as 最大年龄 from memberbaseinfo
max()
计算一列中expression的最大值。
计算人员中的最大工龄：
select max(datediff(yyyy,c10,getdate())) as 最大工龄 from memberbaseinfo
avg()
计算一列中expression的平均值
计算男性员工的平均年龄：
select avg(year(getdate())-year(c09)) as 平均年龄 from memberbaseinfo where c06='男'
计算女性员工的平均年龄：
select avg(datediff(yyyy,c09,getdate())) as 平均年龄 from memberbaseinfo where c06='女'
SUM()
统计表达式中所有项的总和
|
注意点：
1
比较字段中的NULL值用is null 或is not null；
2
’[1-4]%’表示1到4之间，而’[14]%’表示1或4
3
SQL用单引号来环绕文本值,如果是数值不要使用引号。如果字段中包含_或者%等特殊字符的话用以下语句：用escape，\转移字符，表示其后的字符是常量。
查找身份证号码里包含了“%”或“_”的记录：
select c01 身份证号码,c02 姓名 from memberbaseinfo where ( c01 like '%\%%' escape '\' ) or ( c01 like '%\_%' escape '\' ) ;
4
可以直接进行函数练习：
Select substring('SQL Server 2000',1,3)
Select right(left('SQL Server 2000',10) ,6)
Select left('SQL Server 2000',10)
Select len('hello SQL Server 2000')
select replace('SQL Server 2000',' ','-')
前面用SQL语句检查表的情况，为了重复使用增加灵活性，用存储过程写下来。
/*
查找两个表身份证号码、姓名、工作单位不一致的情况
*/
CREATEPROCEDURE [DBO].[CheckTable_SFZ_XM_DWMC]
@T1varchar(40),--表1名称[主表]
--表1字段
@SFZ_T1varchar(20),--身份证号码
@XM_T1varchar(20),--姓名
@DWMC1_T1varchar(40),--一级单位
@DWMC2_T1varchar(40),--二级单位
@DWMC3_T1varchar(40),--部门名称
@T2varchar(40),--表1名称
--表2字段
@SFZ_T2varchar(20),--身份证号码
@XM_T2varchar(20),--姓名
@DWMC1_T2varchar(40),--一级单位
@DWMC2_T2varchar(40),--二级单位
@DWMC3_T2varchar(40) --部门名称
AS
declare@Sqlstr varchar(1000)
--检查T1表的身份证号码相同而姓名不同的情况
--set@Sqlstr='select '+@SFZ_T1+' 身份证号码,'+@XM_T1+' 姓名 from '+@T1+' where'+@SFZ_T1+' in ( select '+@SFZ_T1+' from '+@T1+' group by '+@SFZ_T1+' havingcount(1">'+@SFZ_T1+')>1 and count(distinct1">'+@XM_T1+')>1) order by '+@SFZ_T1
--print@Sqlstr
--exec(@SQLStr)
--两个表对比检查
--1、检查在T2表中存在的身份证号码在T1表中不存在的记录
--set@Sqlstr='select '+@SFZ_T2+' 身份证号码 , '+@XM_T2+' 姓名 from '+@T2+' where'+@SFZ_T1+' not in ( select distinct '+@SFZ_T1+' from '+@T1+' ) order by'+@SFZ_T2
--print'检查结果'
--exec(@SQLStr)
--2、检查在T2表中存在的姓名在T1表中不存在的记录
--set@Sqlstr='select '+@SFZ_T2+' 身份证号码,'+@XM_T2+' 姓名 from '+@T2+' where'+@XM_T2+' not in ( select distinct '+@XM_T2+' from '+@T1+' ) order by'+@SFZ_T2
--exec(@SQLStr)
--3、检查在T2表和T2表中单位名称不对应的记录
set@Sqlstr='select a.'+@SFZ_T1+' 身份证号码,a.'+@XM_T1+' 姓名,a.'+@DWMC1_T1+'+a.'+@DWMC2_T1+'+a.'+@DWMC3_T1+' 表1中的单位,b.'+@DWMC1_T2+'+b.'+@DWMC2_T2+'+b.'+@DWMC3_T2+' 表2中的单位 from '+@T1+' a ,'+@T2+' b wherea.'+@DWMC1_T1+'+a.'+@DWMC2_T1+'+a.'+@DWMC3_T1+' b.'+@DWMC1_T2+'+b.'+@DWMC2_T2+'+b.'+@DWMC3_T2+'and a.'+@SFZ_T1+'=b.'+@SFZ_T2
--print'检查结果'
exec(@SQLStr)
GO
--执行
--CheckTable_SFZ_XM_DWMC'MEMBERLIST','c01','c02','c03','c04','c05','KWSS','c01','c02','c03','c04','c05'
