STEVEN5103

IT博客 首页 新随笔 联系 聚合 管理
  4 Posts :: 0 Stories :: 0 Comments :: 0 Trackbacks

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.

posted on 2005-12-28 22:54 Steven Yi 阅读(143) 评论(0)  编辑 收藏 引用 所属分类: programming
只有注册用户登录后才能发表评论。