精妙的SQL语句(二)

原创

biyusr216 2025-02-11 11:07:34 ©著作权

文章标签 sql 数据库服务器字段表名 文章分类 Python 后端开发

©著作权归作者所有：来自51CTO博客作者biyusr216的原创作品，请联系作者获取转载授权，否则将追究法律责任

9.5.求删除重复记录的sql语句？
怎样把具有相同字段的纪录删除，只留下一条。
例如，表test里有id,name字段
如果有name相同的记录只留下一条，其余的删除。
name的内容不定，相同的记录数不定。
有没有这样的sql语句？
==============================
A:一个完整的解决方案：

将重复的记录记入temp1表:
select [标志字段id],count(*) into temp1 from [表名]
group by [标志字段id]
having count(*)>1

2、将不重复的记录记入temp1表:
insert temp1
select [标志字段id],count(*) from [表名]
group by [标志字段id]
having count(*)=1

3、作一个包含所有不重复记录的表：
select * into temp2 from [表名]
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')

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

10.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

10.3. 如何取得一个数据表的所有列名

方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再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

是不是太简单了？呵呵不过经常用阿.

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

修改别人的,需要sysadmin role
EXEC sp_password NULL, 'newpassword', 'User'

如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa

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

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

10.6. 如何在数据库里找到含有相同字段的表？
a. 查已知列名的情况
Select as TableName, as columnname
From syscolumns a INNER JOIN sysobjects b
ON =
AND b.type='U'
AND ='你的字段名字'

b. 未知列名查所有在不同表出现过的列名
Select As tablename, As columnname
From syscolumns s1, sysobjects o
Where =
And o.type = 'U'
And Exists (
Select 1 From syscolumns s2
Where =
And <>
)

10.7. 查询第xxx行数据

假设id是主键：
select *
from (select top xxx * from yourtable) aa
where not exists(select 1 from (select top xxx-1 * from yourtable) bb where a=b)

如果使用游标也是可以的
fetch absolute [number] from [cursor_name]
行数为绝对行数

10.8. 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))。

11.1.获取表结构[把 'sysobjects' 替换成 'tablename' 即可]

Select CASE IsNull(, '')
When '' Then ''
Else '*'
End as IsPK,
Object_Name() as t_name,
as c_name,
IsNull(SubString(M.text, 1, 254), '') as pbc_init,
as F_DataType,
CASE IsNull(TYPEPROPERTY(, 'Scale'), '')
WHEN '' Then Cast(A.prec as varchar)
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 = Object_id('sysobjects') )
LEFT JOIN ( SysIndexes as I
JOIN Syscolumns as A1
ON ( = and = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) )
ON ( = AND = index_col('sysobjects', I.indid, A1.colid) )
LEFT JOIN SysComments as M
ON ( = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 )
orDER BY A.Colid ASC

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

Select
(case when a.colorder=1 then else '' end) N'表名',
a.colorder N'字段序号',
N'字段名',
(case when COLUMNPROPERTY( ,,'IsIdentity')=1 then '√'else ''
end) N'标识',
(case when (Select count(*)
FROM sysobjects
Where (name in
(Select name
FROM sysindexes
Where (id = ) AND (indid in
(Select indid
FROM sysindexkeys
Where (id = ) AND (colid in
(Select colid
FROM syscolumns
Where (id = ) AND (name = ))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(,,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(,,'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 =d.id and d.xtype='U' and <>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on = AND a.colid = g.smallid
order by object_name(),a.colorder

11.3.快速获取表test的记录总数[对大容量表非常有效]

快速获取表test的记录总数:
select rows from sysindexes where id = object_id(‘test’) and indid in (0,1)

update 2 set KHXH=(ID+1)\2 2行递增编号
update [23] set id1 = 'No.'+right('00000000'+id,6) where id not like 'No%' //递增
update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6) //补位递增
delete from [1] where (id%2)=1
奇数

替换表名字段
update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/') where domurl like '%Upload/Imgswf/%'

截位
Select LEFT(表名, 5)