下面是我的专栏
1-1
1-2
2-1
2-4
是否可以以这种方式排序 - 第一列应该是升序的,第二列应该是降序的
所以结果将是
1-4
1-2
2-1
2-1
我不在乎行是否不匹配。我怎样才能实现这一点(类似于视图)
CREATE TABLE IF NOT EXISTS `pipo_orders` (
`ClientID` int(8) NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` timestamp NOT NULL DEFAULT ,
);
--
-- Dumping data for table `pipo_orders`
--
INSERT INTO `pipo_orders` (`ClientID`, `created_at`, `updated_at`) VALUES
(17, '2014-11-26 16:21:36', '2014-11-26 10:51:36'),
(17, '2014-11-26 16:46:24', '2014-11-26 11:16:24'),
(17, '2014-12-04 16:45:28', '2014-12-04 11:15:28'),
(17, '2014-12-26 12:58:13', '2014-12-26 07:28:13'),
(17, '2014-12-30 14:29:31', '2014-12-30 08:59:31'),
(17, '2014-12-30 14:31:05', '2014-12-30 09:01:05'),
(17, '2015-01-02 12:20:54', '2015-01-02 06:50:54');
如果这是你想要的,那就很奇怪了,我想不出你想要它的理由。事实上,它违背了数据库的基本逻辑......不过:
SELECT t1.ClientID
, t1.created_at
, t2.updated_at
FROM pipo_orders t1
JOIN pipo_orders t2
ON 1 = 1
ORDER BY t1.created_at ASC
, t2.update_at DESC
这看起来像是一件可怕的事情。值created_at和updated_at通过位于同一行中而相关。您可以覆盖此关系并联接完全不相关的值。
但是,要从技术上讲,您必须分别对两列进行排序(为它们提供行号),然后再次连接它们:
select
col1.created_at,
col2.updated_at
from
(
select created_at, @rn1 := @rn1 + 1 as rownum
from pipo_orders po
cross join (select @rn1 := 0) as r
order by created_at
) as col1
from
(
select updated_at, @rn2 := @rn2 + 1 as rownum
from pipo_orders po
cross join (select @rn2 := 0) as r
order by updated_at desc
) as col2 using (rownum)
order by rownum;