我能够实现我的结果的唯一方法是创建一个在我看来非常丑陋的东西，处理查询需要很长时间，但无法想出更简单的东西：
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
请注意，ORDER BY在每个子查询中创建了DESC LIMIT 1，以确保只选择了最新的应用程序。。。一切都在cakeHP项目中（如97）：https://github.com/Ridiculous-Innovations/EnterpriseAppStore/blob/master/web/app/Model/Application.php
为了供您参考，这里有一个表格和一些样本数据，名称中有Latest的iDeviant应用程序应该是组顶部的应用程序：
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":["AppIcon29x29@2x.png","AppIcon29x29@2x~ipad.png","AppIcon29x29~ipad.png","AppIcon40x40@2x.png","AppIcon40x40@2x~ipad.png","AppIcon40x40~ipad.png","AppIcon60x60@2x.png","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":["AppIcon29x29@2x.png","AppIcon29x29@2x~ipad.png","AppIcon29x29~ipad.png","AppIcon40x40@2x.png","AppIcon40x40@2x~ipad.png","AppIcon40x40~ipad.png","AppIcon60x60@2x.png","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":["AppIcon29x29@2x.png","AppIcon29x29@2x~ipad.png","AppIcon29x29~ipad.png","AppIcon40x40@2x.png","AppIcon40x40@2x~ipad.png","AppIcon40x40~ipad.png","AppIcon60x60@2x.png","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":["AppIcon29x29@2x.png","AppIcon29x29@2x~ipad.png","AppIcon29x29~ipad.png","AppIcon40x40@2x.png","AppIcon40x40@2x~ipad.png","AppIcon40x40~ipad.png","AppIcon60x60@2x.png","AppIcon76x76@2x~ipad.png","AppIcon76x76~ipad.png"],"provisioning":"enterprise"}', 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`
FROM `applications`
GROUP BY `applications`.`identifier`, `applications`.`platform`;
想法是：
由于我们在每个组中按
created进行排序，因此我们从该列开始，将其转换为时间戳。UNIX_TIMESTAMP(created)在左边加上固定长度的零，比如15。我认为在不久的将来，我们的时间戳不会超过15位，所以我们是安全的
CCD_ 3与CCD_。将
name放置在串联的右侧。这是为了以后从这个级联中获得SUBSTRING通过这种级联升序（
MIN）进行排序，因此有效地，我们将在GROUP BY applications.identifier, applications.platform组内通过createdASC进行排序现在，我们有了最早的
created和name，右边是每组应用程序的有价值的信息。标识符，应用程序。平台SUBSTRING级联信息，得到1-15（created）和16端（name）两个部分，选择它们作为两个不同的列