SQLServer技巧
-2.批量更新：
UPDATE p_ProductTab SET PC4=p.pc4
from (select productid='030301102000',pc4='423523' union all
select productid='030301102001',pc4='sdgser'
) as p
WHERE p_ProductTab.productid=p.productid
-1.保留2位小数
Convert(decimal(18,2),字段) 长度为18位，2位小数
0.null的判断
case when 字段名 is null then ** else ** end
将null替换为0: isnull(字段名,0)
1.搜索替换某个字段里的字符串
方法一:varchar和nvarchar类型是支持replace，所以如果你的text不超过8000可以先转换成前面两种类型再使用replace 替换 text ntext 数据类型字段的语句 。
update 表名 set 字段名=replace(cast(与前面一样的字段名 as varchar(8000)) ,'原本内容','想要替换成什么')
方法二:update [表名] set 字段名 = replace(与前面一样的字段名,'原本内容','想要替换成什么')
例子：UPDATE p_ProductTab SET Spec=replace(Spec,'，',',') where spec like N'%，%' COLLATE Chinese_PRC_CI_AS_WS
COLLATE Chinese_PRC_CI_AS_WS 全角和半角的指定
2.截取某个字段中的值
select substring(prlstr4,patindex('%合同号%',prlstr4)+4,6) as PO,substring(prlstr4,patindex('%客户料号%',prlstr4)+5,7) as PN,substring(prlstr4,patindex('%REV%',prlstr4)+4,3) as REV,substring(prlstr4,patindex('%其他要求%',prlstr4)+5,1000) as other
from (select top 5000 prlstr4 from OA_OrderBillDetailOriginalTab with(nolock) order by Id desc) b
where prlstr4 like '%1245991%'
3.获取自增长ID
select scope_identity()
4.初始化自增长ID，删除所有数据
truncate table tbname
5 .多条插入
INSERT INTO fp_TiaoXianSpec
select '1277661','ASY,ATTACHED ROUND CABLE,SMF,MPO-MPO,STD FERRULE,1m' union all
select '1277662','ASY,ATTACHED ROUND CABLE,SMF,MPO-MPO,STD FERRULE,2m' union all
select '1277663','ASY,ATTACHED ROUND CABLE,SMF,MPO-MPO,STD FERRULE,3m'
用于插入excel数据时，先在excel中处理字符串，然后copy到SQL查询器中
excel中连接字符串公式 =CONCATENATE("select ",A101,",'0",B101,"','",C101,"','','' union all")
数字转换成字符串公式 =TEXT（A6,"####")
还可以利用select 生成临时表：
join (select product='030301101660',amount=100 union all
select product='030301101561',amount=200 union all
select product='030301101462',amount=300 union all
select product='030301101363',amount=400)
6.获取系统时间getdate()，还可以进行计算getdate()-1等，后面的单位为天
7.生成数据字典
use fpErp --指定要生成数据字典的数据库
go
SELECT
表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
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 sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0
--where d.name='要查询的表' --如果只查询指定表,加上此条件
order by a.id,a.colorder
8.字符串截取与搜索特定字符位置
select id,fileNo,fileName,transdept,transdetail,transdone,returndetail,returndone,opdate,skfileid,fileid from fp_filehistory
where filemedia='1' and transdone='1' and CONVERT(datetime,SUBSTRING(transdetail,5,charindex(' ',transdetail)-5)) between getdate()-1 and getdate()
9.行列转换例子：
student course grade
--------- ---------- --------
student1 语文 50
student1 数学 60
student1 英语 40
student2 语文 30
student2 数学 40
student2 英语 50
select student,
max(case course when '语文' then grade else 0 end) as "语文",
max(case course when '数学' then grade else 0 end) as "数学",
max(case course when '英语' then grade else 0 end) as "英语"
from fp_colrol
group by student
结果
student1 82 65 75
student2 74 96 0
9.将表数据分组并合并列为一行：
select aa.*,bb.projmem from fp_projma aa
left join (select a.projno, STUFF(( SELECT ';' + projmem
FROM fp_projmem b
WHERE a.projno = b.projno
FOR XML PATH('')), 1, 1, '') AS projmem
FROM fp_projmem a GROUP BY a.projno) bb on bb.projno=aa.projno
例子：
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([nameid] nvarchar(24),[subject] nvarchar(22),[passy] nvarchar(21))
Insert #T
select N'A001',N'语文',N'Y' union all
select N'A001',N'数学',N'N' union all
select N'A002',N'语文',N'N' union all
select N'A002',N'英语',N'Y'
Go
--测试数据结束
SELECT a.nameid ,
STUFF(( SELECT ';' + [subject] + [passy]
FROM #T b
WHERE a.[nameid] = b.[nameid]
FOR
XML PATH('')
), 1, 1, '') AS type
FROM #T a
GROUP BY a.nameid
