有用的SQL语句(删除重复记录,收缩日志)
发布时间:2024-01-19 09:55:24
删除重复记录,将TABLE_NAME中的不重复记录保存到#TABLE_NAME中
select distinct * into #table_name from table_name
delete from table_name
select * into table_name from #table_name
drop table #table_name
与此相关的是“select into”选项,可以在数据库属性
对话框中,勾起来此项,或者在Query Analyzer中执行
execute sp_dboption 'db_name','select into','true'
开启。默认值是关闭的。
*******************************************************
收缩事务日志(多次执行)
backup log register with NO_LOG
backup log register with TRUNCATE_ONLY
DBCC SHRINKDATABASE(register)
更多有用的sql语句
/*sql 语法学习*/
/*函数的学习---------------------------------------*/
获取当前时间(时/分/秒):select convert(varchar(10),getdate(),8)
获取当前年月日:select convert(varchar(10),getdate(),120)
获取当前年月:select convert(varchar(7),getdate(),120)
获取当前年月:select convert(varchar(10),year(getdate())) + '-' + convert(varchar(10),month(getDate()))
select cast(b as integer) as bb from table1 where b = '11'
select a,case b when '11' then '细细' when '22' then '呵呵' else '哈哈' end as 转换,c from table1
select a,b,case when c = '111' then '细细' when c = '222' then '呵呵' else '哈哈' end as 转换1 from table1
获取当前时间:print current_timestamp
/*---------------------------------------------*/
-----------------将sql查询输出到txt文本文件中-------------------------------------------
EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 out d:\1.txt -c -q -U"sa" -P"password"'
---------------------------------------------------------------------------------------
---------------------------round的用法beigin------------------------------
declare @s float
set @s = 0.1566134
print round(@s,3)
---------------------------round的用法end---------------------------------
--------------------------------自动收缩数据库begin-----------------------------
EXEC [master]..sp_dboption [Database Name], 'autoshrink', 'TRUE'
--------------------------------自动收缩数据库end-----------------------------
-------------------------------去除首尾无效的字符begin--------------------------
declare @s varchar(20)
set @s=',,,1->1,'
while(left(@s,1)=',')
set @s=stuff(@s,1,1,'')
while(right(@s,1)=',')
set @s=stuff(reverse(@s),1,1,'')
select @s
-------------------------------去除首尾无效的字符end--------------------------
------------删除数据库中的重复记录(且仅保留一条有效记录)示例-----------------
create table A
(
userID int identity(1,1),
userName varchar(20),
userPwd varchar(20),
userEmail varchar(50)
)
insert into A(userName,userpwd) select 'qin','qin' union all select 'qin','qin1' union all select 'qin','qin1'
select * from A
--method one
delete from A where userid not in(select min(userid) as userid from A group by username ,userpwd)
--method two
delete from A where exists (select * from A b where a.username = b.username and a.userpwd = b.userpwd and a.userid < b.userid)
--method three
delete from a where userid not in(select min(userid) from A b where a.username = b.username and a.userpwd = b.userpwd and a.userid > b.userID)
select * from A
drop table A
------------删除数据库中的重复记录(且仅保留一条有效记录)示例-----------------
-------------------------------迭归的应用(找起点和终点之间的路径-----------------------------
create table t
(st varchar(20),ed varchar(20),km int)
go
insert t values ('A','B',1000)
insert t values ('A','C',1100)
insert t values ('A','D',900)
insert t values ('A','E',400)
insert t values ('B','D',300)
insert t values ('D','F',600)
insert t values ('E','A',400)
insert t values ('F','G',1000)
insert t values ('C','B',600)
go
--显示插入值
select * from t
go
--创建函数
--函数返回一个表,根据实际情况的不同一层一层的插入,可以充分利用生成的表
create function f_go(@col varchar(10))
returns @t table(col varchar(30),st varchar(20),ed varchar(20),km int,level int)
as
begin
declare @i int
set @i=1
insert @t select st+'-'+ed,*,@i from t where st=@col
while exists (select * from t a,@t b where
b.ed=a.st and b.level=@i and b.ed<>@col )
begin
set @i=@i+1
insert @t
select b.col+'-'+a.ed,a.st,a.ed,b.km+a.km,@i from t a,@t b
where b.level=@i-1 and b.ed=a.st and b.ed<>@col
end
return
end
go
--调用
--select * from dbo.f_go('A')
select col,km from dbo.f_go('a')
--删除环境
drop function f_go
drop table t
-------------------------------迭归的应用(找起点和终点之间的路径-----------------------------
--------按类别去最新的前N条记录,把同一类的放在一起,统计同一类的项的个数等-------------
create table t
(
ClassName varchar(50),
ClassCode varchar(10),
ClassID int identity(1,1)
)
insert into t
select 'cccc1','002' union all
select 'aaaa','001' union all
select 'bbbb','001' union all
select 'aaaa1','002' union all
select 'cccc','001' union all
select 'dddd','001' union all
select 'bbbb1','002' union all
select 'dddd1','002'
select * from t
select ClassCode = (case when exists(select 1 from t t1 where classCode = t1.ClassCode
and ClassID < t1.ClassID)
then '' else ClassCode end),ClassName from t order by ClassCode,ClassID desc
select count(*),classCode from (select top 100 percent ClassCode = (case when exists(select 1 from t t1 where classCode = t1.ClassCode
and ClassID < t1.ClassID)
then '' else ClassCode end),ClassName from t order by ClassCode,ClassID desc)a group by classcode
select classCode,className from t order by classCode,classID desc
drop table t
--------按类别去最新的前N条记录,把同一类的放在一起,统计同一类的项的个数等-------------
-------------同上,按类别进行统计,把同一类的项的其他内容进行相加并发在一个字段中------------------
create table tb(ProductID varchar(10),PositionID varchar(10))
insert into tb
select '10001','A1'
union all select '10001','B2'
union all select '10002','C3'
union all select '10002','D4'
union all select '10002','E5'
go
create function dbo.fc_str(@ProductID varchar(10))
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+cast(PositionID as varchar(20)) from tb where ProductID=@ProductID
return stuff(@sql,1,1,'')
end
go
select ProductID,dbo.fc_str(ProductID) as PositionID from tb group by ProductID
drop table tb
drop function dbo.fc_str
-------------按类别进行统计,把同一类的项的其他内容进行相加并发在一个字段中------------------
--取各个类的前n条记录(每个类都取top n条)
--如果有数据库中有多个类,现在要取每个类的前n条记录,可用以下语句
Create Table TEST
(ID Int Identity(1,1),
h_id Int)
Insert TEST Select 100
Union All Select 100
Union All Select 100
Union All Select 101
Union All Select 101
Union All Select 101
Union All Select 100
GO
--方法一:
Select * From TEST A Where Id In(Select TOP 3 ID From TEST Where h_id=A.h_id)
--方法二:
Select * From TEST A Where Not Exists (Select 1 From TEST Where h_id=A.h_id And ID<A.ID Having Count(*)>2)
--方法三:
Select * From TEST A Where (Select Count(*) From TEST Where h_id=A.h_id And ID<A.ID)<3
GO
Drop Table TEST
GO
--分组统计,统计每个段中数据的个数
--一般成绩统计可以用到这个
declare @t table(id int,weight int)
insert into @t select 1, 20
insert into @t select 2, 15
insert into @t select 3, 5
insert into @t select 4, 60
insert into @t select 5, 12
insert into @t select 6, 33
insert into @t select 7, 45
insert into @t select 8, 59
insert into @t select 9, 89
insert into @t select 10,110
declare @p int
set @p=10
select
rtrim(p*@p)+'-'+rtrim((p+1)*@p">p*@p)+'-'+rtrim((p+1)*@p) as p,
num
from
(select (weight/@p">weight/@p) as p,count(*) as num from @t where weight between 10 and 100 group by (weight/@p">weight/@p)) a
----------------------------在in语句中只用自定义排序begin--------------------------------
declare @t table(id int,weight int)
insert into @t select 1, 20
insert into @t select 2, 15
insert into @t select 3, 5
insert into @t select 4, 60
insert into @t select 5, 12
insert into @t select 6, 33
insert into @t select 7, 45
insert into @t select 8, 59
insert into @t select 9, 89
insert into @t select 10,110
--默认in语句中sql会按照id进行排序
select * from @t where id in(2,4,3)
--用此方法可以按照我们传入的id顺序进行显示数据
select * from @t where id in(2,4,3) order by charindex(rtrim(id),',2,4,3,')
----------------------------在in语句中只用自定义排序end--------------------------------
猜你喜欢
- Pytorch中,变量参数,用numel得到参数数目,累加def get_parameter_number(net): tota
- 一、前言随着三胎政策的开放,人们对于生娃的讨论也逐渐热烈了起来,经常能够在各大社交媒体当中看到相关的话题,而随着时间慢慢地流逝,中国的首批“
- 官方说明链接:https://intellij-support.jetbrains.com/hc/en-us/community/posts
- 1.导言现今的公司需要易访问的和可用性好的商业数据,以便他们可以在全球市场中获得一席之地。与易访问数据的这个需求相呼应的,关系数据库和分析数
- 一,命名空间函数 tf.variable_scope tf.name_scope 先以下面的代码说明两者的区别# 命名空间管理函数'
- 本文实例为大家分享了python实现决策树分类算法的具体代码,供大家参考,具体内容如下1、概述决策树(decision tree)——是一种
- 本文实例讲述了Python判断有效的数独算法。分享给大家供大家参考,具体如下:一、题目判断一个 9x9 的数独是否有效。只需要根据以下规则,
- 本文实例为大家分享了python实现五子棋小程序的具体代码,供大家参考,具体内容如下一、结合书上例子,分三段编写:wuziqi.py#cod
- OpenCV的imread不能读取中文路径问题import numpy as npimport cv2cv_img = cv2.imdeco
- Python 爬虫之超链接 url中含有中文出错及解决办法python3.5 爬虫错误:UnicodeEncodeError: 'a
- 今天想围绕“产品交互设计”说说一些我们的想法,一说到产品设计,我想在坐的各位脑海里肯定联想到了很多表单设计、导航设计、界面布局等等,但是今天
- 提到开心消消乐这款小游戏,相信大家都不陌生,其曾在 2015 年获得过玩家最喜爱的移动单机游戏奖,受欢迎程度可见一斑,本文我们使用 Pyth
- CASSolution使用CAS作为认证协议。A作为主要的认证提供方(provider)。A保留用户系统,其余系统如xxx/www不保留用户
- 周末在家,儿子闹着要玩游戏,让玩吧,不利于健康,不让玩吧,扛不住他折腾,于是想,不如一起搞个小游戏玩玩!之前给他编过猜数字 和 掷骰子 游戏
- 学了这么长时间的Pygame,一直想写个游戏实战一下。看起来很简单的游戏,写其来怎么这么难。最初想写个俄罗斯方块,想了很长时间如何实现,想来
- 一、k-近邻算法原理及API1.k-近邻算法原理如果一个样本在特征空间中的k个最相似(即特征空间中最邻近)的样本中的大多数属于某一个类别,则
- 启用Ad Hoc Distributed Queries:exec sp_configure 'show advanced opti
- pygame实现代码雨动画如视频所示 利用pygame库实现了一个代码呈雨状下落的视觉效果部分代码如下import sysimport ra
- 在 Web 2.0 时代,开发人员和用户都对基于 Web 的应用程序的可用性和响应性抱有很高的期望。除非是在过去的两年内一直没有关注过这个领
- 在plugin/tagbar.vim里面的键映射改成: \ ['nexttag', '<