정훈's 프로그래밍

SELECT columName , ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM dbo.Transactions 참조 사이트 https://stackoverflow.com/questions/44105691/row-number-without-order-by

while문 declare @no_image_list table ( seq_no int primary key identity(1,1) ) declare @seq_no int select @seq_no=min(seq_no) from @no_image_list while @seq_no is not null begin select * from someone -- while 조건절(필수) select @seq_no=min(seq_no) from @no_image_list where seq_no > @seq_no end apply문 select * from first_table as ft cross apply ( select * from second_table as st where st.c1 = ft.c1 ) a..

일반 버전 페이징 쿼리 MS-SQL-2012 부터 지원] 페이징 쿼리 declare @PageCounting int = 2 , @TopN int = 10 select * from dbo.Word as w with(nolock) order by w.RegisterDate offset (@PageCounting - 1) * @TopN row fetch next @TopN row only

declare @picking_no bigint = '' , @pl_cd varchar(10) = '' , @date_type int = 1 , @start_date datetime = '2020-5-31' , @end_date datetime = '2020-5-31' , @search_type int = 0 , @search_value varchar(25) = '' , @picking_status char(1) = 'A' begin declare @sql nvarchar(max) , @paramlist nvarchar(4000) set @sql = N' set nocount on declare @datetime_min_value datetime = ''1753-01-01'' if @end_date > ..

coalesceselect coalesce ( ( select count(*) from @BasicData group by bd.RegisterDate ) , 0) as AmountOfDailyWord

declare @t_picking_list_inserted_data table( picking_no int , cust_no int ) -- insert to [t_picking_list] insert into tir.dbo.t_picking_list( op_id , picking_yn , picking_work_no , cust_no , reg_id , reg_dt , chg_id , chg_dt , page_no , packing_no , pl_cd ) output inserted.picking_no , inserted.cust_no into @t_picking_list_inserted_data select @op_id , @picking_yn , @picking_work_no , sub_table...

begin tran insert into SDFSDF ( cj_invoice_id , contract_no , original_invoice_number , renewal_invoice_number ) values ( @cj_invoice_id , @contract_no , @original_invoice_number , @renewal_invoice_number ) if @@ERROR 0 and @@ROWCOUNT 1 begin if @@TRANCOUNT > 0 rollback tran return end insert into CJF ( contract_no , renewal_invoice_number , state_code ) values ( @contract_no , @renewal_invoice_..

CREATE FUNCTION [dbo].[fn_gmkt_split_array](@String NVARCHAR(4000), @Delimiter CHAR(1)) RETURNS @Results TABLE (Items NVARCHAR(4000)) AS BEGIN DECLARE @index INT DECLARE @slice NVARCHAR(4000) SELECT @index = 1 IF @String IS NULL RETURN WHILE @index != 0 BEGIN SELECT @index = CHARINDEX(@Delimiter,@String) IF @index !=0 SELECT @slice = left(@String,@index - 1) ELSE SELECT @slice = @String INSERT I..

변수 테이블 선언declare @temp table ( no varchar(50)) 임시 테이블 선언IF OBJECT_ID('tempdb..#temp_table') is not nullDROP TABLE #temp_tableCREATE TABLE #temp_table ( row_num int identity(1,1))CREATE NONCLUSTERED INDEX #temp_table_shipping_no ON #temp_table (shipping_no) With 테이블 선언with temp as ( select no from table )

select top 1 from table1 inner join table2 on a = b where a = b and a ='JP' and a = 'Y' select max(c) , max(d) from table1 inner join table2 on a = b where a = b and a ='JP' and a = 'Y' select max(c) , max(d) from table1 inner join table2 on a = b where a = b and a ='JP' and a = 'Y' group by table1.a "top 1" 과 "max()" 는 최종 검색 결과의 값중 1개만을 가져온다. (조건절에 의해 여러 값이 성립된다해도 최상단 1개만을 가져온다.) 하지만 "max()" 와 ..