유용한 쿼리 모음
/* 평균 구하기*/
SELECT AVG(column_name ) AS column_name_Average FROM table_name
/* 중복제거 갯수 구하기 */
SELECT COUNT(DISTINCT column_name ) FROM table_name
/* mssql */
/* 칼럼의 첫번째 값 가져오기 */
SELECT TOP 1 column_name FROM table_name ORDER BY column_name ASC;
/* mysql */
/* 칼럼의 첫번째 값 가져오기 */
SELECT column_name FROM table_name ORDER BY column_name ASC Limit 1;
/* oracle */
/* 칼럼의 첫번째 값 가져오기 */
SELECT column_name FROM table_name WHERE ROWNUM <=1 ORDER BY column_name ASC;
/* mssql */
/* 칼럼의 마지막 값 가져오기*/
SELECT TOP 1 column_name FROM table_name ORDER BY column_name desc;
/* mysql */
/* 칼럼의 마지막 값 가져오기*/
SELECT column_name FROM table_name ORDER BY column_name DESC LIMIT 1;
/* oracle */
/* 칼럼의 마지막 값 가져오기*/
SELECT column_name FROM table_name ORDER BY column_name DESC WHERE ROWNUM <=1;
/* 최대값 구하기 */
SELECT MAX(column_name) FROM table_name;
/* 최소값 구하기 */
SELECT MIN(column_name) FROM table_name;
/* 칼럼의 값 합산하기 */
SELECT SUM(column_name) FROM table_name;
/* 그룹바이 */
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
/* HAVING */
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
/* mysql, oracle 대문자로 변환 */
SELECT UCASE(column_name) FROM table_name;
/* ms-sql 대문자로 변환*/
SELECT UPPER(column_name)as title FROM table_name
/* mysql, oracle 소문자로 변환 */
SELECT LCASE(column_name) FROM table_name;
/* ms-sql 소문자로 변환 */
SELECT LOWER(column_name)as title FROM table_name
/* mysql, oracle */
SELECT MID(column_name,start,length) AS some_name FROM table_name;
/* ms-sql*/
SELECT SUBSTRING(column_name,0,10) AS some_name FROM table_name
/* 필드값의 길이 구하기 */
SELECT LEN(column_name) FROM table_name
/* 반올림 함수*/
SELECT ROUND(column_name,0) FROM table_name
/* 현재날짜 시간 my-sql, oracle */
SELECT Now() FROM table_name
/* 현재날짜 시간 mssql */
SELECT getdate() FROM table_name
/* my-sql, oracle 데이터형식 바꾸기*/
SELECT column_name1, column_name2, FORMAT(Now(),'YYYY-MM-DD') AS column_name
FROM table_name;