sql,Code片段,代码实例,代码示例,代码片段库

sql Where子句中的可选参数

也在mysql中工作

optArgSQL

SELECT NAME  
FROM TABLE  
WHERE  
    (@City IS NULL OR City = @City)
AND 
    (@Gender IS NULL OR Gender = @Gender)
AND 
    (@Age IS NULL OR Age = @Age)

sql SP创建新批次

mk_NewBatch.sql

alter proc mk_NewBatch(
     @description varchar(60), 
     @cashaccount varchar(10), 
     @nextbatch varchar(15) output
) as

begin

     declare @thisbatch varchar(10)
     declare @lastbatchnum int
     declare @nextbatchnum int

     set nocount on

     select   top 1 @thisbatch = BATCH_NUM
     from      Batch 
     where     left(BATCH_NUM,6) = format(getDate(), 'yyMMdd')
     order by DATE_CREATED desc

     if (@thisbatch is null)
          begin
              set @nextbatchnum = 1
          end
     else
          begin
              select @lastbatchnum = convert(int,right(@thisbatch, len(@thisbatch)-patindex('%-%',@thisbatch)))
              set @nextbatchnum = @lastbatchnum + 1
          end

     set @nextbatch = format(getDate(),'yyMMdd') + '-' + convert(varchar(2), @nextbatchnum)

     insert into Batch (BATCH_NUM,ORG_CODE,STATUS,DESCRIPTION,DATE_CREATED,CREATED_BY,LAST_UPDATED,UPDATED_BY,CONTROLLED,CONTROL_COUNT,CONTROL_CASH,TRANS_COUNT,TRANS_CASH,CASH_PSUEDO,CASH_GL_ACCT,AR_PSUEDO,AR_GL_ACCT,EFFECTIVE_DATE,CASH_ENTITY,CASH_ACCOUNT_CODE,INTENT_TO_EDIT,SOURCE_SYSTEM,DUES_CYCLE_FLAG,MEETING,HAS_EXPORTED_TRANS,WEB_BATCH)
     values (
          @nextbatch,
          '',
          0,
          @description,
          getDate(),
          'MANAGER',
          getDate(),
          'MANAGER',
          0,
          0,
          0,
          0,
          0,
          '',
          (select GL_ACCOUNT from Cash_Accounts where CASH_ACCOUNT_CODE = @cashaccount),
          '',
          '',
          convert(varchar(10),getDate(),126),
          '',
          @cashaccount,
          '',
          '',
          0,
          '',
          0,
          0
     )
     
end

sql PostgreSQLのUPSERT（复合ユニーク制约を含む）

upsert.sql

--同じデータが存在しなければ INSERT, 存在すれば UPDATE
INSERT INTO テーブル名 VALUES ('値１', '値２', ...）
ON CONFLICT ON CONSTRAINT 制約名
DO UPDATE SET カラム１='値', カラム２='値', ...;

constraint.sql

--複合ユニーク制約の付与
ALTER TABLE テーブル名 ADD CONSTRAINT 制約名称 UNIQUE(カラム1,カラム2,...);

--(複合)ユニーク制約の解除
ALTER TABLE テーブル名 DROP CONSTRAINT 制約名称;

sql 协会问题

协会问题

associations issue

select *
from datafiles_data
where Code in ('6674','6678', '7575')
and datafileid = 104

SELECT datafile_associations_data.AssociationID
      ,datafile_associations_data.DatafileDataID1 -- CustomerID
      ,datafile_associations_data.DatafileDataID2 -- DepotID
      ,datafile_associations_data.Properties
FROM datafile_associations_data
INNER JOIN datafile_associations
ON datafile_associations_data.AssociationID = datafile_associations.AssociationID
WHERE datafile_associations.AssociationName = 'CUSTOMER_DEPOTS'
AND datafile_associations_data.DatafileDataID1 in (30809,30897, 86143)

select *
from datafiles_data
where datafileid = 520
and datafiledataid in (24199,24201)

select *
from datafiles_data
where datafileid = 520
and datafiledataid in (24199,24201,32063,32064)


delete from datafile_associations_data
where DatafileDataID1 = 30809 
and DatafileDataID2 = 24199


delete from datafile_associations_data
where DatafileDataID1 = 30897 
and DatafileDataID2 = 24201

sql SQL.EXISTS

Example.sql

SELECT * FROM SYS_WF_ROLE A WHERE STATE_ID = 'c9374bc3-5fda-4316-bdc6-d1abaaf8e263'
								AND EXISTS(SELECT * FROM SYS_ROLE_USER B WHERE B.USER_ID = 'XUYI_A'
																		 AND A.ROLE_ID = B.ROLE_ID )

sql SQL.TempTable

Example.sql

IF Object_id('Tempdb..#INDIVIDUAL_CUSTOMER_CONTACT') IS NOT NULL
	BEGIN
		DROP TABLE #INDIVIDUAL_CUSTOMER_CONTACT
	END
	CREATE TABLE #INDIVIDUAL_CUSTOMER_CONTACT (ROWID int identity(1,1),
											   CUSTOMER_ID nvarchar(36),
											   CUSTOMER_NAME nvarchar(100),
											   CONTACT_ID nvarchar(36),
											   CONTACT_PERSON nvarchar(50),
											   CONTACT_MOBILE nvarchar(30),
											   CONTACT_EMAIL nvarchar(100),
											   CONTACT_TEL nvarchar(30),
											   WEXIN_CODE nvarchar(30),
											   QQ_CODE nvarchar(30),
											   REPEATED_CUSTOMER_ID nvarchar(36))

sql SQL.IF

Example.sql

IF @repeatedCustomerId != 'OCCUPATION'
			BEGIN
				UPDATE #SUPER_INDIVIDUAL_CUSTOMER 
				SET REPEATED_CUSTOMER_ID = @customerId 
				WHERE CUSTOMER_NAME = @customerName
			END
ELSE IF @repeatedCustomerId != 'OCCUPATION'
			BEGIN
				UPDATE #SUPER_INDIVIDUAL_CUSTOMER 
				SET REPEATED_CUSTOMER_ID = @customerId 
				WHERE CUSTOMER_NAME = @customerName
			END
ELSE 
			BEGIN
				UPDATE #SUPER_INDIVIDUAL_CUSTOMER 
				SET REPEATED_CUSTOMER_ID = @customerId 
				WHERE CUSTOMER_NAME = @customerName
			END

sql SQL.Cursor

Example.sql

DECLARE completeDataByCustomerName CURSOR LOCAL FOR
    SELECT CUSTOMER_ID, REPEATED_CUSTOMER_ID, CUSTOMER_NAME FROM #SUPER_INDIVIDUAL_CUSTOMER ORDER BY CUSTOMER_ID
    OPEN completeDataByCustomerName
	FETCH NEXT FROM completeDataByCustomerName INTO @customerId, @repeatedCustomerId, @customerName
    WHILE(@@FETCH_STATUS=0)
		BEGIN
			UPDATE #SUPER_INDIVIDUAL_CUSTOMER 
				SET REPEATED_CUSTOMER_ID = @customerId 
				WHERE CUSTOMER_NAME = @customerName 
                   
      FETCH NEXT FROM completeDataByCustomerName INTO @customerId, @repeatedCustomerId, @customerName
    END
    CLOSE completeDataByCustomerName

sql Link Equity Manager - 实时完整链接块

lem.sql

/* Gets list of link destinations by URL
  REI Acc ID: 36312
	REI Org ID: 6231 
	iapl.deploy_status = 0 for Preview
	iapl.deploy_status = 1 for Live
	preview_num_pages
	preview_h1
	preview_description
*/
select sources.u_url_id, targets2.url, sources.internal_url, sources.internal_url_id,
                        targets.deploy_num_pages,
                        #targets.title, 
                        #targets.h1, 
												targets.deploy_h1
												#targets.deploy_meta_description,
												#targets.http_status_code, targets.last_crawl_status
from ix_account_page_links sources
join ix_account_internal_links_crawl targets on targets.account_id = sources.account_id
and targets.u_url_id = sources.internal_url_id
join ix_account_internal_links_crawl targets2 on targets2.account_id = sources.account_id
and targets2.u_url_id = sources.u_url_id
where sources.account_id = 36312
and sources.deploy_status = 1
AND sources.status = 1
AND targets.is_valid = 1
order by targets2.url
#LIMIT 1000

sql Link Equity Manager - 预览完整链接块

lem.sql

/* Gets list of link destinations by URL
  REI Acc ID: 36312
	REI Org ID: 6231 
	iapl.deploy_status = 0 for Preview
	iapl.deploy_status = 1 for Live
	preview_num_pages
	preview_h1
	preview_description
*/
select sources.u_url_id, targets2.url, sources.internal_url, sources.internal_url_id,
                        targets.preview_num_pages,
                        targets.title,
												targets.h1, 
                        targets.preview_h1, 
												targets.preview_description,
												targets.http_status_code
from ix_account_page_links sources
join ix_account_internal_links_crawl targets on targets.account_id = sources.account_id
and targets.u_url_id = sources.internal_url_id
join ix_account_internal_links_crawl targets2 on targets2.account_id = sources.account_id
and targets2.u_url_id = sources.u_url_id
where sources.account_id = 36312
and sources.deploy_status = 0
AND sources.status = 1
AND targets.is_valid = 1
order by targets2.url