随笔分类 -  sql

mysql将数据只保留一条
该文被密码保护。
posted @ 2019-11-16 15:51 一千零一夜 阅读(3) 评论(0) 推荐(0)
mysql生成自增编号
该文被密码保护。
posted @ 2019-11-16 12:02 一千零一夜 阅读(2) 评论(0) 推荐(0)
sql替换字符
摘要：declare @name varchar(100)='"acct_name"',@card varchar(100)='"id_no"' SELECT TOP 100 stuff( STUFF(Json, CHARINDEX(@name, Json) + LEN(@name) + 2, CHARI 阅读全文
posted @ 2016-02-23 18:09 一千零一夜 阅读(750) 评论(0) 推荐(0)
游标使用
摘要：SET NOCOUNT ONDECLARE @poc INT,@PurchaseOrderId NVARCHAR(40),@EBELP INT,@sql NVARCHAR(max),@PurchaseOrderDetailId NVARCHAR(40),@ExpenseShareId NVARCHA... 阅读全文
posted @ 2015-05-15 10:37 一千零一夜 阅读(216) 评论(0) 推荐(0)
数据库全文检索对比
摘要：declare @t1 datetime,@total intset @t1=GETDATE()select rowId,BuildName from (select top 2896 row_number() over( order by id asc) rowId,BuildName from tbl_data_House order by id asc) tb where tb.rowId>10 and tb.rowId10 and tb.rowId10 and tb.rowId10 and tb.rowId<=20print datediff(ms,@t1,getdate( 阅读全文
posted @ 2014-02-24 16:14 一千零一夜 阅读(498) 评论(0) 推荐(0)
sql去重
摘要：select id,OrderID,Mobile,Name from (select id,OrderID,Mobile,Name from V_Order where len(isnull(Mobile,''))>0 and State=0) Customer where id in (select min(id) from (select id,OrderID,Mobile,Name from V_Order where len(isnull(Mobile,''))>0 and State=0) Customer group by Mobile 阅读全文
posted @ 2013-09-12 18:20 一千零一夜 阅读(477) 评论(0) 推荐(0)
连续每天的数据
摘要：IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;create table #t(id int identity,Dt varchar(10));declare @starttime datetime,@endtime datetimeset @starttime = '===start==='set @endtime ='===end==='insert #tselect convert(varchar(10),dateadd(day,number,@starttime),112) dtf 阅读全文
posted @ 2013-05-14 11:23 一千零一夜 阅读(214) 评论(0) 推荐(0)
sqlserver区分大小写
摘要：CREATE FUNCTION StrComp(@Str1 VARCHAR(50),@Str2 VARCHAR(50))RETURNS INTEGERASBEGINDECLARE @i INTEGERDECLARE @y INTSET @i=0SET @y=1DECLARE @iLen INTSET @iLen = LEN(LTRIM(RTRIM(@Str1)))IF LEN(LTRIM(RTRIM(@Str1))) < LEN(LTRIM(RTRIM(@Str2)))SET @iLen = LEN(LTRIM(RTRIM(@Str2)))WHILE (@i < @iLen)BEG 阅读全文
posted @ 2012-12-10 10:53 一千零一夜 阅读(267) 评论(0) 推荐(0)
我的最新分页
摘要：ALTER proc [dbo].[pageing]( @tableName varchar(255), --表名 @showField varchar(1000), --显示的字段 @orderField varchar(255), --排序的字段 @pa... 阅读全文
posted @ 2012-11-13 17:45 一千零一夜 阅读(293) 评论(0) 推荐(0)
清除script注入
摘要：declare @t varchar(255),@c varchar(255)declare table_cursor cursor for select a.name,b.namefrom sysobjects a,syscolumns b ,systypes cwhere a.id=b.id and a.xtype='u' and c.namein ('char', 'nchar', 'nvarchar', 'varchar','text','ntext')declare @st 阅读全文
posted @ 2012-11-13 17:43 一千零一夜 阅读(202) 评论(0) 推荐(0)
查询当天数据
摘要：convert(varchar(10),H_houseUpdateTime,23)=convert(varchar(10),getdate(),23) 阅读全文
posted @ 2012-11-13 17:33 一千零一夜 阅读(128) 评论(0) 推荐(0)
按字母查找
摘要：create function [dbo].[f_GetPy](@str nvarchar(4000))returns nvarchar(4000)asbegindeclare @strlen int,@re nvarchar(4000)declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))insert into @t(chr,letter) select '吖 ', 'A ' union all select '八 ', ... 阅读全文
posted @ 2012-11-13 17:32 一千零一夜 阅读(169) 评论(0) 推荐(0)
sql去重
摘要：--完全相同的数据去掉重复select id,orderId from (select row_number() over(order by id) rowId,* from v_order) as tt where rowId in(select min(rowId) from (select row_number() over(order by id) rowId,* from v_order) bb group by id having count(id)>=1) 阅读全文
posted @ 2012-11-13 17:30 一千零一夜 阅读(773) 评论(0) 推荐(0)
sql生成不重复的邀请码
摘要：ALTER proc [dbo].[addUser]( @mid int output,@phone nvarchar(20),@regmid int,@pwd nvarchar(30),@relname nvarchar(50), @email nvarchar(200),@tel nvarchar(20),@sex nvarchar(2),@photo nvarchar(300),@IDNum nvarchar(20), @integral decimal(18,2),@flag char(1),@linkkey nvarchar(50),@qq nvarchar(30)... 阅读全文
posted @ 2012-11-13 17:29 一千零一夜 阅读(1079) 评论(0) 推荐(0)
sql递归查询
摘要：--获得上级with my as(select * from TB_Area where AreaID=72344566572777472union allselect aa.* from my,TB_Area aa where my.ParentID=aa.AreaID) select * from mygo--获得下级with my as(select * from TB_Area where AreaID=72339069014638592union allselect aa.* from my,TB_Area aa where my.AreaID=aa.ParentID) select 阅读全文
posted @ 2012-11-13 16:43 一千零一夜 阅读(153) 评论(0) 推荐(0)
sql触发器
摘要：if exists(select [name] from sys.objects where [name]='updateChild' and [type]='TR')drop trigger updateChildgocreate trigger updateChildon TB_ProductTypefor updateasif update(Pids) or update(Pid) or update([Level])begin update TB_ProductType set Pids=replace(pt.Pids,d.Pids,i.Pids), [ 阅读全文
posted @ 2012-11-13 16:42 一千零一夜 阅读(199) 评论(0) 推荐(0)