题目是这样的“一个表t,有个日期列d。需要写SQL,输出日期d。排序要求:d大于当前日期的在小于的之前,距离当前日期越进的在远的之前 。”
按照自己的理解转换为 1到9,9个数。进行排序后输出的为 678954321
建立表语句
CREATE TABLE `t2` (
`d` CHAR(50) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
两种实现方法为:
SELECT a3.z,a3.d
FROM (
(
SELECT t.m-d z, d
FROM t2,(select max(d) m from t2) t
WHERE t2.d > 5)
UNION ALL
(
SELECT d-t.m z, d
FROM t2,(select max(d) m from t2) t
WHERE t2.d < 5)
) a3
ORDER BY a3.z DESC
select d>5 as future, d from t2 order by future desc, abs(d-5)