SqlServer
笑是神的伪装
我欲乘风破浪,踏遍黄沙海洋
与其误会一场,也要不负勇往
我愿你是个谎,从未出现南墙
展开
-
SqlServer 索引定期重新生成
SELECT 'ALTER INDEX ['+ind.name + '] ON [dbo].[' +OBJECT_NAME(ind.OBJECT_ID) + '] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 9.原创 2020-11-30 11:11:20 · 1002 阅读 · 0 评论 -
jpa 拼接like
public static PredicateBuilder getPredicateBuilder(String name, String property){ //property 2个和3个的处理 String[] resultArr = property.split("\\."); //客户名称输入带空格的 自动拼成 如111 222 拼成(like 111 and like 222) PredicateBuilder and...原创 2020-09-01 11:06:41 · 936 阅读 · 0 评论 -
SQLSERVER 函数编写和调用
--创建函数ALTER FUNCTION [dbo].[f_inventory_monitor]( @yearMonth as varchar(30), @lastYearMonth as varchar(30))RETURNS TABLERETURN ( select * from sale)--调用函数SELECT * from f_inventory_monitor('2020-07','2020-06')...原创 2020-07-24 11:32:49 · 1977 阅读 · 0 评论 -
SQLSERVER 查看sql执行状态,死锁等待
SELECT [Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid), [User] = nt_username, [Status] = er.status, [Wait] = wait_type, [Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2, (CASE WHEN er.statement_end_offset = - 1 THEN.原创 2020-07-13 11:42:37 · 1096 阅读 · 0 评论 -
sqlserver 创建带返回值的存储过程
--创建存储过程ALTER PROCEDURE [dbo].[pro_sales_month] @yearMonth varchar(30), @returnValue VARCHAR(10) outputASBEGIN TRY BEGIN TRANSACTION insert into crm_platform_kingyork_staging.dbo.KY_SD_DATA_M SELECT es.id as PK_ID,es.sale_year_month as ST.原创 2020-07-07 11:05:06 · 1360 阅读 · 0 评论 -
sqlServer 查询A表比B表多的数据
--B表比A表多的数据select * from ent_slf_expect_inventory_copy1 B where (select count(1) as num from ent_slf_expect_inventory A where A.institution_id = B.institution_idand B.product_id = A.product_idand...原创 2020-04-08 09:54:47 · 1359 阅读 · 0 评论 -
sql 查询重复记录并删除
--查询重复记录select original_product_name,original_product_specfrom ent_sfl_product_mappingwhere deleted = 0group by original_product_name,original_product_spec having count(1) > 1--删除重复记录d...原创 2020-03-16 14:58:58 · 689 阅读 · 0 评论 -
oracle,db2,sqlserver 复制表结构及数据
oracle复制表结构及其数据:create table table_name_new as select * from table_name_olddb2复制表结构及其数据 需要两句:create table table_name_new like table_name_oldinsert into table_name_new select * from table_na...原创 2019-01-11 19:39:19 · 898 阅读 · 0 评论 -
SqlServer 获取时间月份
SqlServer当月最后一天SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))上个月第一天SELECT CONVERT ( VARCHAR ( 100 ), DATEADD( MONTH, DATEDIFF( MONTH, 0,getdate()) - 1, 0 ), 23 )当月第一天SE...原创 2019-10-31 19:34:43 · 3651 阅读 · 0 评论