Database

create table t (n int); insert into t values (1); -- 13번 반복하면 4096행이 생성됨 insert into t select * from t SELECT * FROM t SELECT * FROM date_t create table date_t (d date, ds char(10));  insert into date_t select d, date_format(d, '%Y-%m-%d') from (   select @rnum:=@rnum+1 as rownum, date(adddate('2024-01-01', interval @rnum day)) as d   from (select @rnum:=-1) r, t   ) t where year(d)  [출처] http:/..

# YYYY-MM-DDselect * from  (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,  (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 ..

WITH nums ( num ) AS ( SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) -1 FROM sys.all_columns ) , dlist ( aDay ) AS ( SELECT TOP 500 DATEADD( DAY, num * -1, '2024-05-01' ) FROM nums ) , dlist2 ( aDay, TGD ) AS ( SELECT aDay, DATEADD( DAY, ( ( 8 - DATEPART( WEEKDAY, aDay ) ) % 7 ) - 3, aDay ) AS ThuOfGivenDay FROM dlist ) SELECT * , DATEPART( YEAR, TGD ) AS [년] , DATEPART( MONTH, TGD ) AS ..

https://blog.naver.com/sundooedu/221503687297 Elastic Search : 실시간 검색 및 분석 엔진 [차세대 분산 시스템] 데이터의 규모가 폭발적으로 증가하고 있는 시점이다. 증가하는 데이터의 문제를 해결하는 분산형 검색 및 ... blog.naver.com 나중에 보기

WITH RECURSIVE CTE AS ( SELECT DEPT_ID, DEPT_NAME, UP_DEPT_ID, DEPT_LOC, DEPT_SORT_ORDR, DEPT_STATUS, DEPT_DEPTH, TOP_DEPT_YN, 1 AS LEVEL FROM ORG_DEPARTMENT UNION ALL SELECT A.DEPT_ID, A.DEPT_NAME, A.UP_DEPT_ID, A.DEPT_LOC, A.DEPT_SORT_ORDR, A.DEPT_STATUS, A.DEPT_DEPTH, A.TOP_DEPT_YN, 1 + B.LEVEL AS LEVEL FROM ORG_DEPARTMENT A INNER JOIN CTE B ON A.UP_DEPT_ID = B.DEPT_ID ) SELECT DISTINCT FUNC_..

##########팀단위########## SELECT (SELECT NAME6 FROM APPLEJARA_PERSON BBBB WHERE NAME5=이름) AS "그룹", (SELECT NAME2 FROM TEST.APPLEJARA_PERSON_DEPT ZZ WHERE NAME3=부서) AS "본부", 부서 AS "팀", (SELECT NAME1 FROM TEST.APPLEJARA_PERSON_DEPT ZZ WHERE NAME3=부서) AS "순서", COUNT(부서) AS "인원", ROUND(AVG(초단위재직),0) "AVG(초단위)", CONCAT(AVG(초단위재직) DIV 86400,'.',DATE_FORMAT(SEC_TO_TIME(AVG(초단위재직) % 86400), '%H'),':',DATE..

SELECT NAME2 AS "부서", NAME4 AS "이름", NAME5 AS "직위", (SELECT NAME1 FROM TEST.PERSON ZZ WHERE NAME5=XX.NAME4) AS "순서", SUM_HH, SUM_MM, SUM_HH*60 AS SUM_HH_60, SUM_MM AS SUM_MM_60, SUM_HH*60 + SUM_MM AS SUM_FINAL, (SUM_HH*60 + SUM_MM)/60 AS SUM_F_HH, ((SUM_HH*60 + SUM_MM)/60)/24 AS SUM_F_DAY FROM ( SELECT NAME2, # "부서" NAME4, # "이름" NAME5, # "직위" NAME25, LPAD(NAME25,7,'00h '), SUBSTR(LPAD(NAME25,7,..

-- @sday, @eday 원하는 시작일자, 종료일자로 수정. -- @filename1, @filename2의 D:\MI_LOG\ 경로를 로그가 위치한 경로로 수정. DECLARE @SDate DATETIME, @EDate DATETIME, @query1 NVARCHAR(4000) DECLARE @sday VARCHAR(8) DECLARE @Eday VARCHAR(8) SET @sday = '20180711' SET @eday = '20181231' SET @SDate = CONVERT(datetime , @sday) SET @EDate = CONVERT(datetime , @eday) WHILE DATEDIFF(dd, @SDate, @EDate)>=0 BEGIN SET @sday = CONVERT(V..

#DB데이터 SELECT NAME3,NAME10,NAME11,NAME12,NAME13 FROM CHECK_VACATION_V2 WHERE NAME13 NOT REGEXP '반차|외근' ORDER BY NAME3 ASC #DB조회결과 SELECT t.NAME3 ,c.Numdate ,t.NAME12 ,t.NAME13 FROM CHECK_VACATION_V2 t JOIN view_calendar c ON c.Numdate BETWEEN DATE_FORMAT(t.NAME10, '%Y-%m-%d') AND DATE_FORMAT(t.NAME11, '%Y-%m-%d') WHERE NAME13 NOT REGEXP '반차|외근' ORDER BY NAME3,c.Numdate ASC ======================..

-- 2000-01-01 부터 + 9999 일까지 달력 만들기 WITH Calendar AS ( SELECT CONCAT(y, '0101') + INTERVAL tt*1000 + a*100 + b*10 + c DAY AS dt FROM (SELECT 0 AS tt UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) AS tt , (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT..