mysql - 根据resultID创建违规发生频率的列表

我正在尝试根据我的“违规”表和“结果”表之间的关系创建违规列表以及它们发生的频率。

两者之间的关系是，resultID列是“结果”表的主键，而result_resultID列是“违规”表中的外键。

我正在尝试通过violationID对它们进行分组，而我需要对某些结果发生多少次进行总和。

我的数据库模式脚本如下：

-MySQL Workbench正向工程

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- Schema jocutraffic

-- Table `offender`

DROP TABLE IF EXISTS `offender` ;

CREATE TABLE IF NOT EXISTS `offender` (
`offenderID` INT NOT NULL,
`firstname` VARCHAR(45) NULL,
`lastname` VARCHAR(45) NULL,
`address` VARCHAR(45) NULL,
`city` VARCHAR(45) NULL,
`state` VARCHAR(2) NULL,
`numberoftickets` INT NULL DEFAULT NULL,
PRIMARY KEY (`offenderID`),
UNIQUE INDEX `offenderID_UNIQUE` (`offenderID` ASC))
ENGINE = InnoDB;

-- Table `vehicle`

DROP TABLE IF EXISTS `vehicle` ;

CREATE TABLE IF NOT EXISTS `vehicle` (
`vehicleLiscense` VARCHAR(35) NOT NULL,
`state` VARCHAR(2) NOT NULL,
`color` VARCHAR(45) NULL,
`year` YEAR NULL DEFAULT NULL,
`make` VARCHAR(45) NULL DEFAULT NULL,
`type` VARCHAR(45) NULL DEFAULT NULL,
`vin` VARCHAR(45) NULL DEFAULT NULL,
`owner` VARCHAR(45) NULL DEFAULT NULL,
`address` VARCHAR(45) NULL,
`offenderID` INT(11) NOT NULL,
PRIMARY KEY (`vehicleLiscense`, `offenderID`),
INDEX `fk_vehicle_offender_idx` (`offenderID` ASC),
CONSTRAINT `fk_vehicle_offender`
FOREIGN KEY (`offenderID`)
REFERENCES `offender` (`offenderID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- Table `result`

DROP TABLE IF EXISTS `result` ;

CREATE TABLE IF NOT EXISTS `result` (
`resultID` INT NOT NULL,
`description` VARCHAR(250) NOT NULL,
PRIMARY KEY (`resultID`))
ENGINE = InnoDB;

-- Table `officer`

DROP TABLE IF EXISTS `officer` ;

CREATE TABLE IF NOT EXISTS `officer` (
`officerID` INT NOT NULL,
`firstname` VARCHAR(45) NULL,
`lastname` VARCHAR(45) NULL,
PRIMARY KEY (`officerID`),
UNIQUE INDEX `lastname_UNIQUE` (`lastname` ASC))
ENGINE = InnoDB;

-- Table `violation`

DROP TABLE IF EXISTS `violation` ;

CREATE TABLE IF NOT EXISTS `violation` (
`violationID` INT NOT NULL,
`vehicle_vehicleLiscense` VARCHAR(35) NOT NULL,
`offenderID` INT(11) NOT NULL,
`violationMonth` INT(2) NOT NULL,
`violationDay` VARCHAR(2) NOT NULL,
`violationYear` YEAR NOT NULL,
`result_resultID` INT NOT NULL,
`officer_officerID` INT NOT NULL,
PRIMARY KEY (`violationID`, `vehicle_vehicleLiscense`, `offenderID`),
INDEX `fk_violation_vehicle1_idx` (`vehicle_vehicleLiscense` ASC, `offenderID` ASC),
INDEX `fk_violation_result1_idx` (`result_resultID` ASC),
INDEX `fk_violation_officer1_idx` (`officer_officerID` ASC),
UNIQUE INDEX `violationID_UNIQUE` (`violationID` ASC),
CONSTRAINT `fk_violation_vehicle1`
FOREIGN KEY (`vehicle_vehicleLiscense` , `offenderID`)
REFERENCES `vehicle` (`vehicleLiscense` , `offenderID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_violation_result1`
FOREIGN KEY (`result_resultID`)
REFERENCES `result` (`resultID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_violation_officer1`
FOREIGN KEY (`officer_officerID`)
REFERENCES `officer` (`officerID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

例如，这是一些我插入表中的数据的屏幕截图，并且有更好的理解。




我知道我可能在这里需要一个join语句，但是，在使用聚合SUM函数进行这项工作时遇到了麻烦。任何帮助是极大的赞赏

最佳答案

这将为您提供每个结果的总数，您不需要vehicule联接即可获得结果，但是如果您的问题有所不同，我将保留。

SQL Fiddle Demo

SELECT re.`description`, count(*)
FROM `violation` vi
JOIN `vehicle` ve
  ON vi.`vehicle_vehicleLiscense` = ve.`vehicleLiscense`
 AND vi.`offenderID` = ve.`offenderID`
JOIN `result` re
  ON vi.`result_resultID` = re.`resultID`
GROUP BY re.`description`

关于mysql - 根据resultID创建违规发生频率的列表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34008634/