1、数据相关操作
1、数据取整截取
【四舍五入取整截取】
select round(54.56,0)
【向下取整截取】 得54
SELECT FLOOR(54.56)
【向上取整截取】 得14
SELECT CEILING(13.15)
2、 报错：遇到以零作除数错误
1、在除数那里判断分母是否为0（用‘NULLIF’函数）
select FLOOR(MB038/NULLIF(MB037,0)/11.5) AS XSCN from INVMB
2、在除数那里判断分母是否为0（用‘CASE WHEN’函数）
SELECT (CASE WHEN Quantity*UnitPrice=0 THEN 0 ELSE (Quantity*Price)/(Quantity*UnitPrice)) AS [平均值] FROM ##表
2、日期相关操作
1、格式转换
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
select datediff(year, 开始日期,结束日期); --两日期间隔年
select datediff(quarter, 开始日期,结束日期); --两日期间隔季
select datediff(month, 开始日期,结束日期); --两日期间隔月
select datediff(day, 开始日期,结束日期); --两日期间隔天
select datediff(week, 开始日期,结束日期); --两日期间隔周
select datediff(hour, 开始日期,结束日期); --两日期间隔小时
select datediff(minute, 开始日期,结束日期); --两日期间隔分
select datediff(second, 开始日期,结束日期); --两日期间隔秒
2、取第一天
1) 一年的第一天
SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0)
注：首先DATEDIFF(YEAR,0,GETDATE()) --计算当前年份与 1900年相差的年数,然后通过计算1900-1-1加上相差的年数的日期即为当年第一天
2) 一个季的第一天
SELECT DATEADD(Quarter,DATEDIFF(Quarter,0,GETDATE()),0)
注：首先DATEDIFF(Quarter,0,GETDATE()) --计算当前月份与 1900年相差的季份数,然后通过计算1900-1-1加上相差的季份数的日期即为当季第一天
3) 一个月的第一天
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)
注：首先DATEDIFF(MONTH,0,GETDATE()) --计算当前月份与 1900年相差的月份数,然后通过计算1900-1-1加上相差的月份数的日期即为当月第一天
4) 一周的第一天
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
5) 当天的半夜 (00:00:00.000)
SELECT DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
6) 上月的最后一天
SELECT DATEADD(ms,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0))
注:用本月的第一天减去3毫秒，即得出上个月的最有一天.SQL SERVER DATETIME类型的时间精确到3毫秒。
7) 本月的最后一天
SELECT DATEADD(ms,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0))
8) 本月的天数
i) SELECT DAY(DATEADD(ms,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)))
ii)SELECT 32-DAY(GETDATE()+(32-DAY(GETDATE())))
9) 本年的最后一天
SELECT DATEADD(ms,-3,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE())+1,0))
10) 一周的第一天
SELECT DATEADD(DAY,1-DATEPART(weekday,GETDATE()),GETDATE())
一周的最后一天
SELECT DATEADD(DAY,7-DATEPART(WeekDay,GETDATE()),GETDATE())
SELECT DATEADD(weekday,DATEDIFF(weekday,0,DATEADD(DAY,6-DATEPART(day,GETDATE()),GETDATE())),0)
3、字符串相关操作
1、统计字段中符号出现的次数。
len(字段)-len(replace(字段,'查找符号','')) AS gs
2、无法解决 equal to 操作中 “Chinese_PRC_CI_AS” 和 “Chinese_PRC_BIN” 之间的排序规则冲突
添加 collate Chinese_PRC_CI_AS 就行
如：from uf_workorderonline left join YFERP.RM.dbo.INVMB on cpph=MB001 collate Chinese_PRC_CI_AS
3、参数数据类型 text 对于 len 函数的参数 1 无效
1、参数数据类型 text 对于 len 函数的参数 1 无效，使用cast 把 text 先转为 varchar。
isnull(len(CAST(sxyg as VARCHAR(4000)))-len(replace(CAST(sxyg as VARCHAR(4000)),',',''))+1,0) AS sxyg
4、游标相关操作
declare @sxsl int --上线数量
declare @kdsj char(5) --开单时间
declare @cpph varchar(200) --产品品号
DECLARE JSQJHQLCURSOR CURSOR FOR --创建游标 JSQJHQLCURSOR为游标名称
SELECT cpph,sxsl,kdsj FROM uf_workorderonline
where kdrq = CONVERT(varchar(100), GETDATE(), 23) and cxbh=@SCCX order by id asc --要循环的数据存入游标
OPEN JSQJHQLCURSOR --打开游标
FETCH FROM JSQJHQLCURSOR INTO
@cpph,@sxsl,@kdsj --取出数据存入临时变量中
WHILE @@FETCH_STATUS=0 --进入循环
BEGIN
----------------------
--这里是判断条件及操作
-------------------
FETCH NEXT FROM JSQJHQLCURSOR INTO --执行下一条
@cpph,@sxsl,@kdsj
END
CLOSE JSQJHQLCURSOR
Deallocate JSQJHQLCURSOR
5、两台SQL服务器相互访问
创建服务器的连接
EXEC sp_addlinkedserver
@server='name',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.3.X' --要访问的服务器
EXEC sp_addlinkedsrvlogin
'name', --被访问的服务器别名
'false',
NULL,
'sa', --帐号
'sa' --密码
查询方式
SELECT * FROM 服务器名.数据库名.dbo.表名
完成互联后必须执行第7
6、清除数据库日志文件
USE [master]
GO
ALTER DATABASE [RM] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [RM] SET RECOVERY SIMPLE
GO
USE [RM]
GO
DBCC SHRINKFILE (N'Leader_Log' , 0,TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE [RM] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [RM] SET RECOVERY FULL
GO
--查询指定数据库的日志文件名称
USE [RM]
GO
SELECT name FROM SYS.database_files WHERE type_desc='LOG'
7.做触发器时错误“该伙伴事务管理器已经禁止了它对远程/网络事务的支持”解决方案（两台SQL关联后必须确认该操作）
Server 2008 r2 操作系统
启动台下服务。
Distributed Transaction Coordinator
Distributed Link Tracking Client
Remote Procedure Call(PRC)
Remote Procedure Call(PRC) Locator
同时打开’控制面板’->‘管理工具’->‘组件服务’,点开’组件服务’->‘计算机’->‘我的电脑’,然后展开“我的电脑”，找到 Distributed Transaction Coordinator 继续展开，在
本地DTC上右键，属性。 勾选 安全设置所有选项和 不要求进行验证。
8、获取数据库表中所有的字段名称
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(name) from syscolumns where id=OBJECT_ID('INVMB') #INVMB 数据库表
select @s #@S 所有字段保存的临时字段
9、允许复制自增ID
SET IDENTITY_INSERT 表名XXX ON
SET IDENTITY_INSERT 表名XXX OFF
10、查询结果生成新数据表
select * into 新表 from 老表 where 查询条件
11、先进先出原则计算
--方法一
declare @dt datetime,@pid char(3)
set @dt='2004-01-06'
set @pid='001'
declare @p table ( pid char(3),iodt datetime, qty int)
insert @p select '001', '2004-01-01', 10
union select '001', '2004-01-03', -8
union select '001', '2004-01-04', -1
union select '001', '2004-01-05', 5
union select '001', '2004-01-07', -6
union select '002', '2004-01-06', 20
select pid,库龄,case when qty>数量 then 数量 else qty end as 数量
from ( select pid,
datediff(day,iodt,@dt) as 库龄,
qty,
( select isnull(sum(qty),0)
from @p
where pid=@pid and iodt<=@dt and (iodt<=a.iodt or (iodt>a.iodt and qty<0))
)
as 数量
from @p a
where pid=@pid and iodt<=@dt and qty>0
) b
where 数量>0
--方法二 需 SQL2012及以上版本或oracle才支持。
-- 20200719 日期作为变量传入
with p_dw as
(select '001' pid, '20200701' date_id, 10 qty
from dual
union all
select '001' pid, '20200703' date_id, -8 qty
from dual
union all
select '001' pid, '20200704' date_id, -1 qty
from dual
union all
select '001' pid, '20200705' date_id, 5 qty --3
from dual
union all
select '001' pid, '20200707' date_id, -3 qty
from dual
union all
select '001' pid, '20200708' date_id, 9 qty --9
from dual
union all
select '001' pid, '20200709' date_id, 7 qty --7
from dual
union all
select '001' pid, '20200710' date_id, 7 qty --7
from dual)
select pid,
--qty,
date_id,
days,
current_store_nums cnt_qty,
--nvl(lag(current_store_nums) over (partition by pid order by date_id),0) LAST_ROWS,
current_store_nums -
nvl(lag(current_store_nums) over(partition by pid order by date_id),
0) current_store_row__qty
from (select pid,
qty,
date_id,
to_date('20200719', 'yyyymmdd') -
to_date(date_id, 'yyyymmdd') days,
SUM(case
when qty < 0 then
qty
else
0
end) OVER(PARTITION BY PID) + SUM(case
when qty > 0 then
qty
else
0
end) OVER(PARTITION BY PID ORDER BY DATE_ID) current_store_nums
from p_dw
where date_id <= '20200719')
WHERE current_store_nums > 0
AND QTY > 0
12、SQL游标循环查询表值函数并存到指定表中
DECLARE @ph CHAR(20)
DECLARE JSQJHQLCURSOR CURSOR FOR
select 品号 from LED
OPEN JSQJHQLCURSOR --循环取品号当条件传入，然后通过LOADBOM函数取材料品号
FETCH FROM JSQJHQLCURSOR INTO
@ph
WHILE @@FETCH_STATUS=0 --通过游标判断循环
BEGIN
insert into LEDlist select distinct ID from ( --把材料品号结果存入 LEDlist表中。
SELECT ID FROM Loadbom(@ph,1) where ID<>@ph
union all
SELECT ParentID FROM Loadbom(@ph,1) where ID<>@ph )as KK
FETCH NEXT FROM JSQJHQLCURSOR INTO
@ph
END
CLOSE JSQJHQLCURSOR
Deallocate JSQJHQLCURSOR
13、SQL中 TOP 与 TOP (100) PERCENT 的说明
sql="select top 30 * from data where title='"&title1&"' order by id desc" 原意是选出符合“where 条件”的记录集里的“前30条”
但是，对于该SQL语句，由于语句里同时存在where和top语句的，并且where条件列不是合适的索引，程序执行的是全表扫描，首先是查找符合where条件的记录， 而这里的top限制形同虚设。如果全表是百万级别以上的数据表，那么就这么一个简单的判断，就有可能拖垮数据库。
所以需要先把符合“where条件”的记录，用一个子查询筛选出来，再在筛选结果集里选top30。
因为SQL规则规定，如果子查询里，有order ...，就必须有TOP，所以就用SELECT TOP (100) PERCENT来限定，TOP (100) PERCENT是为了保证筛选出所有符合条件的数据条目。
PERCENT 代表的是 百分比
TOP (10) PERCENT 代表的是 10% 符合条件的数据
TOP (100) PERCENT 代表的是 100% 符合条件的数据
14、通过XML将字符串转换为表格列值
DECLARE @str VARCHAR(1000)
DECLARE @x xml
SET @str='aaa,bbb,ccc'
SELECT id
FROM (SELECT [value] = CONVERT(XML , '<v>' + REPLACE(@str , ',' , '</v><v>')+ '</v>')
) A
OUTER APPLY ( SELECT id = N.v.value('.' , 'varchar(100)')
FROM A.[value].nodes('/v') N (v)
) B
754
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
