1.判断重名
select a00101,count(a00101) from a001 where a00180='11' group by a00101 having count(a00101)>1
2.判断身份证重号
select a00177,count(a00177) from a001 where a00180='01' group by a00177 having count(a00177)>1
3.判断子集主建重复记录
select a00100 ,id,count(*) from a082a004 group by a00100,id having count(*)>1
4.条件查询
select a0101,c0111 from a001a002 where c0111 in (select c0111 from a001a001)
5.插入选定的数据
insert into a022(a02200,a02299,zorder,status,id ,a02205,a02210)
select a00100 as a02200,a00100+'@'+cast(id as varchar(3)) as a02299,100 as zorder, status,id,a2205 as a02205,a2210 as a02210 from gpms..a022a004
6./*设置报表权限时的模块列表*/
Select * from sm_BusiRoles
7./*系统建模时子集中指标的排列顺序*/
select * from sm_builtitem where setid='a001' order by ordersame
8./*报表中进行条件设置时某子集指标列表*/
select * from sm_builtItem where SetId='A001'
9.进入薪资发放时点发放出来的人员名单:
SELECT A05900,A059.id,A059.zorder,E00122,A00101,A00182,A059.A05901,A059.A05902,A059.A05903,A059.A05904,A059.A05905,A059.A05906,A059.A05907,A059.A05908,A059.A05909,A059.A05910,A059.C05901,A059.C05902,A059.C05903,A059.C05904,A059.C05905,A059.E05902,A059.G05901,A059.G05902,A059.G05903,A059.G05904,A059.G05905,A059.G05906,A059.G05907,A059.G05908,A059.G05909,A059.G05910,A059.G05911,A059.G05912,A059.G05913,A059.G05914,A059.G05915,A059.G05916,A059.G05917,A059.G05918,A059.G05919,A059.G05920,A059.G05921,A059.G05922,A059.G05923,A059.G05924,A059.G05925,A059.G05926,A059.G05927,A059.G05928,A059.G05929,A059.G05930,A059.G05931,A059.G05932,A059.G05933, a001.b00100,A05999, substring(A059.status,1,1) as Status
FROM A059,A001,B001
WHERE b001.b00100=a001.b00100 and A059.A05900=A001.A00199 AND A059.A05900 in (select A00100 from vw_xz_personrelate where payclassid=52 and paySetid=78 And E00122 like '%%' and E00122 in (select depbm from [Vdeppri000763] where havepri<>'0' and modelid='HR_XZFL') and ( stopflag='00' or stopflag='01')) and A059.status like '1%' Order by A001.B00100,A001.E00122,A001.zorder,A059.zorder,A05904
解释：where条件中主要有1)帐套已关联该人员编码;2)人员薪资没有停发标志;3.检查A059.status标志;
10.对某一列自动生成递增的号码
use ehrdemo
create table test(userid int)
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
11.怎么判断出一个表的哪些字段不允许为空？
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename
12.如何取得一个数据表的所有列名
方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
a.SQL语句如下：
declare @objid int,@objname char(40)
set @objname = 'tablename'
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid
b.SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='<TABLE>'
13.如何在数据库里找到含有相同字段的表？
a. 查已知列名的情况
SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b
ON a.id=b.id
AND b.type='U'
AND a.name='你的字段名字'
b. 未知列名查所有在不同表出现过的列名
Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
And o.type = 'U'
And Exists (
Select 1 From syscolumns s2
Where s1.name = s2.name
And s1.id <> s2.id
)
14.SQL Server日期计算
a. 一个月的第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)
i. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
15.