<p>下面的方法使用单个SQL <code>SELECT</code>和<code>CASE</code>语句,这些语句通过后处理子查询来应用规则。可能还有进一步的调整,但它给出了总体思路。很抱歉这很复杂-一开始只是基于帖子中的规则,但是已经被修改以处理一般的情况,结果发现非常复杂!在</p>
<p><strong>已知的限制:</strong>这种方法目前不能很好地处理闰年,并且假设2月的最后一天总是28日,这样做是安全的。这是可以修复的,但闰年的计算并不是完全微不足道的,所以我慎重地将这一点省略掉,以免过于复杂。在</p>
<pre><code>SELECT Col1, Col2, Col3, Col4,
CASE WHEN timeBegin IS NULL AND timeEnd IS NOT NULL
THEN '01-01-01'
WHEN timeBegin LIKE '-%' Handle negative (= BC) dates separately
THEN CASE WHEN SUBSTRING(timeBegin, 2) LIKE '%-[0-1]#'
THEN SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 2) || '9 BC'
WHEN SUBSTRING(timeBegin, 2) LIKE '%-1-##'
THEN SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 4) || '-31 BC'
WHEN SUBSTRING(timeBegin, 2) LIKE '%-2-##'
THEN SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 4) || '-28 BC' No leap year calculation
WHEN SUBSTRING(timeBegin, 2) LIKE '%-3-##'
THEN SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 4) || '-31 BC'
WHEN SUBSTRING(timeBegin, 2) LIKE '%-4-##'
THEN SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 4) || '-30 BC'
WHEN SUBSTRING(timeBegin, 2) LIKE '%-5-##'
THEN SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 4) || '-31 BC'
WHEN SUBSTRING(timeBegin, 2) LIKE '%-6-##'
THEN SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 4) || '-30 BC'
WHEN SUBSTRING(timeBegin, 2) LIKE '%-7-##'
THEN SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 4) || '-31 BC'
WHEN SUBSTRING(timeBegin, 2) LIKE '%-8-##'
THEN SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 4) || '-31 BC'
WHEN SUBSTRING(timeBegin, 2) LIKE '%-9-##'
THEN SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 4) || '-30 BC'
WHEN SUBSTRING(timeBegin, 2) LIKE '%-10-##'
THEN SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 4) || '-31 BC'
WHEN SUBSTRING(timeBegin, 2) LIKE '%-11-##'
THEN SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 4) || '-30 BC'
WHEN SUBSTRING(timeBegin, 2) LIKE '%-0#-##'
THEN REPLACE(SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 7), '#', '9') || '-09-30 BC'
WHEN SUBSTRING(timeBegin, 2) LIKE '%-12-##' OR SUBSTRING(timeBegin, 2) LIKE '%-_#-##'
THEN REPLACE(SUBSTRING(timeBegin, 2, CHAR_LENGTH(timeBegin) - 7), '#', '9') || '-12-31 BC'
ELSE REPLACE(SUBSTRING(timeBegin, 2), '#', '9')
END
ELSE REPLACE(REPLACE(REPLACE(timeBegin, '-0#', '-01'), '-##', '-01'), '#', '0')
END AS timeBegin,
CASE WHEN timeEnd IS NULL AND timeBegin IS NOT NULL
THEN '9999-12-12'
WHEN timeEnd LIKE '-%' Handle negative (= BC) dates separately
THEN REPLACE(REPLACE(REPLACE(SUBSTRING(timeEnd, 2), '-0#', '-01'), '-##', '-01'), '#', '0') || ' BC'
WHEN timeEnd LIKE '%-[0-1]#'
THEN SUBSTRING(timeEnd, 1, CHAR_LENGTH(timeEnd) - 1) || '9'
WHEN timeEnd LIKE '%-1-3#' OR timeEnd LIKE '%-1-##'
THEN SUBSTRING(timeEnd, 1, CHAR_LENGTH(timeEnd) - 2) || '31'
WHEN timeEnd LIKE '%-2-2#' OR timeEnd LIKE '%-2-##'
THEN SUBSTRING(timeEnd, 1, CHAR_LENGTH(timeEnd) - 2)|| '28' No leap year calculation
WHEN timeEnd LIKE '%-3-3#' OR timeEnd LIKE '%-3-##'
THEN SUBSTRING(timeEnd, 1, CHAR_LENGTH(timeEnd) - 2)|| '31'
WHEN timeEnd LIKE '%-4-3#' OR timeEnd LIKE '%-4-##'
THEN SUBSTRING(timeEnd, 1, CHAR_LENGTH(timeEnd) - 2) || '30'
WHEN timeEnd LIKE '%-5-3#' OR timeEnd LIKE '%-5-##'
THEN SUBSTRING(timeEnd, 1, CHAR_LENGTH(timeEnd) - 2) || '31'
WHEN timeEnd LIKE '%-6-3#' OR timeEnd LIKE '%-6-##'
THEN SUBSTRING(timeEnd, 1, CHAR_LENGTH(timeEnd) - 2) || '30'
WHEN timeEnd LIKE '%-7-3#' OR timeEnd LIKE '%-7-##'
THEN SUBSTRING(timeEnd, 1, CHAR_LENGTH(timeEnd) - 2) || '31'
WHEN timeEnd LIKE '%-8-3#' OR timeEnd LIKE '%-8-##'
THEN SUBSTRING(timeEnd, 1, CHAR_LENGTH(timeEnd) - 2) || '31'
WHEN timeEnd LIKE '%-9-3#' OR timeEnd LIKE '%-9-##'
THEN SUBSTRING(timeEnd, 1, CHAR_LENGTH(timeEnd) - 2) || '30'
WHEN timeEnd LIKE '%-10-3#' OR timeEnd LIKE '%-10-##'
THEN SUBSTRING(timeEnd, 1, CHAR_LENGTH(timeEnd) - 2) || '31'
WHEN timeEnd LIKE '%-11-3#' OR timeEnd LIKE '%-11-##'
THEN SUBSTRING(timeEnd, 1, CHAR_LENGTH(timeEnd) - 2) || '30'
WHEN timeEnd LIKE '%-0#-##'
THEN SUBSTRING(timeEnd, 1, CHAR_LENGTH(timeEnd) - 5) || '09-30'
WHEN timeEnd LIKE '%-1#-##' OR timeEnd LIKE '%-##-##'
THEN SUBSTRING(REPLACE(timeEnd, '#', '9'), 1, CHAR_LENGTH(timeEnd) - 5) || '12-31'
ELSE REPLACE(timeEnd, '#', '9')
END AS timeEnd
FROM
(SELECT t1.*,
(SELECT Col4 FROM Tbl t2 WHERE (t1.Col1 = t2.Col2 OR t1.Col2 = t2.Col1)
AND t2.Col3 = 'timeBegin') AS timeBegin,
(SELECT Col4 FROM Tbl t2 WHERE (t1.Col1 = t2.Col2 OR t1.Col2 = t2.Col1)
AND t2.Col3 = 'timeEnd') AS timeEnd
FROM Tbl t1
WHERE t1.Col3 NOT IN ('timeBegin', 'timeEnd')) subquery
ORDER BY CAST(SUBSTRING(Col1, 3) AS INT)
</code></pre>
<p>这是一个<a href="http://sqlfiddle.com/#!12/70e2b/55" rel="nofollow">SQL Fiddle Demo</a>,它显示它产生的结果与发布的示例相似。在</p>