mysql groupby返回不正确的值

xjreopfe  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(418)

我遇到了一个与在查询中使用groupby相关的问题。当我同时按p.id和cc.carclasshash分组时，返回的是重复的行。但是，当我只按p.id分组时，查询将按我所希望的那样工作，只使用一个p.id和cc.carclasshash。但是，问题是随p.id返回的cc.carclasshash值是随机的。底线是，若我按两个值分组，我将得到返回的所有数据以及多个p.id。如果我一组一组，我会得到一个身份证，但还是用错了车。
有没有办法确保cc.carclasshash值对应于正确的最小e.alternateeventdurationinmilliseconds值？
我已经附上了一个样本数据问题的截图。https://imgur.com/a/8n4spfk 前两行仅按p.id（一个p.id，但随机car值）分组。下面四行按p.id和cc.ownedcarid分组（使用正确的汽车，但现在有多个数据p.id）。我想要的结果是每个p.id使用cc.ownedcarid的时间最短，而每个播放器没有任何其他结果。
这是我正在使用的代码，它在按两个值分组时返回重复的数据。

SELECT p.ID, e.personaId, e.ID AS event_id, e.EVENTID, e.carId, 
         MIN(e.alternateEventDurationInMilliseconds) AS alternateEventDurationInMilliseconds,
           p.iconIndex,
           p.name AS p_name, cc.name AS c_name, cc.carClassHash AS c_hash, ev.carClassHash, ccs.full_name AS cs_name, b.user_id AS banned_status, ev.legitTime, e.bustedCount, e.distanceToFinish, e.finishReason, e.fractionCompleted
           FROM EVENT_DATA e
    INNER JOIN PERSONA p ON e.personaId = p.ID
    INNER JOIN CUSTOMCAR cc ON cc.ownedCarId = e.carId 
    INNER JOIN CAR_CLASSES ccs ON ccs.store_name = cc.name
    INNER JOIN USER u ON u.ID = p.USERID
    LEFT JOIN BAN b ON b.user_id = u.ID
    INNER JOIN EVENTSAVED ev ON ev.ID = e.EVENTID

    WHERE (e.EVENTID = '28' AND e.alternateEventDurationInMilliseconds > '0' AND e.bustedCount < '1' AND e.distanceToFinish = '0' AND e.fractionCompleted > '0.9' AND cc.carClassHash = ev.carClassHash AND e.alternateEventDurationInMilliseconds > ev.legitTime AND e.finishReason != '2' OR e.finishReason = '1')

GROUP BY p.ID, cc.ownedCarId

ORDER BY alternateEventDurationInMilliseconds ASC

      CREATE TABLE `EVENT` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `baseCashReward` int(11) NOT NULL,
 `baseRepReward` int(11) NOT NULL,
 `eventModeId` int(11) NOT NULL,
 `finalCashRewardMultiplier` float NOT NULL,
 `finalRepRewardMultiplier` float NOT NULL,
 `isEnabled` bit(1) NOT NULL,
 `isLocked` bit(1) NOT NULL,
 `legitTime` bigint(20) NOT NULL,
 `levelCashRewardMultiplier` float NOT NULL,
 `levelRepRewardMultiplier` float NOT NULL,
 `maxCarClassRating` int(11) NOT NULL,
 `maxLevel` int(11) NOT NULL,
 `maxPlayers` int(11) NOT NULL,
 `minCarClassRating` int(11) NOT NULL,
 `minLevel` int(11) NOT NULL,
 `minTopSpeedTrigger` float NOT NULL,
 `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `perfectStartCashMultiplier` float NOT NULL,
 `perfectStartRepMultiplier` float NOT NULL,
 `rank1CashMultiplier` float NOT NULL,
 `rank1RepMultiplier` float NOT NULL,
 `rank2CashMultiplier` float NOT NULL,
 `rank2RepMultiplier` float NOT NULL,
 `rank3CashMultiplier` float NOT NULL,
 `rank3RepMultiplier` float NOT NULL,
 `rank4CashMultiplier` float NOT NULL,
 `rank4RepMultiplier` float NOT NULL,
 `rank5CashMultiplier` float NOT NULL,
 `rank5RepMultiplier` float NOT NULL,
 `rank6CashMultiplier` float NOT NULL,
 `rank6RepMultiplier` float NOT NULL,
 `rank7CashMultiplier` float NOT NULL,
 `rank7RepMultiplier` float NOT NULL,
 `rank8CashMultiplier` float NOT NULL,
 `rank8RepMultiplier` float NOT NULL,
 `topSpeedCashMultiplier` float NOT NULL,
 `topSpeedRepMultiplier` float NOT NULL,
 `carClassHash` int(11) NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=536 DEFAULT CHARSET=utf8

CREATE TABLE `EVENT_DATA` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `alternateEventDurationInMilliseconds` bigint(20) NOT NULL,
  `bestLapDurationInMilliseconds` bigint(20) NOT NULL,
  `bustedCount` int(11) NOT NULL,
  `carId` bigint(20) NOT NULL,
  `copsDeployed` int(11) NOT NULL,
  `copsDisabled` int(11) NOT NULL,
  `copsRammed` int(11) NOT NULL,
  `costToState` int(11) NOT NULL,
  `distanceToFinish` float NOT NULL,
  `eventDurationInMilliseconds` bigint(20) NOT NULL,
  `eventModeId` int(11) NOT NULL,
  `eventSessionId` bigint(20) DEFAULT NULL,
  `finishReason` int(11) NOT NULL,
  `fractionCompleted` float NOT NULL,
  `hacksDetected` bigint(20) NOT NULL,
  `heat` float NOT NULL,
  `infractions` int(11) NOT NULL,
  `longestJumpDurationInMilliseconds` bigint(20) NOT NULL,
  `numberOfCollisions` int(11) NOT NULL,
  `perfectStart` int(11) NOT NULL,
  `personaId` bigint(20) DEFAULT NULL,
  `rank` int(11) NOT NULL,
  `roadBlocksDodged` int(11) NOT NULL,
  `spikeStripsDodged` int(11) NOT NULL,
  `sumOfJumpsDurationInMilliseconds` bigint(20) NOT NULL,
  `topSpeed` float NOT NULL,
  `EVENTID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK_EVENTDATA_EVENT` (`EVENTID`),
  CONSTRAINT `FK_EVENTDATA_EVENT` FOREIGN KEY (`EVENTID`) REFERENCES `EVENT` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=157134 DEFAULT CHARSET=utf8  

CREATE TABLE `PERSONA` (
 `ID` bigint(20) NOT NULL AUTO_INCREMENT,
 `boost` double NOT NULL,
 `cash` double NOT NULL,
 `created` datetime DEFAULT NULL,
 `curCarIndex` int(11) NOT NULL,
 `iconIndex` int(11) NOT NULL,
 `level` int(11) NOT NULL,
 `motto` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `percentToLevel` float NOT NULL,
 `rating` double NOT NULL,
 `rep` double NOT NULL,
 `repAtCurrentLevel` int(11) NOT NULL,
 `score` int(11) NOT NULL,
 `USERID` bigint(20) DEFAULT NULL,
 `badges` varchar(2048) DEFAULT NULL,
 PRIMARY KEY (`ID`),
 KEY `FK_PERSONA_USER` (`USERID`),
 CONSTRAINT `FK_PERSONA_USER` FOREIGN KEY (`USERID`) REFERENCES `USER` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4043 DEFAULT CHARSET=utf8

CREATE TABLE `CUSTOMCAR` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `baseCar` int(11) NOT NULL,
 `carClassHash` int(11) NOT NULL,
 `isPreset` bit(1) NOT NULL,
 `level` int(11) NOT NULL,
 `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `physicsProfileHash` int(11) NOT NULL,
 `rating` int(11) NOT NULL,
 `resalePrice` float NOT NULL,
 `rideHeightDrop` float NOT NULL,
 `skillModSlotCount` int(11) NOT NULL,
 `version` int(11) NOT NULL,
 `ownedCarId` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `FK_CUSTOMCAR_OWNEDCAR` (`ownedCarId`),
 CONSTRAINT `FK_CUSTOMCAR_OWNEDCAR` FOREIGN KEY (`ownedCarId`) REFERENCES `OWNEDCAR` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16087 DEFAULT CHARSET=utf8

CREATE TABLE `CAR_CLASSES` (
 `store_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `ac_stock` int(11) DEFAULT NULL,
 `ac_var1` int(11) DEFAULT NULL,
 `ac_var2` int(11) DEFAULT NULL,
 `ac_var3` int(11) DEFAULT NULL,
 `full_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `ha_stock` int(11) DEFAULT NULL,
 `ha_var1` int(11) DEFAULT NULL,
 `ha_var2` int(11) DEFAULT NULL,
 `ha_var3` int(11) DEFAULT NULL,
 `hash` int(11) DEFAULT NULL,
 `manufactor` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `model` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `product_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `ts_stock` int(11) DEFAULT NULL,
 `ts_var1` int(11) DEFAULT NULL,
 `ts_var2` int(11) DEFAULT NULL,
 `ts_var3` int(11) DEFAULT NULL,
 PRIMARY KEY (`store_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `USER` (
 `ID` bigint(20) NOT NULL AUTO_INCREMENT,
 `created` datetime DEFAULT NULL,
 `EMAIL` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `HWID` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `IP_ADDRESS` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `isAdmin` bit(1) DEFAULT NULL,
 `lastLogin` datetime DEFAULT NULL,
 `PASSWORD` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `premium` bit(1) DEFAULT NULL,
 `gameHardwareHash` varchar(255) DEFAULT NULL,
 `authservUUID` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3887 DEFAULT CHARSET=utf8

CREATE TABLE `BAN` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `data` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `endsAt` datetime DEFAULT NULL,
 `reason` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `user_id` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `FK_BAN_USER` (`user_id`),
 CONSTRAINT `FK_BAN_USER` FOREIGN KEY (`user_id`) REFERENCES `USER` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

CREATE TABLE `EVENTSAVED` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `baseCashReward` int(11) NOT NULL,
 `baseRepReward` int(11) NOT NULL,
 `eventModeId` int(11) NOT NULL,
 `finalCashRewardMultiplier` float NOT NULL,
 `finalRepRewardMultiplier` float NOT NULL,
 `isEnabled` bit(1) NOT NULL,
 `isLocked` bit(1) NOT NULL,
 `legitTime` bigint(20) NOT NULL,
 `levelCashRewardMultiplier` float NOT NULL,
 `levelRepRewardMultiplier` float NOT NULL,
 `maxCarClassRating` int(11) NOT NULL,
 `maxLevel` int(11) NOT NULL,
 `maxPlayers` int(11) NOT NULL,
 `minCarClassRating` int(11) NOT NULL,
 `minLevel` int(11) NOT NULL,
 `minTopSpeedTrigger` float NOT NULL,
 `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `perfectStartCashMultiplier` float NOT NULL,
 `perfectStartRepMultiplier` float NOT NULL,
 `rank1CashMultiplier` float NOT NULL,
 `rank1RepMultiplier` float NOT NULL,
 `rank2CashMultiplier` float NOT NULL,
 `rank2RepMultiplier` float NOT NULL,
 `rank3CashMultiplier` float NOT NULL,
 `rank3RepMultiplier` float NOT NULL,
 `rank4CashMultiplier` float NOT NULL,
 `rank4RepMultiplier` float NOT NULL,
 `rank5CashMultiplier` float NOT NULL,
 `rank5RepMultiplier` float NOT NULL,
 `rank6CashMultiplier` float NOT NULL,
 `rank6RepMultiplier` float NOT NULL,
 `rank7CashMultiplier` float NOT NULL,
 `rank7RepMultiplier` float NOT NULL,
 `rank8CashMultiplier` float NOT NULL,
 `rank8RepMultiplier` float NOT NULL,
 `topSpeedCashMultiplier` float NOT NULL,
 `topSpeedRepMultiplier` float NOT NULL,
 `carClassHash` int(11) NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=536 DEFAULT CHARSET=utf8

CREATE TABLE `OWNEDCAR` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `durability` int(11) NOT NULL,
 `expirationDate` datetime DEFAULT NULL,
 `heat` float NOT NULL,
 `ownershipType` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
 `carSlotId` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `FK_OWNEDCAR_CARSLOT` (`carSlotId`),
 CONSTRAINT `FK_OWNEDCAR_CARSLOT` FOREIGN KEY (`carSlotId`) REFERENCES `CARSLOT` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16088 DEFAULT CHARSET=utf8

CREATE TABLE `CARSLOT` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `PersonaId` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `FK_CARSLOT_PERSONA` (`PersonaId`),
 CONSTRAINT `FK_CARSLOT_PERSONA` FOREIGN KEY (`PersonaId`) REFERENCES `PERSONA` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=16088 DEFAULT CHARSET=utf8
mysql

1条答案

按热度按时间
qltillow

qltillow1#

好吧，这会给你想要的，但有一点需要注意：

SELECT p.ID, e.personaId, e.ID AS event_id, e.EVENTID, e.carId, e.alternateEventDurationInMilliseconds,
p.iconIndex, p.name AS p_name, cc.name AS c_name, cc.carClassHash AS c_hash, ev.carClassHash, ccs.full_name AS cs_name, b.user_id AS banned_status, 
ev.legitTime, e.bustedCount, e.distanceToFinish, e.finishReason, e.fractionCompleted

FROM EVENT_DATA e

INNER JOIN (SELECT personaId, MIN(alternateEventDurationInMilliseconds) AS alternateEventDurationInMilliseconds
        FROM EVENT_DATA
        GROUP BY personaId) f
    ON f.personaId = e.personaId AND f.alternateEventDurationInMilliseconds = e.alternateEventDurationInMilliseconds

INNER JOIN PERSONA p 
    ON p.ID = e.personaId 
INNER JOIN CUSTOMCAR cc 
    ON cc.ownedCarId = e.carId 
INNER JOIN EVENTSAVED ev 
    ON ev.ID = e.EVENTID
INNER JOIN CAR_CLASSES ccs 
    ON ccs.store_name = cc.name
INNER JOIN USER u 
    ON u.ID = p.USERID   
LEFT JOIN BAN b 
    ON b.user_id = u.ID

WHERE e.finishReason = '1'
OR (e.EVENTID = '28' 
    AND e.alternateEventDurationInMilliseconds > '0' 
    AND e.bustedCount < '1' 
    AND e.distanceToFinish = '0' 
    AND e.fractionCompleted > '0.9' 
    AND cc.carClassHash = ev.carClassHash 
    AND e.alternateEventDurationInMilliseconds > ev.legitTime 
    AND e.finishReason != '2')

ORDER BY e.alternateEventDurationInMilliseconds ASC

警告
一个警告是，如果同一个用户，得到完全相同的时间，用不同的车。事实上，该用户将有2个“最佳时间”与2个不同的汽车。
让我知道这是否适合你。

赞(0）分享  回复(0）举报  2021-06-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com