--1.导入去重：导入+去 重 sql中传入一个用户自定义表tb,删除现表与传入中已有的数据 DELETE dbo.SendExtendFee FROM @Tb b WHERE b.SendSite = dbo.SendExtendFee.SendSite AND b.SendWeb = SendExtendFee.SendWeb AND b.Cooperation=SendExtendFee.Cooperation AND b.CarLength=SendExtendFee.CarLength; --之后在进行新增INSERT dbo.SendExtendFee ( ID , SendSite , SendWeb , Cooperation , CarLength , Price ) SELECT NEWID() , SendSite , SendWeb , Cooperation , CarLength , Price FROM @Tb; --2.数据表新增列：alter table table_name add col_name VARCHAR(50) --3.创建临时表： ① WITH etc AS ( SELECT * FROM dbo.BillVehicleStar WHERE Type=‘配载‘ ) ②DECLARE @tmpStrTwo TABLE ( str1 VARCHAR(30) , str2 VARCHAR(200) ); 加入数据： INSERT INTO @tmpStrTwo ( str1 , str2 ) SELECT str1 , str2 FROM L_SplitStr_TWO(@BillNos, @MiddleBillNos, ‘@‘); 4..将decimal转换成varchar: convert(varchar, cast(Money as Money)) AS Money SELECT CONVERT(INT, SUBSTRING(‘431223199903312214‘,7,4))----19995.SQL查询字段出现在所有存储过程： select name from sysobjects o, syscomments s where o.id = s.id and text like ‘%text%‘ and o.xtype = ‘P‘ -------------------------- select distinct name from sysobjects o, syscomments s where o.id = s.id and (o.xtype = ‘P‘ or o.xtype = ‘FN‘ or o.xtype = ‘TF‘ or o.xtype = ‘TR‘ or o.xtype = ‘V‘) and text like ‘%字段名%‘ 6.增加字段说明(注释): execute sp_addextendedproperty ‘MS_Description‘, ‘说明‘, ‘user‘, ‘dbo‘, ‘table‘, ‘表名‘, ‘column‘, ‘列名‘ 修改字段说明: EXEC sp_updateextendedproperty ‘MS_Description‘,‘说明‘,‘user‘,dbo,‘table‘,‘表名‘,‘column‘,‘列名‘7.数据库判断是否符合，并返回： IF NOT EXISTS ( SELECT * FROM billDeparture WHERE DepartureBatch = @DepartureBatch AND ISNULL(VerifyMan, ‘‘) = ‘‘ ) BEGIN RAISERROR(‘本车已审核,不能修改本车信息!‘,16,1); RETURN; END; 8.SQL设置特定日期（日）（在特定日期范围内判断）： declare @bgdate varchar(100) declare @eddate varchar(100) DECLARE @ndate VARCHAR(100) select @bgdate= CONVERT(varchar(10), CONVERT(varchar(8),dateadd(month,0,getdate()),23)+‘15‘ , 23)+ ‘ 00:00:00‘ --15号00:00 select @eddate=CONVERT(varchar(10), CONVERT(varchar(8),dateadd(month,0,getdate()),23)+‘24‘ , 23)+ +‘ 23:59:59‘ --24号23：59 SELECT @ndate=GETDATE() 9.11.两个日期之间相差多少天限制： IF(DATEDIFF(DAY,@bdate,@edate)>30) BEGIN RAISERROR(‘一次性最多提取30天数据！！！‘, 16, 1) WITH NOWAIT; RETURN; END 10.相差时间: Select 1 From billDepartureList l Where l.AcceptSiteName=@LoginSiteName And l.AcceptWebName=@LoginWebName And a.BillNo=l.BillNO And l.RemainWebPCS>0 and l.AcceptBillDate>=DATEADD(MONTH, -3, GETDATE() where DATEDIFF(DAY,DeliTime,GETDATE())<=3 ②查询当前时间加上多少小时后的时间 select dateadd(hour,24,getdate()) as nowdat --当前时间加上24小时 11. 清空表内数据：truncate TABLE TimelinessReport_Log 删除表：drop table TimelinessReport_Log 更改数据库字段类型： alter table 表名 alter column 字段名 type not null 修改字段名： sp_rename ‘表名.旧字段名‘,‘新字段名‘ 增加字段： alter table 表名 add 字段名 type not null default 0 删除字段： alter table 表名 drop column 字段名; 12.判断是否存在该记录： IF ( EXISTS ( SELECT BillId FROM WayBill WHERE BillNo = @BillNo ) ) BEGIN RAISERROR(‘运单号已开过运单，请检查!‘,16,1); RETURN; END; 13.批量SQL： 》①新增： INSERT INTO Middle_Add_SysFee (BillNo,SysMiddleFreight,MinimumBill,HeavyPrice,LightPrice) SELECT str1,str2,@MinimumBill,@HeavyPrice,@LightPrice FROM L_SplitStr_TWO (@BillNos,@SysMiddleFreights,‘@‘); ②修改： 》单个修改：UPDATE RewardData SET isAudit=‘已审核‘ WHERE ID IN(SELECT str1 FROM dbo.L_SplitStr_ONE(@IDS,‘@‘)); 》多个值修改：①：建立临时表 --yzw DECLARE @tmpStrTwo1 TABLE ( str1 VARCHAR(30) , str2 VARCHAR(200) ); 》②插入用户定义表： --yzw INSERT INTO @tmpStrTwo1 ( str1 , str2 ) SELECT str1 , str2 FROM L_SplitStr_TWO(@BillNos, @SysMiddleFreights, ‘@‘); 》 ③对应字段修改： UPDATE Middle_Add_SysFee SET SysMiddleFreight=b.str2,MinimumBill=@MinimumBill,HeavyPrice=@HeavyPrice,LightPrice=@LightPrice FROM @tmpStrTwo1 AS b WHERE BillNo=b.str1 14.①Sql中CHARINDEX用法 函数 》判断一个字符串中是否包含另一个字符串 -CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] ) expressionToFind ：目标字符串，就是想要找到的字符串，最大长度为8000 。 expressionToSearch ：用于被查找的字符串。 start_location：开始查找的位置，为空时默认从第一位开始查找。 》例子：select charindex(‘test‘,‘this Test is Test‘) --输出结果：6 select charindex(‘test‘,‘this Test is Test‘,7) --输出结果：14 》大小写敏感： select charindex(‘test‘,‘this Test is Test‘COLLATE Latin1_General_CS_AS) -COLLATE Latin1_General_CS_AS 大小写敏感 》大小写不敏感： select charindex(‘Test‘,‘this Test is Test‘COLLATE Latin1_General_CI_AS) -COLLATE Latin1_General_CI_AS 大小写不敏感 ②Sql中PATINDEX用法： 》和CHARINDEX类似，PATINDEX也可以用来判断一个字符串中是否包含另一个字符串，两种的差异在于，前者是全匹配，后者支持模糊匹配。 》例子：select PATINDEX(‘%ter%‘,‘interesting data‘) --输出结果：3 select PATINDEX(‘%t_ng%‘,‘interesting data‘) --输出结果：8 _为不知单词可查 ③left 用法: >>>>>截取>从左开始截取几位 left() LEFT (<character_expression>， <integer_expression>) 返回character_expression 左起 integer_expression 个字符。 >实例:select left(‘abcdef‘,3) >>>>abc ④stuff用法: >>>>>替换>从第几位开始,几位数,替换成特定 实例: 以下示例在第一个字符串 abcdef 中删除从第 2 个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串。 SELECT STUFF(‘abcdef‘, 2, 3, ‘ijklmn‘); >>>>>>>>>>>aijklmnef ---abcdef从第二字符开始三个字符替换成ijklmn ⑤SUBSTRING取值用法:>>>>>>>>>截取,从第几位开始,截取几位 SELECT SUBSTRING(CONVERT(VARCHAR(50), ‘362329199306041315‘, 111), 7, 8) AS birthday ⑥ replace函数 替换函数>>>>特定的替换 update 表 set 家庭住址 = replace(家庭住址,‘一楼‘,‘‘) 把一楼替换成空的 -- 把ReceivOrderNo 中的特定@delic+‘,‘ 替换为空 UPDATE waybill SET ReceivOrderNo=REPLACE(ReceivOrderNo,@delic+‘,‘,‘‘) WHERE BillNo=@bino; ⑦LEN函数 : 返回给定字符串表达的字符数 SELECT LEN(‘abcdefg‘)--7 SELECT LEN(N‘abcdefg‘)--7 SELECT LEN(‘‘)--0
16.时间差值返回：
1 IF ( ABS(DATEDIFF(HOUR, @WebDate, GETDATE())) > 720 ) --发车日期和当前日期相差144小时以上 2 BEGIN 3 RAISERROR(‘发车日期超过720小时，不能补货!‘,16,1); 4 RETURN; 5 END;
17.同一批次油卡和现付分开显示（显示两行）
1 SELECT DepartureBatch, OilCardFee, 0 AS NowPayDriver FROM dbo.billDeparture 2 WHERE ISNULL(OilCardFee, 0) > 0 AND DepartureBatch = ‘CD201707-0016‘3 UNION ALL4 SELECT DepartureBatch, 0 AS OilCardFee, NowPayDriver FROM dbo.billDeparture 5 WHERE ISNULL(NowPayDriver, 0) > 0 AND DepartureBatch = ‘CD201707-0016‘
18.执行存储在字段中的SQL ：
1 DECLARE @sqlstr1 VARCHAR(MAX) , 2 @AuditingMan1 VARCHAR(100) , 3 @ApprovalMan1 VARCHAR(100); 4 DECLARE @BillNo1 VARCHAR(100) , 5 @ApplyMan1 VARCHAR(100) , 6 @ApplyType1 VARCHAR(100); 7 SELECT @sqlstr1 = SqlStr , 8 @AuditingMan1 = AuditingMan , 9 @ApprovalMan1 = ApprovalMan , 10 @BillNo1 = BillNO , 11 @ApplyMan1 = ApplyMan , 12 @ApplyType1 = ApplyType 13 FROM billApply 14 WHERE ApplyID = @ApplyID; 15 16 EXEC(@sqlstr1);
19.单存储过程查询多个datatable，作为一个结果集，返回并调用：
1 》查询1：》SELECT place , 2 bm , 3 regione , 4 businessdepartment , 5 qymc 6 FROM B_YGDA 7 WHERE bh LIKE @UserAccount; 8 9 》查询2：》 SELECT CASE WHEN @UserAccount IN ( SELECT UserAccount 10 FROM sysUserInfo 11 WHERE GRCode LIKE ‘%367%‘ ) 12 THEN ‘人事高级权限组‘ 13 WHEN @UserAccount IN ( SELECT UserAccount 14 FROM dbo.sysUserInfo 15 WHERE GRCode LIKE ‘%375%‘ ) 16 THEN ‘人事部门经理权限组‘ 17 ELSE ‘人事普通权限组‘ 18 END AS GRCode 19 FROM dbo.sysUserInfo 20 WHERE @UserAccount = UserAccount;
20.带参数返回：
1 RAISERROR(‘派车单号 %s 已经存在，请重新保存即可!‘,16,1,@DeliCode);
21.同一单号不同费相减取值:(重)
1 (SELECT SUM(d.Money) FROM basAccDetail d WHERE d.billno=b.BillNo AND d.FeeType=‘收送货费‘ )2 -(SELECT SUM(d.Money) FROM basAccDetail d WHERE d.billno=b.BillNo AND d.FeeType=‘付送货费‘ ) AS money,
22.查询作为字段
1 CASE WHEN ( SELECT COUNT(1) 2 FROM dbo.basTransferCode e 3 WHERE e.BillNos IN (SELECT str1 FROM dbo.L_SplitStr_ONE(@BillNos,‘@‘)) 4 ) > 0 THEN ‘已发送‘ 5 ELSE ‘未发送‘ 6 END AS randcode_state,--验证码是否发送
23.当前时间+须要增加的时间
SET @EndDate = CONVERT(DATETIME, ( CONVERT(VARCHAR, DATEADD(HOUR, ( @96 ), GETDATE()), 23) + ‘ 23:59:59‘ )); ==当前日期+4天 23:59:59
24.新增批量: in (select str1 from @tempOne)---
INSERT INTO @tmpStrTwo--增加未关联派车单的费用 ( str2 ) SELECT ISNULL(VehFare,0) FROM billDelivery a,@tmpStrOne b WHERE b.str3<>‘‘ and a.DeliCode IN(SELECT str3 FROM @tmpStrOne)
25.金额取两位小数:
1 1. 使用 Round() 函数，如 Round(@num,2) 参数 2 表示 保留两位有效数字。2 3 2. 更好的方法是使用 Convert(decimal(18,2),@num) 实现转换，decimal(18,2) 指定要保留的有效数字。4 5 这两个方法有一点不同：使用 Round() 函数，如果 @num 是常数，如 Round(2.3344,2) 则 会在把有效数字后面的 变为0 ，成 2.3300。但 Convert() 函数就不会。
26.查看锁表:
1 SELECT 2 request_session_id spid, 3 OBJECT_NAME( 4 resource_associated_entity_id 5 ) tableName 6 FROM 7 sys.dm_tran_locks 8 WHERE 9 resource_type = ‘OBJECT‘ 10 ORDER BY request_session_id ASC
27.将时间转化为连续字符串+随机数:
1 SELECT CONVERT(varchar(19),getdate(),112)+DateName(hour,GetDate())+ DateName(minute,GetDate())+DateName(second,GetDate())+DateName(ms,GetDate())2 +cast( floor(rand()*5000)+5000 as VARCHAR(500))
28.SQL将时间转化为字符串输出
1 convert(varchar(100),字段,20) as 字段2 CONVERT(VARCHAR(100), BillDate, 23) as
29.导入有相同的则修改,没有的则新增
1 CREATE PROC USP_ADD_DepartmentBudget_IMPORT 2 @Tb Table_BasDepartmentBudget READONLY , 3 @LoginAreaName VARCHAR(30) ,--登录大区 4 @LoginCauseName VARCHAR(30) ,--登录事业部 5 @LoginDepartName VARCHAR(30) ,--登录部门 6 @LoginSiteName VARCHAR(30) ,--登录站点 7 @LoginWebName VARCHAR(30) ,--登录网点 8 @LoginUserAccount VARCHAR(30) ,--登录账号 9 @LoginUserName VARCHAR(30)--登录姓名 10 AS11 BEGIN TRAN TR; 12 BEGIN TRY 13 14 DECLARE @TempTableOne TABLE--传入表与实体表相同的数据15 (16 Year int,17 Month int,18 DepName VARCHAR(100),19 StandardBudgetAmount DECIMAL(18,2)20 )21 DECLARE @TempTableTwo TABLE--实体表中没有的数据22 (23 Year int,24 Month int,25 DepName VARCHAR(100),26 StandardBudgetAmount DECIMAL(18,2)27 )28 29 --插入相同数据30 INSERT INTO @TempTableOne31 SELECT a.Year,a.Month,a.DepName,a.StandardBudgetAmount FROM @Tb a INNER JOIN DepartmentBudget b ON a.Year=b.Year AND a.Month=b.Month AND a.DepName=b.DepName;32 33 --修改实体表数据34 UPDATE b SET b.StandardBudgetAmount=a.StandardBudgetAmount,b.BudgetUpdateTime=GETDATE(),BudgetUpdateMan=@LoginUserName FROM @Tb a,DepartmentBudget b WHERE b.Year=a.Year AND b.Month=a.Month AND b.DepName=a.DepName;35 36 --@Tb删除@TempTableOne有的数据放入@TempTableTwo37 INSERT INTO @TempTableTwo38 SELECT * FROM @Tb a WHERE NOT EXISTS(SELECT 1 FROM @TempTableOne b WHERE a.Year=b.Year AND a.Month=b.Month AND a.DepName=b.DepName );39 40 --剩下的新增进实体表41 INSERT INTO DepartmentBudget42 (Year,Month,DepName,StandardBudgetAmount,BudgetCreatTime)43 SELECT 44 Year,45 Month,46 DepName,47 StandardBudgetAmount,48 getdate()49 FROM @TempTableTwo
30.设定每天特定时间段不能提取数据
1 IF ( CONVERT(VARCHAR, GETDATE(), 20) > ( CONVERT(VARCHAR, GETDATE(), 23)2 + ‘ 08:30:00‘ )3 AND CONVERT(VARCHAR, GETDATE(), 20) < ( CONVERT(VARCHAR, GETDATE(), 23)4 + ‘ 10:00:00‘ )5 )6 BEGIN 7 RAISERROR(‘该段时间总部人员暂停使用该模块,有需要可联系IT人员!‘,16,1); 8 RETURN; 9 END;
31.查询同一批次单号内的件数,用@拼接成一个字段 关键字:多个 拼接成一个
1 --示例一: --插入库存 2 DECLARE @OptNums VARCHAR(8000) = ‘‘; 3 DECLARE @SCWeb VARCHAR(8000) = ‘‘; 4 DECLARE @SCDesWeb2 VARCHAR(100) = ‘‘; 5 DECLARE @BillNoStr1 VARCHAR(8000);--,@AcceptBillMan varchar(30) 6 SELECT @BillNoStr1 = ‘‘; 7 SELECT @BillNoStr1 = @BillNoStr1 + a.BillNo + ‘@‘ , 8 @OptNums = @OptNums + CONVERT(VARCHAR(50), b.Num) + ‘@‘ , 9 @SCWeb = a.SCWeb , 10 @SCDesWeb2 = a.SCDesWeb 11 FROM ShortConnDetail a WITH ( NOLOCK ) 12 LEFT JOIN dbo.WayBill b WITH ( NOLOCK ) ON a.BillNo = b.BillNo 13 WHERE SCBatchNo = ‘LE2017113016584389‘; 14 15 示例二: 16 DECLARE @Nums VARCHAR(8000)=‘‘; 17 SELECT @Nums=@Nums+CONVERT(VARCHAR(50),Num)+‘@‘ FROM waybill WHERE billno IN ( ‘11609273‘,‘11609381‘,‘11609383‘,‘11609384‘,‘11609385‘,‘11609386‘,‘11609499‘,‘11609500‘,‘11609557‘,‘11609558‘ ); 18 SELECT @Nums; 19 20 21 拓展一:一个批次内多个单号,不同中转承运公司,用批次号汇总时,中转承运公司用逗号隔开: 22 WITH etc 23 AS ( SELECT MiddleBatch , 24 MiddleCarrier 25 FROM Middle_HelpTB 26 WHERE PreMiddleDate BETWEEN ‘2018-10-10 00:00:00‘ AND ‘2018-11-17 23:59:59‘ 27 GROUP BY MiddleBatch , 28 MiddleCarrier 29 )-----注:避免重复承运公司,先把有重复的用临时表分组汇总 30 SELECT b.MiddleBatch ,--配载批次号(中转批次号) 31 MAX(b.MiddleDate) AS MiddleDate ,--(配载时间，中转配载中点击转实际配载按钮之后生成的时间) 32 MAX(b.PreMiddleDate) AS PreMiddleDate ,--预配载时间（中转完成时间） 33 MAX(ISNULL(b.MiddleWebName, b.PreMiddleWebName)) AS MiddleWebName ,--配载网点(中转配载操作新增网点) 34 -- MAX(b.MiddleCarrier) AS MiddleCarrier,--承运公司 35 --重点部分,用逗号分隔 36 CONVERT(VARCHAR(MAX), ( SELECT STUFF(( SELECT ‘,‘ 37 + w.MiddleCarrier 38 FROM etc w 39 WHERE w.MiddleBatch = b.MiddleBatch 40 FOR 41 XML PATH(‘‘) 42 ), 1, 1, ‘‘) 43 )) AS MiddleCarrier ,--承运公司 --注:再根据分好组的承运公司用逗号 44 --重点部分,用逗号分隔 45 COUNT(b.BillNo) AS BillCount ,--票数 46 SUM(a.FeeVolume) AS FeeVolume ,--计费体积 47 SUM(a.FeeWeight) AS FeeWeight ,--计费重量 48 MAX(b.MiddleOperator) AS MiddleOperator ,--实际配载经办人 49 MAX(b.MiddleSendFee) AS MiddleSendFee ,--转送费 50 MAX(b.MiddleChauffer) AS MiddleChauffer ,--转送司机 51 MAX(b.MiddleChaufferPhone) AS MiddleChaufferPhone ,--转送司机电话 52 MAX(b.MiddleCarNo) AS MiddleCarNo ,--转送车牌号 53 SUM(CONVERT(DECIMAL(18, 2), ISNULL(b.MiddleBackFee, 0))) AS MiddleBackFee ,--转送回扣 54 MAX(b.PrintTime) AS PrintTime ,--打印时间 55 CASE WHEN MAX(ISNULL(b.PrintTime, ‘‘)) = ‘‘ THEN ‘‘ 56 ELSE ‘已打印‘ 57 END AS IsPrint 58 FROM WayBill a 59 INNER JOIN Middle_HelpTB b ON b.BillNo = a.BillNo 60 WHERE b.PreMiddleDate BETWEEN ‘2018-10-10 00:00:00‘ AND ‘2018-11-17 23:59:59‘ 61 GROUP BY b.MiddleBatch; 62 63 拓展二: 64 --根据分拨批次号得到内的单号的所有中转地,用逗号隔开 65 CREATE PROCEDURE QSP_GET_DEPARTURE_FB 66 @t1 DATETIME , 67 @t2 DATETIME , 68 @CauseName VARCHAR(50) , 69 @AreaName VARCHAR(50) , 70 @SiteName VARCHAR(50) , 71 @WebName VARCHAR(50) , 72 @LoginAreaName VARCHAR(30) ,--登录大区 73 @LoginCauseName VARCHAR(30) ,--登录事业部 74 @LoginDepartName VARCHAR(30) ,--登录部门 75 @LoginSiteName VARCHAR(30) ,--登录站点 76 @LoginWebName VARCHAR(30) ,--登录网点 77 @LoginUserAccount VARCHAR(30) ,--登录账号 78 @LoginUserName VARCHAR(30)--登录姓名 79 AS 80 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 81 82 DECLARE @errorcode INT; 83 DECLARE @errormsg NVARCHAR(20); 84 SELECT @errorcode = errorcode , 85 @errormsg = errormsg 86 FROM Fun_IsExitPermissions(@LoginUserAccount, @CauseName, @AreaName, 87 @WebName, @LoginCauseName, @LoginAreaName, 88 @LoginWebName, 121583, 121585, 121581); 89 IF @errorcode <= 0 90 RAISERROR(@errormsg, 16, 1) WITH NOWAIT; 91 ELSE 92 BEGIN 93 SELECT MAX(a.PID) PID , 94 a.AllocateBatch , 95 MAX(a.CarNo) CarNo , 96 MAX(a.DriverName) DriverName , 97 MAX(a.DriverPhone) DriverPhone , 98 SUM(OperationWeight) AS OperationWeight , ---gxh 99 --重点部分,用逗号分隔 100 CONVERT(VARCHAR(MAX), ( SELECT STUFF(( SELECT101 ‘,‘102 + w.TransferSite103 FROM104 dbo.WayBill w ,105 dbo.billDepartureFBList bd106 WHERE107 bd.AllocateBatch = MAX(a.AllocateBatch)108 AND w.BillNo = bd.BillNo109 FOR110 XML111 PATH(‘‘)112 ), 1, 1, ‘‘)113 )) AS TransferSite114 --重点部分,用逗号分隔 115 FROM billDepartureFB a116 OUTER APPLY ( SELECT MIN(AcceptMan) AcceptMan ,117 MAX(ISNULL(AcceptDate, ‘‘)) AS AcceptDate--hj 118 FROM LEY_Test20180802.dbo.billDepartureFBList b119 WHERE a.AllocateBatch = b.AllocateBatch120 ) AS b121 LEFT JOIN billDepartureFBList d ON d.AllocateBatch = a.AllocateBatch --luohui 122 LEFT JOIN dbo.WayBill c ON c.BillNo = d.BillNo --luohui 123 WHERE a.SendDate BETWEEN @t1 AND @t2124 AND a.FromCause LIKE @CauseName125 AND a.FromArea LIKE @AreaName126 AND a.FromSiteName LIKE @SiteName127 AND a.FromWebName LIKE @WebName128 AND ISNULL(a.AcceptCompanyId, ‘‘) <> ‘‘129 GROUP BY a.AllocateBatch; 130 131 END;
32.游标及直接查询使用:
1 ------------------------------------利用游标不定义表查询--格式:单号-系统配载运费+,+单号-系统配载运费---------------------------------------- 2 DECLARE @a VARCHAR(100); 3 DECLARE @b VARCHAR(100); 4 DECLARE @c VARCHAR(max)=‘‘; 5 DECLARE cu CURSOR 6 FOR 7 SELECT Billno,SysMiddleFreight FROM Middle_HelpTB WHERE MiddleBatch=‘PL2018081110004225‘; 8 9 OPEN cu ;10 FETCH cu INTO @a,@b;11 WHILE(@@FETCH_STATUS=0)12 BEGIN13 SET @c=@c+‘,‘+@a+‘-‘+@b;14 FETCH cu INTO @a,@b;15 END16 CLOSE cu;17 DEALLOCATE cu;18 19 SELECT SUBSTRING(CONVERT(VARCHAR(300),@c),2,LEN(@c)-1) AS c;>>>20 结果:13091195-24.00,12315824-7312.00,13050337-320.00,13067207-800.0021 ------------------------------直接查询出来--格式:单号-系统配载运费+,+单号-系统配载运费------------------------------------------22 DECLARE @d VARCHAR(max)=‘‘;23 SELECT @d=@d+‘,‘+Billno+‘-‘+SysMiddleFreight FROM Middle_HelpTB WHERE MiddleBatch=‘PL2018081110004225‘;24 SELECT SUBSTRING(CONVERT(VARCHAR(300),@d),2,LEN(@d)-1) AS d;25 >>>结果:13091195-24.00,12315824-7312.00,13050337-320.00,13067207-800.00
33.查询是否有该数据库插入数据
1 IF EXISTS ( SELECT 1 2 WHERE DB_NAME() = ‘KMS20160713‘ ) 3 BEGIN 4 INSERT dbo.QueryLog 5 ( ID , 6 FromName , 7 ProcedureName , 8 Bdate , 9 Edate ,10 OptAccount ,11 OptMan ,12 OptDate13 )14 VALUES ( NEWID() ,15 ‘送货调度-按票‘ ,16 ‘QSP_GET_SEND_BILL_Clone‘ ,17 @t1 ,18 @t2 ,19 @LoginUserAccount ,20 @LoginUserName ,21 GETDATE()22 );23 END;
34.SQL查询一个字符在一个字段中出现的次数:
select (len(‘豪成-朱俊海‘)-len(replace(‘豪成-朱俊海‘, ‘-‘,‘‘)))/len(‘-‘) AS cc >>>>>>>1
35.跨库查询:
select * from OPENDATASOURCE(‘SQLOLEDB‘,‘Data Source=119.23.132.112;User ID=lq;Password=lq123123‘).KMSLad.dbo.WayBillab
36..执行SQL:
EXECUTE sp_executesql @SQL;
37.SqlServer 计算表个数:
SqlServer 计算表个数: SELECT count(*) FROM sys.objects WHERE type=‘U‘ 计算视图个数 SELECT count(*) FROM sys.objects WHERE type=‘V‘ 计算存储过程个数 SELECT count(*) FROM sys.objects WHERE type=‘P‘
38.查询自带自增长序号:
select ROW_NUMBER() OVER (ORDER BY b.BillNO ASC) AS 序号
39.SQL字段加注释说明
1 表:2 新增：exec sp_addextendedproperty N‘MS_Description‘, N‘表备注内容‘, N‘SCHEMA‘, N‘dbo‘,N‘table‘, N‘表名‘;3 修改：exec sp_updateextendedproperty N‘MS_Description‘, N‘表备注内容‘, N‘SCHEMA‘, N‘dbo‘,N‘table‘, N‘表名‘;4 5 字段添加和修改备注：6 新增：exec sp_addextendedproperty N‘MS_Description‘, N‘字段备注内容‘, N‘SCHEMA‘, N‘dbo‘,N‘table‘, N‘表名‘,N‘column‘, N‘字段名‘;7 修改：exec sp_updateextendedproperty N‘MS_Description‘, N‘字段备注内容‘, N‘SCHEMA‘, N‘dbo‘,N‘table‘, N‘表名‘,N‘column‘, N‘字段名‘;
