Sql特別查詢
//birth
DATEDIFF(year, birth, GETDATE() - CASE WHEN(MONTH(birth) > MONTH(GETDATE())) OR (MONTH(birth) = MONTH(GETDATE()) AND DAY(birth) > DAY(GETDATE())) THEN 1 ELSE 0 END)https://learn.microsoft.com/zh-tw/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver16
//date compare
CONVERT(nvarchar(6), dateColumn, 112) = (CONVERT(nvarchar(4), {Date.Year}) + RIGHT(REPLICATE('0', 2) + CONVERT(nvarchar(2), {tDate.Month}), 2))//xml 節點取得
CAST(column AS XML).value('(/節點1/節點2)[1]', 'nvarchar(max)')https://learn.microsoft.com/zh-tw/sql/t-sql/xml/value-method-xml-data-type?view=sql-server-ver16
//父與子，合併欄位為單一值
select 父.column + ',' + STRING_AGG(子.column,',') from 父.table
left join 子.table on 父.key = 子.key
where 父.column = 條件
group by 父.columnhttps://learn.microsoft.com/zh-tw/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16