SQL语句技巧

SQL语句技巧

1 行列相关 1.1   行列转换 -- 普通

 

(CJ)

Name Subject Result

80

90

85

85

92

82

 

80 90 85

85 92 82

 

declare @sql varchar(4000)

set @sql = 'select Name'

select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'

from (select distinct Subject from CJ) as a

select @sql = @sql+' from test group by name'

exec(@sql)

 

1.2   行列转换 -- 合并

 

A,

id pid

1 1

1 2

1 3

2 1

2 2

3 1

B:

id pid

1 1,2,3

2 1,2

3 1

 

create function fmerg(@id int)

returns varchar(8000)

as

begin

declare @str varchar(8000)

set @str=''

select @str=@str+','+cast(pid as varchar) from A where id=@id

set @str=right(@str,len(@str)-1)

return(@str)

End

go

 

--

select distinct id,dbo.fmerg(id) from A

 

1.3 示例

select * from v_temp

:

user_name role_name

-------------------------

feng

feng

test  

:

user_name role_name

---------------------------

feng ,

test

===================

create table a_test(name varchar(20),role2 varchar(20))

insert into a_test values('','')

insert into a_test values('','')

insert into a_test values('','')

insert into a_test values('','')

 

create function join_str(@content varchar(100))

returns varchar(2000)

as

begin

declare @str varchar(2000)

set @str=''

select @str=@str+','+rtrim(role2) from a_test where [name]=@content

select @str=right(@str,len(@str)-1)

return @str

end

go

 

--

select [name],dbo.join_str([name]) role2 from a_test group by [name]

 

--select distinct name,dbo.uf_test(name) from a_test

 

2 比较结构相同的两表

32

============================ 

northwindorders

select * into n1 from orders

select * into n2 from orders

 

select * from n1

select * from n2

 

--n1

alter table n1 add constraint pk_n1_id primary key (OrderID)

alter table n2 add constraint pk_n2_id primary key (OrderID)

 

select OrderID from (select * from n1

union

select * from n2) a group by OrderID having count(*) > 1

 

ID

 

select * from n1 where orderid in

(

select OrderID from (select * from n1

union

select * from n2) a group by OrderID having count(*) > 1

)

--n1,n2

delete from n1 where orderID in ('10728','10730')

delete from n2 where orderID in ('11000','11001')

 

--*************************************************************

-- uu.bitsCN.com

select * from n1 where orderid in

(

select OrderID from (select * from n1

union

select * from n2) a group by OrderID having count(*) > 1

)

union

--n2n110728,10730

select * from n1 where OrderID not in (select OrderID from n2)

union

--n1n211000,11001

select * from n2 where OrderID not in (select OrderID from n1)

 

3 四种方法取表里 n 到 m 条纪录：

 

1.

select top m * into () from tablename order by columnname -- top m

set rowcount n

select * from order by columnname desc

 

 

2.

select top n * from

(select top m * from tablename order by columnname) a

order by columnname desc

 

 

3.tablenameidentity

select identity(int) id0,* into #temp from tablename

 

nm

select * from #temp where id0 >=n and id0 <= m

 

select identity(int) id0,* into #temp from tablename,DBselect into/bulkcopy bitsCN@com

exec sp_dboption DB,'select into/bulkcopy',true

 

 

4.identity

select * from tablename where identitycol between n and m

5.

create table a_dist(id int,name varchar(20))

 

insert into a_dist values(1,'abc')

insert into a_dist values(1,'abc')

insert into a_dist values(1,'abc')

insert into a_dist values(1,'abc')

 

exec up_distinct 'a_dist','id'

 

select * from a_dist

 

create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))

--f_key

as

begin

declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer

select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'

exec(@sql)

open cur_rows

fetch cur_rows into @id,@max

while @@fetch_status=0

begin

select @max = @max -1

set rowcount @max

bitscn.net

 

select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key

if @type=56

select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id

if @type=167

select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''

exec(@sql)

fetch cur_rows into @id,@max

end

close cur_rows

deallocate cur_rows

set rowcount 0

end

 

select * from systypes

select * from syscolumns where id = object_id('a_dist')

 

4 查询数据的最大排序问题（只能用一条语句写）

CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))

 

insert into hard values ('A','1',3)

insert into hard values ('A','2',4)

insert into hard values ('A','4',2)

insert into hard values ('A','6',9)

insert into hard values ('B','1',4)

insert into hard values ('B','2',5)

insert into hard values ('B','3',6)

insert into hard values ('C','3',4)

insert into hard values ('C','6',7)

insert into hard values ('C','2',3)

bitsCN@com

 

 

 

 

qu co je

----------- ----------- -----

A 6 9

A 2 4

B 3 6

B 2 5

C 6 7

C 3 4

 

 

quje2

sql

select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)

5 求删除重复记录的 sql 语句

testid,name

name

name

 

==============================

A:

 

temp1:

select [id],count(*) into temp1 from []

group by [id]

having count(*)>1

 

2temp1:

insert temp1

select [id],count(*) from []

group by [id]

having count(*)=1

 

3

select * into temp2 from []

www.bitscn.net

 

 

where id in(select id from temp1)

 

4:

delete []

 

5

insert []

select * from temp2

 

6:

drop table temp1

drop table temp2

================================

B:

create table a_dist(id int,name varchar(20))

 

insert into a_dist values(1,'abc')

insert into a_dist values(1,'abc')

insert into a_dist values(1,'abc')

insert into a_dist values(1,'abc')

 

exec up_distinct 'a_dist','id'

 

select * from a_dist

 

create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))

--f_key

as

begin

declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer

select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'

exec(@sql)

open cur_rows

fetch cur_rows into @id,@max

while @@fetch_status=0

 

 

 

begin

select @max = @max -1

set rowcount @max

select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key

if @type=56

select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id

if @type=167

select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''

exec(@sql)

fetch cur_rows into @id,@max

end

close cur_rows

deallocate cur_rows

set rowcount 0

end

 

select * from systypes

select * from syscolumns where id = object_id('a_dist')

 

 

6 取得一个数据表的所有列名

 

SYSTEMOBJECTSYSTEMID,SYSCOLUMN

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

 

.

 

7 通过 SQL 语句来更改用户的密码

 

,sysadmin role

EXEC sp_password NULL, 'newpassword', 'User'

 

SAEXEC sp_password NULL, 'newpassword', sa

 

8 怎么判断出一个表的哪些字段不允许为空？

 

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and TABLE_NAME=tablename

 

9 如何在数据库里找到含有相同字段的表？

a. www_bitscn_net

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

)

 

10 查询第 xxx 行数据

 

id

select *

from (select top xxx * from yourtable) aa

where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)

 

fetch absolute [number] from [cursor_name]

 

11 SQL Server 日期计算

a.

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

b.

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

c. uwww.bitscn.net

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))

 

12 获取表结构

[把 'sysobjects' 替换 成 'tablename' 即可]

 

SELECT CASE IsNull(I.name, '')

When '' Then ''

Else '*'

End as IsPK,

Object_Name(A.id) as t_name,

A.name as c_name,

IsNull(SubString(M.text, 1, 254), '') as pbc_init,

T.name as F_DataType,

CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '')

WHEN '' Then Cast(A.prec as varchar)

bbs.bitsCN.com

 

ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar)

END as F_Scale,

A.isnullable as F_isNullAble

FROM Syscolumns as A

JOIN Systypes as T

ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') )

LEFT JOIN ( SysIndexes as I

JOIN Syscolumns as A1

ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) )

ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) )

LEFT JOIN SysComments as M

ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 )

ORDER BY A.Colid ASC

 

 

13 提取数据库内所有表的字段详细说明的 SQL 语句

SELECT

(case when a.colorder=1 then d.name else '' end) N'',

a.colorder N'',

a.name N'',

(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''

end) N'',

(case when (SELECT count(*)

FROM sysobjects

WHERE (name in uu.bitsCN.com

(SELECT name

FROM sysindexes

WHERE (id = a.id) AND (indid in

(SELECT indid

FROM sysindexkeys

WHERE (id = a.id) AND (colid in

(SELECT colid

FROM syscolumns

WHERE (id = a.id) AND (name = a.name))))))) AND

(xtype = 'PK'))>0 then '√' else '' end) N'',

b.name N'',

a.length N'',

COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'',

isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'',

(case when a.isnullable=1 then '√'else '' end) N'',

isnull(e.text,'') N'',

isnull(g.[value],'') AS N''

FROM syscolumns a

left join systypes b

on a.xtype=b.xusertype

inner join sysobjects d

on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join syscomments e

on a.cdefault=e.id

left join sysproperties g

on a.id=g.id AND a.colid = g.smallid

order by object_name(a.id),a.colorder

 

14 快速获取表 test 的记录总数

 

test:

select rows from sysindexes where id = object_id(‘test’) and indid in (0,1)