mysql - ORDER BY 不适用于 MySQL 中的 CONCAT 语句

Question

对于我的项目，我有一个奇怪的要求!!

我有如下三个表，例如 ATab、BTab 和 CTab

CREATE TABLE IF NOT EXISTS `ATab` (
`id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`oid` int(8) unsigned NOT NULL,
`type` int(1) unsigned NOT NULL ,
`parent` int(8) unsigned NOT NULL ,
`title` varchar(128) CHARACTER SET utf8 NOT NULL DEFAULT '' ,
`tagname` varchar(16) CHARACTER SET utf8 NOT NULL DEFAULT '',
`uid` int(8) NOT NULL COMMENT 'uid',
`name` varchar(64) CHARACTER SET utf8 DEFAULT NULL ,
`ctime` int(8) NOT NULL ,
`utime` int(11) DEFAULT NULL ,
`lnk` varchar(64) DEFAULT NULL ,
 PRIMARY KEY (`id`),
 KEY `re` (`oid`),
 KEY `re_type` (`type`),
 KEY `rg_parent` (`parent`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `BTab` (
`pid` int(8) unsigned NOT NULL AUTO_INCREMENT,
`ptype` int(1) DEFAULT NULL ,
`pname` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
 PRIMARY KEY (`pid`),
 KEY `ptype` (`ptype`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `CTab` (
`cid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`id` int(11) DEFAULT NULL,
`mvl` text CHARACTER SET utf8,
 PRIMARY KEY (`cid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

示例条目:

INSERT INTO `ATab` (`id`, `oid`, `type`, `parent`, `title`, `tagname`, `uid`, `name`, `ctime`, `utime`, `lnk`) VALUES
(1, 1, 1, 1, 'child1', 'test', 1, 'john', 1383740369, 1383740369, '1'),
(2, 1, 2, 1, 'child2', 'test', 1, 'john', 1383740379, 1383740379, '1'),
(3, 2, 1, 2, 'child3', 'sample', 2, 'jose', 1383740389, 1383740399, '2'),
(4, 2, 2, 2, 'child4', 'sample', 2, 'jose', 1383740479, 1383740479, '2');

INSERT INTO `BTab` (`pid`, `ptype`, `pname`) VALUES
(1, 1, 'parent1'),
(2, 2, 'parent2');


INSERT INTO `CTab` (`cid`, `id`, `mvl`) VALUES
(1, 1, '{\r\n    "test": "test json data "\r\n}'),
(2, 2, '{\r\n    "sample": "sample json data"\r\n}');

BTab 包含父条目。 ATab 包含 BTab 中条目的子条目。 parent 会有多个 child 。 CTab 包含与 ATab 相关的数据 (CTab.id = ATab.id)。 ATab 中的所有条目都不会在 CTab 中有条目(所以我使用了 LEFT OUTER JOIN)。

我的 SELECT 查询:-

SELECT CONCAT(  "{\"items\":[", CONVERT( GROUP_CONCAT(  "{\"t", 
TYPE ,  "\":{\"id\":", ATab.id,  ",\"oid\":", oid,  ",\"ty\":", 
TYPE ,  ",\"p\":", parent,  ", \"rtnm\":\"", tagname,  "\",\"tl\":\"", title,  "\", \"nm\":\"", name,  "\", \"ct\":", ctime,  ", \"ut\":", utime,  ", \"u\":", uid,  " , \"mvl\":", IFNULL( CTab.mvl,  "{}" ) ,  ",\"lnk\":\"", IFNULL( lnk,  "0" ) ,  "\"}}" ) 
USING utf8 ) ,  "]}" ) AS jsn
FROM ATab
JOIN BTab
LEFT OUTER JOIN CTab ON CTab.id = ATab.id
WHERE oid =1
AND ATab.parent = BTab.pid
AND ATab.parent
IN ( 1 ) 
GROUP BY ATab.parent
ORDER BY utime DESC 
LIMIT 0 , 500

输出将是一个 JSON 作为

{
    "items": [
        {
            "t1": {
                "id": 1,
                "oid": 1,
                "ty": 1,
                "p": 1,
                "rtnm": "test",
                "tl": "child1",
                "nm": "john",
                "ct": 1383740369,
                "ut": 1383740369,
                "u": 1,
                "mvl": {
                    "test": "test json data "
                },
                "lnk": "1"
            }
        },
        {
            "t2": {
                "id": 2,
                "oid": 1,
                "ty": 2,
                "p": 1,
                "rtnm": "test",
                "tl": "child2",
                "nm": "john",
                "ct": 1383740379,
                "ut": 1383740379,
                "u": 1,
                "mvl": {
                    "sample": "sample json data"
                },
                "lnk": "1"
            }
        }
    ]
}

我需要根据 utime (ORDER BY utime DESC) 对结果进行排序。但是输出没有排序。我认为问题与我使用的 CONCAT 语句有关(对此我不确定)。我无法更改 JSON 结构。我希望数据库处理排序。查询工作正常。

任何帮助将不胜感激......

编辑:

对不起各位..我尝试创建一个 sqlfiddle url。但是我没有得到 sqlfiddle 站点..可能是网络问题:(

SQLfiddle 网址:http://sqlfiddle.com/#!2/3564f/1

Answer 1

作为sled在评论部分提及 - 将您的 ORDER BY 移到您的 GROUP_CONCAT 中。因此:

SELECT CONCAT(  "{\"items\":[", CONVERT( GROUP_CONCAT(  "{\"t", 
TYPE ,  "\":{\"id\":", ATab.id,  ",\"oid\":", oid,  ",\"ty\":", 
TYPE ,  ",\"p\":", parent,  ", \"rtnm\":\"", tagname,  "\",\"tl\":\"", title,  "\", \"nm\":\"", name,  "\", \"ct\":", ctime,  ", \"ut\":", utime,  ", \"u\":", uid,  " , \"mvl\":", IFNULL( CTab.mvl,  "{}" ) ,  ",\"lnk\":\"", IFNULL( lnk,  "0" ) ,  "\"}}" ORDER BY utime DESC ) 
USING utf8 ) ,  "]}" ) AS jsn
FROM ATab
JOIN BTab
LEFT OUTER JOIN CTab ON CTab.id = ATab.id
WHERE oid =1
AND ATab.parent = BTab.pid
AND ATab.parent
IN ( 1 ) 
GROUP BY ATab.parent
LIMIT 0 , 500;