sql 常用语句
--1、最接近当天的数据 当天再次排序
select * from CSI_MagicPower order by datediff(d, CreateDate,GETDATE())
--0、修改自增字段的初始值
DBCC CHECKIDENT('表明', RESEED, 初始值)
--1、锁
select request_session_id as spid,OBJECT_NAME(resource_associated_entity_id) as tableName
from sys.dm_tran_locks
where resource_type='OBJECT'
--2、实现行列互换
Create Table T_Stu
(
UID INT PRIMARY KEY IDENTITY(1,1),
UserName varchar(100) not null,
Course varchar(50) not null,
Score int not null
)
--insert into T_Stu values('张三','语文',87)
--insert into T_Stu values('张三','数学',89)
--insert into T_Stu values('张三','物理',97)
--insert into T_Stu values('李四','语文',84)
--insert into T_Stu values('李四','数学',86)
--insert into T_Stu values('李四','物理',95)
--insert into T_Stu values('王五','语文',78)
--insert into T_Stu values('王五','数学',79)
--insert into T_Stu values('王五','物理',81)
select UserName '姓名', Course '课程',Score '分数' from T_Stu
select UserName as '姓名' ,
max(case Course when '语文' then Score else 0 end) '语文',
max(case Course when '数学' then Score else 0 end) '数学',
max(case Course when '物理' then Score else 0 end) '物理'
from T_Stu
group by UserName
select a.UserName as '姓名',a.score '语文',b.score '数学' from
(select UserName,score from T_Stu where Course='语文') a,
(select UserName,score from T_Stu where Course='数学') b
where a.score<b.score and a.UserName=b.UserName
Create Table T_Stu2
(
UID INT PRIMARY KEY IDENTITY(1,1),
UserName varchar(100) not null,
Chinese int not null,
Math int not null,
Physics int not null
)
--insert into T_Stu2 values('张三',88,87,98)
--insert into T_Stu2 values('李四',85,84,78)
--insert into T_Stu2 values('王五',59,78,67)
select UserName '姓名', Chinese '语文',Math '数学',Physics '物理' from T_Stu2
select * from
(
select '姓名'=UserName , 课程 = '语文' , 分数 = Chinese from T_Stu2
union all
select '姓名'=UserName , 课程 = '数学' , 分数 = Math from T_Stu2
union all
select '姓名'=UserName , 课程 = '物理' , 分数 = Physics from T_Stu2
) t
order by '姓名' , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end
--4、随机查询数据
Select * From T_Stu Order By NewID()
--5、多表修改
update iknow_User set Point=Point+(SELECT NUM FROM (select U_ID,SUM(Result) NUM from CSI_Luckdraw WHERE CreateDate<='2014-01-20 19:15:31.270'
GROUP BY U_ID ) B WHERE U_ID=iknow_User.AccountId) WHERE AccountId IN (select U_ID from CSI_Luckdraw WHERE CreateDate<='2014-01-20 19:15:31.270'
GROUP BY U_ID )
--6、多表添加
insert into iknow_PointLog (UserId,Point,PointRuleId,CreateDate,OperatorId,RemarkId)
SELECT U_ID,Result,0,CreateDate,U_ID,0 from CSI_Luckdraw WHERE CreateDate<='2014-01-20 19:15:31.270'
--7、查询一周时间
select * from CSI_Riddle where AnswerTime<=DATEADD(wk,DATEDIFF(wk,0,getdate()-1),6)
select * from CSI_Riddle
where AnswerTime between DATEADD(wk,DATEDIFF(wk,0,getdate()-1),0) and DATEADD(wk,DATEDIFF(wk,0,getdate()-1),6)
select DATEADD(wk,DATEDIFF(wk,0,getdate()),0),DATEADD(wk,DATEDIFF(wk,0,getdate()),6)
--8、删除数据，自增字段重新排序
truncate table CSI_RiddleAnswer
--9、定义自增列初始值
--修改自增字段的初始值
DBCC CHECKIDENT('表明', RESEED, 初始值)
--10、查询一周时间内数据
select * from CSI_Riddle
where AnswerTime between DATEADD(wk,DATEDIFF(wk,0,getdate()-1),0) and DATEADD(wk,DATEDIFF(wk,0,getdate()-1),6)
--11、分页存储过程（动软生成器）
Create procedure [dbo].[SeachFeYe]
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数,@rowcount as 总行数,@currentpage as 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
GO
----------------------、、、、、、----------------------------
Create PROCEDURE [dbo].[CMS_spReturnPage](@Select varchar(8000), @OrderBy varchar(1000),
@StartRow int, @EndRow int)
AS
BEGIN
declare @ColList varchar(2000);
declare @Where varchar(2000);
declare @i int;
declare @i2 int;
declare @tmp varchar(1000);
declare @dec varchar(1000);
declare @f varchar(100);
declare @d varchar(100);
declare @Symbol char(2);
declare @SQL varchar(8000);
declare @Sort varchar(1000);
set @Sort = @OrderBy + ', '
set @dec = ''
set @Where = ''
set @SQL = ''
set @i = charindex(',' , @Sort)
while @i != 0
begin
set @tmp = left(@Sort,@i-1)
set @i2 = charindex(' ', @tmp)
set @f = ltrim(rtrim(left(@tmp,@i2-1)))
set @d = ltrim(rtrim(substring(@tmp,@i2+1,100)))
set @Sort = rtrim(ltrim(substring(@Sort,@i+1,100)))
set @i = charindex(',', @Sort)
set @symbol = case when @d = 'ASC' then '>' else '<' end +
case when @i=0 then '=' else '' end
set @dec = @dec + 'declare @' + @f + ' sql_variant; '
set @ColList = isnull(replace(replace(@colList,'>','='),'<','=') + ' and ','') +
@f + @Symbol + ' @' + @f
set @Where = @Where + ' OR (' + @ColList + ') '
set @SQL = @SQL + ', @' + @f + '= ' + @f
end
set @SQL = @dec + ' ' +
'SET ROWCOUNT ' + convert(varchar(10), @StartRow) + '; ' +
'SELECT ' + substring(@SQL,3,7000) + ' FROM (' + @Select + ') a ORDER BY '+ @OrderBy + '; ' +
'SET ROWCOUNT ' + convert(varchar(10), 1 + @EndRow - @StartRow) + '; ' +
'SELECT * FROM (' + @Select + ') a WHERE ' + substring(@Where,4,7000) + ' ORDER BY ' + @OrderBy + '; ' +
'SET ROWCOUNT 0;'
exec(@SQL)
END
GO
--12、获取日期不重复
select distinct (convert(varchar(10),CreateDate,120)) AS CreateDate from CSI_MagicPowerWinLog
where 1=1 ORDER BY CreateDate DESC
71 62100 2014-02-28 11:51:00.633
72 62134 2014-02-28 11:51:00.650
73 22334 2014-02-28 11:51:00.650
74 10 2014-02-28 11:51:00.653
75 22335 2014-02-28 11:51:00.653
76 22336 2014-03-03 13:20:00.837
77 22332 2014-03-03 13:20:00.843
78 1 2014-03-03 13:20:00.847
79 62107 2014-03-03 13:20:01.000
80 14 2014-03-03 13:20:01.107
81 21 2014-03-04 16:50:00.387
82 9 2014-03-04 16:50:00.443
83 62135 2014-03-04 16:50:00.717
84 47 2014-03-04 16:50:00.817
85 12 2014-03-04 16:50:01.353
86 11 2014-03-04 16:50:01.360
效果：
2014-03-04
2014-03-03
2014-02-28
--13、sql 字段字符串内容替换
SELECT * FROM dbo.Table WHERE Name LIKE '%NYCL23%'
UPDATE Table SET Name=replace(Name,'NYCL23','WYCL10') WHERE Name LIKE '%NYCL23%'
swl.NYCL23.wyy 结果 swl.WYCL10.wyy
