● 以特定符號串接某欄位的查詢結果 ( 這邊以逗號 "," 為串接符號 )
● 將查詢出來的多筆資料結合成一筆
以圖表示即
→
原查詢之 Oracle SQL 語法如下:
select
Column_Name
from
Table_Name
where
column1 = 'xxx';
串接語法如下:
with mv as (
select
Column_Name
from
Table_Name
where
column1 = 'xxx'
)
select
ltrim(extract(xmlagg(xmlelement("Column_Name",','||Column_Name)),'/Column_Name/text()').getstringval(),',') Column_Name
from mv;
2012年9月13日 星期四
2012年5月3日 星期四
以 Java 取出某一年裡某季、某月、某週之第一天及最後一天
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
public class DateUtil {
/**
* 得到某年某週的第一天
*
* @param year
* @param week
* @return
*/
public static Date getFirstDayOfWeek(int year, int week) {
week = week - 1;
Calendar calendar = Calendar.getInstance();
calendar.set(Calendar.YEAR, year);
calendar.set(Calendar.MONTH, Calendar.JANUARY);
calendar.set(Calendar.DATE, 1);
Calendar cal = (Calendar) calendar.clone();
cal.add(Calendar.DATE, week * 7);
return getFirstDayOfWeek(cal.getTime());
}
/**
* 得到某年某週的最後一天
*
* @param year
* @param week
* @return
*/
public static Date getLastDayOfWeek(int year, int week) {
week = week - 1;
Calendar calendar = Calendar.getInstance();
calendar.set(Calendar.YEAR, year);
calendar.set(Calendar.MONTH, Calendar.JANUARY);
calendar.set(Calendar.DATE, 1);
Calendar cal = (Calendar) calendar.clone();
cal.add(Calendar.DATE, week * 7);
return getLastDayOfWeek(cal.getTime());
}
/**
* 取得當前日期所在週的第一天
*
* @param date
* @return
*/
public static Date getFirstDayOfWeek(Date date) {
Calendar calendar = Calendar.getInstance();
calendar.setFirstDayOfWeek(Calendar.SUNDAY);
calendar.setTime(date);
calendar.set(Calendar.DAY_OF_WEEK,
calendar.getFirstDayOfWeek()); // Sunday
return calendar.getTime();
}
/**
* 取得當前日期所在週的最後一天
*
* @param date
* @return
*/
public static Date getLastDayOfWeek(Date date) {
Calendar calendar = Calendar.getInstance();
calendar.setFirstDayOfWeek(Calendar.SUNDAY);
calendar.setTime(date);
calendar.set(Calendar.DAY_OF_WEEK,
calendar.getFirstDayOfWeek() + 6); // Saturday
return calendar.getTime();
}
/**
* 取得當前日期所在週的前一週最後一天
*
* @param date
* @return
*/
public static Date getLastDayOfLastWeek(Date date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
return getLastDayOfWeek(calendar.get(Calendar.YEAR),
calendar.get(Calendar.WEEK_OF_YEAR) - 1);
}
/**
* 返回指定日期的月的第一天
*
* @param year
* @param month
* @return
*/
public static Date getFirstDayOfMonth(Date date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
calendar.set(calendar.get(Calendar.YEAR),
calendar.get(Calendar.MONTH), 1);
return calendar.getTime();
}
/**
* 返回指定年月的月的第一天
*
* @param year
* @param month
* @return
*/
public static Date getFirstDayOfMonth(Integer year, Integer month) {
Calendar calendar = Calendar.getInstance();
if (year == null) {
year = calendar.get(Calendar.YEAR);
}
if (month == null) {
month = calendar.get(Calendar.MONTH);
}
calendar.set(year, month, 1);
return calendar.getTime();
}
/**
* 返回指定日期的月的最後一天
*
* @param year
* @param month
* @return
*/
public static Date getLastDayOfMonth(Date date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
calendar.set(calendar.get(Calendar.YEAR),
calendar.get(Calendar.MONTH), 1);
calendar.roll(Calendar.DATE, -1);
return calendar.getTime();
}
/**
* 返回指定年月的月的最後一天
*
* @param year
* @param month
* @return
*/
public static Date getLastDayOfMonth(Integer year, Integer month) {
Calendar calendar = Calendar.getInstance();
if (year == null) {
year = calendar.get(Calendar.YEAR);
}
if (month == null) {
month = calendar.get(Calendar.MONTH);
}
calendar.set(year, month, 1);
calendar.roll(Calendar.DATE, -1);
return calendar.getTime();
}
/**
* 返回指定日期的上個月的最後一天
*
* @param year
* @param month
* @return
*/
public static Date getLastDayOfLastMonth(Date date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
calendar.set(calendar.get(Calendar.YEAR),
calendar.get(Calendar.MONTH) - 1, 1);
calendar.roll(Calendar.DATE, -1);
return calendar.getTime();
}
/**
* 返回指定日期的季的第一天
*
* @param year
* @param quarter
* @return
*/
public static Date getFirstDayOfQuarter(Date date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
return getFirstDayOfQuarter(calendar.get(Calendar.YEAR),
getQuarterOfYear(date));
}
/**
* 返回指定年季的季的第一天
*
* @param year
* @param quarter
* @return
*/
public static Date getFirstDayOfQuarter(Integer year, Integer quarter) {
Calendar calendar = Calendar.getInstance();
Integer month = new Integer(0);
if (quarter == 1) {
month = 1 - 1;
} else if (quarter == 2) {
month = 4 - 1;
} else if (quarter == 3) {
month = 7 - 1;
} else if (quarter == 4) {
month = 10 - 1;
} else {
month = calendar.get(Calendar.MONTH);
}
return getFirstDayOfMonth(year, month);
}
/**
* 返回指定日期的季的最後一天
*
* @param year
* @param quarter
* @return
*/
public static Date getLastDayOfQuarter(Date date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
return getLastDayOfQuarter(calendar.get(Calendar.YEAR),
getQuarterOfYear(date));
}
/**
* 返回指定年季的季的最後一天
*
* @param year
* @param quarter
* @return
*/
public static Date getLastDayOfQuarter(Integer year, Integer quarter) {
Calendar calendar = Calendar.getInstance();
Integer month = new Integer(0);
if (quarter == 1) {
month = 3 - 1;
} else if (quarter == 2) {
month = 6 - 1;
} else if (quarter == 3) {
month = 9 - 1;
} else if (quarter == 4) {
month = 12 - 1;
} else {
month = calendar.get(Calendar.MONTH);
}
return getLastDayOfMonth(year, month);
}
/**
* 返回指定日期的上一季的最後一天
*
* @param year
* @param quarter
* @return
*/
public static Date getLastDayOfLastQuarter(Date date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
return getLastDayOfLastQuarter(calendar.get(Calendar.YEAR),
getQuarterOfYear(date));
}
/**
* 返回指定年季的上一季的最後一天
*
* @param year
* @param quarter
* @return
*/
public static Date getLastDayOfLastQuarter(Integer year, Integer quarter) {
Calendar calendar = Calendar.getInstance();
Integer month = new Integer(0);
if (quarter == 1) {
month = 12 - 1;
} else if (quarter == 2) {
month = 3 - 1;
} else if (quarter == 3) {
month = 6 - 1;
} else if (quarter == 4) {
month = 9 - 1;
} else {
month = calendar.get(Calendar.MONTH);
}
return getLastDayOfMonth(year, month);
}
/**
* 返回指定日期的季度
*
* @param date
* @return
*/
public static int getQuarterOfYear(Date date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
return calendar.get(Calendar.MONTH) / 3 + 1;
}
}
訂閱:
文章 (Atom)