如何从分组表中选择多个需要排序的值 [英] How can I select multiple values from a grouped table that need to be under order

问题描述

我能够获得结果的唯一方法是创建一个在我看来非常丑陋的东西，需要花费很多时间来处理查询，但无法提出更简单的东西：

  SELECT 
 *，COUNT（Application.id）AS计数，
（来自应用程序的SELECT ID，其中标识符= Application.identifier并且platform = Application.platform ORDER BY创建的DESC LIMIT 1）AS ID，
（从应用程序中选择位置WHERE标识符= Application.identifier AND platform = Application.platform ORDER BY创建的DESC LIMIT 1）AS位置
 （从应用程序中选择名称，其中标识符= Application.identifier和平台= Application.platform，按创建DESC LIMIT ORDER BY）AS名称，
（从应用程序中选择版本，其中标识符= Application.identifier和平台= Application.platform，ORDER BY已创建DESC LIMIT 1）AS版本，
（从应用程序创建的SELECT，其中标识符= Application.identifier和平台= Application.platform ORDER BY创建的DESC LIMIT 1）AS从`enterpriseappstore..applications中创建
 AS作为Application` 
，WHERE 1 = 1 
 GROUP BY`Application`.identifier， $ Application $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ 
 $ b 
请注意，在每个子查询中，ORDER BY创建了DESC LIMIT 1，以确保仅选择了最新的应用程序……所有内容都在CakePHP项目中（如97）：
 < a href = https://github.com/Ridiculous-Innovations/EnterpriseAppStore/blob/master/web/app/Model/Application.php rel = nofollow> https://github.com/Ridiculous-Innovations/ EnterpriseAppStore / blob / master / web / app / Model / Application.php  
 
 
 
作为参考，有一个表和一些示例数据，即iDeviant应用程序名称中带有最新的名称应该是该组顶部的名称：
  CREATE TABLE`applications（
 `id` bigint（20）unsigned NOT NULL AUTO _INCREMENT，
`name` varchar（50）不为空，
`identifier` varchar（150）不为空，
`url` varchar（255）不为空，
`platform `tinyint（2）无符号NOT NULL，
`version` varchar（15）NOT NULL，
`size` bigint（20）无符号NOT NULL默认'0'，
`sort` int （5）无符号非空默认'1000'，
`config`文本非空，
`location` tinyint（2）无符号非空默认'0'，
`创建`日期时间NOT NULL，
`date`的修改时间不为NULL，
 PRIMARY KEY（`id`），
 KEY`name`（`name`，`identifier`，`platform`，`sort`） ，
 KEY`version`（`version`）
）ENGINE = MyISAM DEFAULT CHARSET = utf8; 
 
-
-转储表application的数据
-
 
插入到applications中（id，name） ，`identifier`，`url`，`platform`，`version`，`size`，`sort`，`config`，`location`，`created`，`modified`）值
（1，' iDeviant'，'com.fuerteint.iDeviant'，''，'，'4.0'，1059404，1000，'{ plist：{ CFBundleDisplayName： iDeviant， CFBundleName： iDeviant， DTXcode ： 0501， DTSDKName： iphoneos7.0， UISupportedInterfaceOrientations〜ipad：[ UIInterfaceOrientationPortrait， UIInterfaceOrientationPortraitUpsideDown， UIInterfaceOrientationLandscapeLeft， UIInterfaceOrientationLandscapeRight]， CFBundleIconsiconii { CFBundleIconFiles：[ AppIcon29x29， AppIcon40x40， AppIcon60x60， AppIcon76x76]}}， DTSDKBuild： 11B508， CFBundleDevelopmentRegion： en， CFBundleVersion： 1.0， BuildMachineOSBuild： 13A603， DTPlatformName： iphoneos， CFBundleShortVersionString： 4.0， CFBundlePackageType： APPL， CFBundleSupportedPlatforms：[ iPhoneOS]， CFBundleI nfoDictionaryVersion： 6.0， UIRequiredDeviceCapabilities：[ armv7]， DTCompiler： com.apple.compilers.llvm.clang.1_0， CFBundleExecutable： iDeviant， UILaunchImages：[{ UILaunchImageOrientation：肖像， UILaunchImageName： LaunchImage-700-568h， UILaunchImageSize： {320，568}， UILaunchImageMinimumOSVersion： 7.0}]， CFBundleResourceSpecification： ResourceRules.plist ， MinimumOSVersion： 7.0， UIDeviceFamily：[1]， UIViewControllerBasedStatusBarAppearance：false， DTXcodeBuild： 5A2053， CFBundleIdentifier： com.fuerteint.iDeviant， UIAppFonts：[[FontAwesome .ttf]， CFBundleSignature： ????， DTPlatformVersion： 7.0， CFBundleIcons：{ CFBundlePrimaryIcon：{ CFBundleIconFiles：[ AppIcon29x29， AppIcon40x40， AppIcon60x60 ]}}， LSRequiresIPhoneOS：true， UISupportedInterfaceOrientations：[ UIInterfaceOrientationPortrait， UIInterfaceOrientationLandscapeLeft， UIInterfaceOrientationLandscapeRight]， DTPlatformBuild： 11B508}， icons：[ AppIcon29x29 @ x。  AppIcon29x29@2x~ipad.png， Ap pIcon29x29〜ipad.png， [e-mail]， AppIcon40x40@2x~ipad.png， AppIcon40x40〜ipad.png， [e-mail]， AppIcon76x76@2x~ipad.png ， AppIcon76x76〜ipad.png]，配置：企业，作者：，描述：，完整描述：}'，1，'2013-11-19 14 ：36：01'，'2013-11-19 14:36:01'），
（29，'removeya-debug'，'cz.ursimon.removya'，''，3，'1.0'， 536699，1000，'{版本代码： 1，安装位置： 0， min-sdk-version： 8，屏幕尺寸：{ anydensity： true， smallscreens： true， normalscreens： true， largescreens： true， resizeable： true}， permissions：[]， author：，  description：， fullDescription：}'，0，'2013-11-20 09:40:35'，'2013-11-20 09:40:35'），
（ 30，'iDeviant'，'com.fuerteint.iDeviant'，''，1，'4.0'，1059404，1000，'{ plist：{ CFBundleDisplayName： iDeviant， CFBundleName： iDeviant，  DTXcode： 0501， DTSDKName： iphoneos7.0， UISupportedInterfaceOrientations〜ipad：[ UIInterfaceOrientationPortrait， UIInterfaceOrientationPortraitUpsideDown， UIInt erfaceOrientationLandscapeLeft， UIInterfaceOrientationLandscapeRight]， CFBundleIcons〜ipad：{ CFBundlePrimaryIcon：{ CFBundleIconFiles：[ AppIcon29x29， AppIcon40x40， AppIcon60x60， TSI} 508 ， CFBundleDevelopmentRegion： en， CFBundleVersion： 1.0， BuildMachineOSBuild： 13A603， DTPlatformName： iphoneos， CFBundleShortVersionString： 4.0， CFBundlePackageType： APPL，  CFBundleSupportedPlatforms：[ iPhoneOS]， CFBundleInfoDictionaryVersion： 6.0， UIRequiredDeviceCapabilities：[ armv7]， DTCompiler： com.apple.compilers.llvm.clang.1_0， CFBundleExecutableable：  iDeviant， UILaunchImages：[{ UILaunchImageOrientation：肖像， UILaunchImageName： LaunchImage-700-568h， UILaunchImageSize： {320，568}， UILaunchImageMinimumOSVersion： 7.0} ]， CFBundleResourceSpecification： ResourceRules.plist， MinimumOSVersion： 7.0， UIDeviceFamily：[1]， UIViewControllerBasedStatusBarAppearance：false， DTXcodeBuild： 5A2053， CFBundleIdentifier： com.fuerteint .iDeviant， U IAppFonts：[ FontAwesome.ttf]， CFBundleSignature： ????， DTPlatformVersion： 7.0， CFBundleIcons：{ CFBundlePrimaryIcon：{ CFBundleIconFiles：[ AppIcon29x29， AppIcon40x40， AppIcon60x60]}}， LSRequiresIPhoneOS：true， UISupportedInterfaceOrientations：[ UIInterfaceOrientationPortrait， UIInterfaceOrientationLandscapeLeft， UIInterfaceOrientationLandscapeRight]， DTPlatformsBuild： 11Bcon， 29Icon @ 2x.png， AppIcon29x29@2x~ipad.png， AppIcon29x29〜ipad.png， [e-mail]， AppIcon40x40@2x~ipad.png， AppIcon40x40〜ipad.png，  [e-mail]， AppIcon76x76@2x~ipad.png， AppIcon76x76〜ipad.png]，配置：企业}'，0，'2013-11-20 21:08:09 '，'2013-11-20 21:08:09'），
（31，'iDeviant'，'com.fuerteint.iDeviant'，''，1，'4.0'，1059404，1000，'{  plist：{ CFBundleDisplayName： iDeviant， CFBundleName： iDeviant， DTXcode： 0501， DTSDKName： iphoneos7.0， UISupportedInterfaceOrientations〜ipad：[ UIInterfaceOrientationPortrait，  UIInterfaceOrientationPortraitU psideDown， UIInterfaceOrientationLandscapeLeft， UIInterfaceOrientationLandscapeRight]， CFBundleIcons〜ipad：{ CFBundlePrimaryIcon：{ CFBundleIconFiles：[ AppIcon29x29， AppIcon40x40， AppIcon60x60}， ： 11B508， CFBundleDevelopmentRegion： en， CFBundleVersion： 1.0， BuildMachineOSBuild： 13A603， DTPlatformName： iphoneos， CFBundleShortVersionString： 4.0， CFBundlePackageType：  APPL， CFBundleSupportedPlatforms：[ iPhoneOS]， CFBundleInfoDictionaryVersion： 6.0， UIRequiredDeviceCapabilities：[ armv7]， DTCompiler： com.apple.compilers.llvm.clang.1_0，  CFBundleExecutable： iDeviant， UILaunchImages：[{ UILaunchImageOrientation： Portrait， UILaunchImageName： LaunchImage-700-568h， UILaunchImageSize： {320，568}， UILaunchImageMinimumOSVersion：  7.0}]， CFBundleResourceSpecification： ResourceRules.plist， MinimumOSVersion： 7.0， UIDeviceFamily：[1]， UIViewControllerBasedStatusBarAppearance：false， DTXcodeBuild： 5A2053， CFBundleIdentifier：  com.fuert eint.iDeviant， UIAppFonts：[ FontAwesome.ttf]， CFBundleSignature： ????， DTPlatformVersion： 7.0， CFBundleIcons：{ CFBundlePrimaryIcon：{ CFBundleIconFiles： [ AppIcon29x29， AppIcon40x40， AppIcon60x60]}}， LSRequiresIPhoneOS：true， UISupportedInterfaceOrientations：[ UIInterfaceOrientationPortrait， UIInterfaceOrientationLandscapeLeft， UIInterfaceOrientationLandscapeRight] DTP图标：[ [e-mail]， AppIcon29x29@2x~ipad.png， AppIcon29x29〜ipad.png， [e-mail]， AppIcon40x40@2x~ipad.png， AppIcon40x40 〜ipad.png， [e-mail]， AppIcon76x76@2x~ipad.png， AppIcon76x76〜ipad.png]，配置：企业}'，0，'2013-11- 20 21:08:22'，'2013-11-20 21:08:22'），
（32，'iDeviant Latest'，'com.fuerteint.iDeviant'，''，1，'5.0' ，1059404，1000，'{ plist：{ CFBundleDisplayName： iDeviant， CFBundleName： iDeviant， DTXcode： 0501， DTSDKName： iphoneos7.0， UISupportedInterfaceOrientations〜ipad ：[ UIInterfaceOrientationPortrait， UIInter faceOrientationPortraitUpsideDown， UIInterfaceOrientationLandscapeLeft， UIInterfaceOrientationLandscapeRight]， CFBundleIcons〜ipad：{ CFBundlePrimaryIcon：{ CFBundleIconFiles：[ AppIcon29x29， AppIcon40x60， AppIdx， ： 11B508， CFBundleDevelopmentRegion： en， CFBundleVersion： 1.0， BuildMachineOSBuild： 13A603， DTPlatformName： iphoneos， CFBundleShortVersionString： 4.0， CFBundlePackageType：  APPL， CFBundleSupportedPlatforms：[ iPhoneOS]， CFBundleInfoDictionaryVersion： 6.0， UIRequiredDeviceCapabilities：[ armv7]， DTCompiler： com.apple.compilers.llvm.clang.1_0，  CFBundleExecutable： iDeviant， UILaunchImages：[{ UILaunchImageOrientation： Portrait， UILaunchImageName： LaunchImage-700-568h， UILaunchImageSize： {320，568}， UILaunchImageMinimumOSVersion：  7.0}]， CFBundleResourceSpecification： ResourceRules.plist， MinimumOSVersion： 7.0， UIDeviceFamily：[1]， UIViewControllerBasedStatusBarAppearance：false， DTXcodeBuild： 5A2053， CFBund leIdentifier： com.fuerteint.iDeviant， UIAppFonts：[ FontAwesome.ttf]， CFBundleSignature： ????， DTPlatformVersion： 7.0， CFBundleIcons：{ CFBundlePrimaryIcon ：{ CFBundleIconFiles：[ AppIcon29x29， AppIcon40x40， AppIcon60x60]}}， LSRequiresIPhoneOS：true， UISupportedInterfaceOrientations：[ UIInterfaceOrientationPortrait， UIInterfaceOrientationLandscapeLeft， UIInterfaceRight  11B508}，图标：[ [e-mail]， AppIcon29x29@2x~ipad.png， AppIcon29x29〜ipad.png， [e-mail]， AppIcon40x40 @ 2x〜ipad .png， AppIcon40x40〜ipad.png， [e-mail]， AppIcon76x76@2x~ipad.png， AppIcon76x76〜ipad.png]，配置：企业}'，0 ，'2013-11-20 21:08:35'，'2013-11-20 21:08:35'）; 
  
 
 
解决方案
您是否尝试过：
  SELECT FROM_UNIXTIME（SUBSTR（MIN（CONCAT（LPAD（UNIX_TIMESTAMP（`created`），15，'0'），`name`）），1 ，15））AS`date`，
 SUBSTR（MIN（CONCAT（LPAD（UNIX_TIMESTAMP（`created`），15，'0'），`name`））），16）AS`name` 
来自应用程序 
应用程序，标识符，应用程序，平台分组； 
  
想法是：
 
 
 
由于我们按照每个组中创建的 进行排序，因此从此列开始，将其转换为时间戳。  UNIX_TIMESTAMP（创建） 
 
 
 
 
将其左侧填充零至固定长度，例如15。不要以为我们在最近的将来时间戳不会超过15位，所以我们很安全
 
 
 
 
  CONCAT ，其名称为 。将 name 放在串联的右侧。这是以后通过此串联升序从该串联中 SUBSTRING   
 
 
 
排序（ MIN ），因此我们将有效地对创建的  ASC 进行排序 GROUP BY applications.identifier，applications.platform  
 
 
 
 
组现在我们拥有最早的在每组pplications.identifier，applications.platform 
上都创建了，以及 name 有价值的信息。 li> 
 
 
  SUBSTRING 的连接信息，得到两部分1-15（创建了 ）和16位（名称），将它们选择为两个不同的列
 
 
 
 
Only way I have been able to achieve my result was to create something that is in my eyes very ugly, it takes ages to process the query but was unable to come up with anything simpler that that:
SELECT 
*, COUNT(Application.id) AS count, 
(SELECT id FROM applications WHERE identifier = Application.identifier AND platform = Application.platform ORDER BY created DESC LIMIT 1) AS id, 
(SELECT location FROM applications WHERE identifier = Application.identifier AND platform = Application.platform ORDER BY created DESC LIMIT 1) AS location, 
(SELECT name FROM applications WHERE identifier = Application.identifier AND platform = Application.platform ORDER BY created DESC LIMIT 1) AS name, 
(SELECT version FROM applications WHERE identifier = Application.identifier AND platform = Application.platform ORDER BY created DESC LIMIT 1) AS version, 
(SELECT created FROM applications WHERE identifier = Application.identifier AND platform = Application.platform ORDER BY created DESC LIMIT 1) AS created 
FROM `enterpriseappstore`.`applications` AS `Application` 
WHERE 1 = 1 
GROUP BY `Application`.`identifier`, `Application`.`platform` 
ORDER BY `Application`.`name` ASC, `Application`.`created` DESC
Please note the ORDER BY created DESC LIMIT 1 in every subquery that makes sure only the latest application has been selected ... everything is in cakePHP project here (like 97):
https://github.com/Ridiculous-Innovations/EnterpriseAppStore/blob/master/web/app/Model/Application.php


For your reference, there is a table and some sample data, the iDeviant app with Latest in it's name should be the one on the top of the group:
CREATE TABLE `applications` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `identifier` varchar(150) NOT NULL,
  `url` varchar(255) NOT NULL,
  `platform` tinyint(2) unsigned NOT NULL,
  `version` varchar(15) NOT NULL,
  `size` bigint(20) unsigned NOT NULL DEFAULT '0',
  `sort` int(5) unsigned NOT NULL DEFAULT '1000',
  `config` text NOT NULL,
  `location` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`,`identifier`,`platform`,`sort`),
  KEY `version` (`version`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

--
-- Dumping data for table `applications`
--

INSERT INTO `applications` (`id`, `name`, `identifier`, `url`, `platform`, `version`, `size`, `sort`, `config`, `location`, `created`, `modified`) VALUES
(1, 'iDeviant', 'com.fuerteint.iDeviant', '', 1, '4.0', 1059404, 1000, '{"plist":{"CFBundleDisplayName":"iDeviant","CFBundleName":"iDeviant","DTXcode":"0501","DTSDKName":"iphoneos7.0","UISupportedInterfaceOrientations~ipad":["UIInterfaceOrientationPortrait","UIInterfaceOrientationPortraitUpsideDown","UIInterfaceOrientationLandscapeLeft","UIInterfaceOrientationLandscapeRight"],"CFBundleIcons~ipad":{"CFBundlePrimaryIcon":{"CFBundleIconFiles":["AppIcon29x29","AppIcon40x40","AppIcon60x60","AppIcon76x76"]}},"DTSDKBuild":"11B508","CFBundleDevelopmentRegion":"en","CFBundleVersion":"1.0","BuildMachineOSBuild":"13A603","DTPlatformName":"iphoneos","CFBundleShortVersionString":"4.0","CFBundlePackageType":"APPL","CFBundleSupportedPlatforms":["iPhoneOS"],"CFBundleInfoDictionaryVersion":"6.0","UIRequiredDeviceCapabilities":["armv7"],"DTCompiler":"com.apple.compilers.llvm.clang.1_0","CFBundleExecutable":"iDeviant","UILaunchImages":[{"UILaunchImageOrientation":"Portrait","UILaunchImageName":"LaunchImage-700-568h","UILaunchImageSize":"{320, 568}","UILaunchImageMinimumOSVersion":"7.0"}],"CFBundleResourceSpecification":"ResourceRules.plist","MinimumOSVersion":"7.0","UIDeviceFamily":[1],"UIViewControllerBasedStatusBarAppearance":false,"DTXcodeBuild":"5A2053","CFBundleIdentifier":"com.fuerteint.iDeviant","UIAppFonts":["FontAwesome.ttf"],"CFBundleSignature":"????","DTPlatformVersion":"7.0","CFBundleIcons":{"CFBundlePrimaryIcon":{"CFBundleIconFiles":["AppIcon29x29","AppIcon40x40","AppIcon60x60"]}},"LSRequiresIPhoneOS":true,"UISupportedInterfaceOrientations":["UIInterfaceOrientationPortrait","UIInterfaceOrientationLandscapeLeft","UIInterfaceOrientationLandscapeRight"],"DTPlatformBuild":"11B508"},"icons":["[e-mail]","AppIcon29x29@2x~ipad.png","AppIcon29x29~ipad.png","[e-mail]","AppIcon40x40@2x~ipad.png","AppIcon40x40~ipad.png","[e-mail]","AppIcon76x76@2x~ipad.png","AppIcon76x76~ipad.png"],"provisioning":"enterprise","author":"","description":"","fullDescription":""}', 1, '2013-11-19 14:36:01', '2013-11-19 14:36:01'),
(29, 'removeya-debug', 'cz.ursimon.removya', '', 3, '1.0', 536699, 1000, '{"version-code":"1","install-location":"0","min-sdk-version":"8","screen-sizes":{"anydensity":"true","smallscreens":"true","normalscreens":"true","largescreens":"true","resizeable":"true"},"permissions":[],"author":"","description":"","fullDescription":""}', 0, '2013-11-20 09:40:35', '2013-11-20 09:40:35'),
(30, 'iDeviant', 'com.fuerteint.iDeviant', '', 1, '4.0', 1059404, 1000, '{"plist":{"CFBundleDisplayName":"iDeviant","CFBundleName":"iDeviant","DTXcode":"0501","DTSDKName":"iphoneos7.0","UISupportedInterfaceOrientations~ipad":["UIInterfaceOrientationPortrait","UIInterfaceOrientationPortraitUpsideDown","UIInterfaceOrientationLandscapeLeft","UIInterfaceOrientationLandscapeRight"],"CFBundleIcons~ipad":{"CFBundlePrimaryIcon":{"CFBundleIconFiles":["AppIcon29x29","AppIcon40x40","AppIcon60x60","AppIcon76x76"]}},"DTSDKBuild":"11B508","CFBundleDevelopmentRegion":"en","CFBundleVersion":"1.0","BuildMachineOSBuild":"13A603","DTPlatformName":"iphoneos","CFBundleShortVersionString":"4.0","CFBundlePackageType":"APPL","CFBundleSupportedPlatforms":["iPhoneOS"],"CFBundleInfoDictionaryVersion":"6.0","UIRequiredDeviceCapabilities":["armv7"],"DTCompiler":"com.apple.compilers.llvm.clang.1_0","CFBundleExecutable":"iDeviant","UILaunchImages":[{"UILaunchImageOrientation":"Portrait","UILaunchImageName":"LaunchImage-700-568h","UILaunchImageSize":"{320, 568}","UILaunchImageMinimumOSVersion":"7.0"}],"CFBundleResourceSpecification":"ResourceRules.plist","MinimumOSVersion":"7.0","UIDeviceFamily":[1],"UIViewControllerBasedStatusBarAppearance":false,"DTXcodeBuild":"5A2053","CFBundleIdentifier":"com.fuerteint.iDeviant","UIAppFonts":["FontAwesome.ttf"],"CFBundleSignature":"????","DTPlatformVersion":"7.0","CFBundleIcons":{"CFBundlePrimaryIcon":{"CFBundleIconFiles":["AppIcon29x29","AppIcon40x40","AppIcon60x60"]}},"LSRequiresIPhoneOS":true,"UISupportedInterfaceOrientations":["UIInterfaceOrientationPortrait","UIInterfaceOrientationLandscapeLeft","UIInterfaceOrientationLandscapeRight"],"DTPlatformBuild":"11B508"},"icons":["[e-mail]","AppIcon29x29@2x~ipad.png","AppIcon29x29~ipad.png","[e-mail]","AppIcon40x40@2x~ipad.png","AppIcon40x40~ipad.png","[e-mail]","AppIcon76x76@2x~ipad.png","AppIcon76x76~ipad.png"],"provisioning":"enterprise"}', 0, '2013-11-20 21:08:09', '2013-11-20 21:08:09'),
(31, 'iDeviant', 'com.fuerteint.iDeviant', '', 1, '4.0', 1059404, 1000, '{"plist":{"CFBundleDisplayName":"iDeviant","CFBundleName":"iDeviant","DTXcode":"0501","DTSDKName":"iphoneos7.0","UISupportedInterfaceOrientations~ipad":["UIInterfaceOrientationPortrait","UIInterfaceOrientationPortraitUpsideDown","UIInterfaceOrientationLandscapeLeft","UIInterfaceOrientationLandscapeRight"],"CFBundleIcons~ipad":{"CFBundlePrimaryIcon":{"CFBundleIconFiles":["AppIcon29x29","AppIcon40x40","AppIcon60x60","AppIcon76x76"]}},"DTSDKBuild":"11B508","CFBundleDevelopmentRegion":"en","CFBundleVersion":"1.0","BuildMachineOSBuild":"13A603","DTPlatformName":"iphoneos","CFBundleShortVersionString":"4.0","CFBundlePackageType":"APPL","CFBundleSupportedPlatforms":["iPhoneOS"],"CFBundleInfoDictionaryVersion":"6.0","UIRequiredDeviceCapabilities":["armv7"],"DTCompiler":"com.apple.compilers.llvm.clang.1_0","CFBundleExecutable":"iDeviant","UILaunchImages":[{"UILaunchImageOrientation":"Portrait","UILaunchImageName":"LaunchImage-700-568h","UILaunchImageSize":"{320, 568}","UILaunchImageMinimumOSVersion":"7.0"}],"CFBundleResourceSpecification":"ResourceRules.plist","MinimumOSVersion":"7.0","UIDeviceFamily":[1],"UIViewControllerBasedStatusBarAppearance":false,"DTXcodeBuild":"5A2053","CFBundleIdentifier":"com.fuerteint.iDeviant","UIAppFonts":["FontAwesome.ttf"],"CFBundleSignature":"????","DTPlatformVersion":"7.0","CFBundleIcons":{"CFBundlePrimaryIcon":{"CFBundleIconFiles":["AppIcon29x29","AppIcon40x40","AppIcon60x60"]}},"LSRequiresIPhoneOS":true,"UISupportedInterfaceOrientations":["UIInterfaceOrientationPortrait","UIInterfaceOrientationLandscapeLeft","UIInterfaceOrientationLandscapeRight"],"DTPlatformBuild":"11B508"},"icons":["[e-mail]","AppIcon29x29@2x~ipad.png","AppIcon29x29~ipad.png","[e-mail]","AppIcon40x40@2x~ipad.png","AppIcon40x40~ipad.png","[e-mail]","AppIcon76x76@2x~ipad.png","AppIcon76x76~ipad.png"],"provisioning":"enterprise"}', 0, '2013-11-20 21:08:22', '2013-11-20 21:08:22'),
(32, 'iDeviant Latest', 'com.fuerteint.iDeviant', '', 1, '5.0', 1059404, 1000, '{"plist":{"CFBundleDisplayName":"iDeviant","CFBundleName":"iDeviant","DTXcode":"0501","DTSDKName":"iphoneos7.0","UISupportedInterfaceOrientations~ipad":["UIInterfaceOrientationPortrait","UIInterfaceOrientationPortraitUpsideDown","UIInterfaceOrientationLandscapeLeft","UIInterfaceOrientationLandscapeRight"],"CFBundleIcons~ipad":{"CFBundlePrimaryIcon":{"CFBundleIconFiles":["AppIcon29x29","AppIcon40x40","AppIcon60x60","AppIcon76x76"]}},"DTSDKBuild":"11B508","CFBundleDevelopmentRegion":"en","CFBundleVersion":"1.0","BuildMachineOSBuild":"13A603","DTPlatformName":"iphoneos","CFBundleShortVersionString":"4.0","CFBundlePackageType":"APPL","CFBundleSupportedPlatforms":["iPhoneOS"],"CFBundleInfoDictionaryVersion":"6.0","UIRequiredDeviceCapabilities":["armv7"],"DTCompiler":"com.apple.compilers.llvm.clang.1_0","CFBundleExecutable":"iDeviant","UILaunchImages":[{"UILaunchImageOrientation":"Portrait","UILaunchImageName":"LaunchImage-700-568h","UILaunchImageSize":"{320, 568}","UILaunchImageMinimumOSVersion":"7.0"}],"CFBundleResourceSpecification":"ResourceRules.plist","MinimumOSVersion":"7.0","UIDeviceFamily":[1],"UIViewControllerBasedStatusBarAppearance":false,"DTXcodeBuild":"5A2053","CFBundleIdentifier":"com.fuerteint.iDeviant","UIAppFonts":["FontAwesome.ttf"],"CFBundleSignature":"????","DTPlatformVersion":"7.0","CFBundleIcons":{"CFBundlePrimaryIcon":{"CFBundleIconFiles":["AppIcon29x29","AppIcon40x40","AppIcon60x60"]}},"LSRequiresIPhoneOS":true,"UISupportedInterfaceOrientations":["UIInterfaceOrientationPortrait","UIInterfaceOrientationLandscapeLeft","UIInterfaceOrientationLandscapeRight"],"DTPlatformBuild":"11B508"},"icons":["[e-mail]","AppIcon29x29@2x~ipad.png","AppIcon29x29~ipad.png","[e-mail]","AppIcon40x40@2x~ipad.png","AppIcon40x40~ipad.png","[e-mail]","AppIcon76x76@2x~ipad.png","AppIcon76x76~ipad.png"],"provisioning":"enterprise"}', 0, '2013-11-20 21:08:35', '2013-11-20 21:08:35');

解决方案
have you tried this:
SELECT FROM_UNIXTIME(SUBSTR(MIN(CONCAT(LPAD(UNIX_TIMESTAMP(`created`),15,'0'),`name`)),1,15)) AS `date`,
SUBSTR(MIN(CONCAT(LPAD(UNIX_TIMESTAMP(`created`),15,'0'),`name`)),16) AS `name` 
FROM `applications`
GROUP BY `applications`.`identifier`, `applications`.`platform`;
The idea is:

since we sort by created within every group, we start from this column by converting it to timestamp. UNIX_TIMESTAMP(created)
pad it on the left with zeroes to fixed length, say 15. I don't think we'll have timestamps >15 digits in the nearest future, so we're safe
CONCAT it with the name. Place name on the right side of concatenation. This is for later SUBSTRING'ing of it from this concatenation
sort by this concatenation ascending (MIN), so effectively we will sort by created ASC within group of GROUP BY applications.identifier, applications.platform 
now we have the earliest created along with the name valuable info on the right for each group of pplications.identifier, applications.platform
SUBSTRING the concatenated info, get two parts 1-15 (created) and 16-end (name), select them as two different columns


                        
这篇关于如何从分组表中选择多个需要排序的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！