Question

我读过许多文章、书籍、录像等,

这么说吧...

三表:

CREATE TABLE IF NOT EXISTS `feature` (  
  `feature_id` INT NOT NULL AUTO_INCREMENT ,  
  `title` VARCHAR(45) NOT NULL ,  
  `description` TEXT NOT NULL ,  
  PRIMARY KEY (`feature_id`) )  
ENGINE = InnoDB;  

CREATE TABLE IF NOT EXISTS `requirement` (  
  `requirement_id` INT NOT NULL AUTO_INCREMENT ,  
  `title` VARCHAR(45) NOT NULL ,  
  `description` TEXT NOT NULL ,  
  PRIMARY KEY (`requirement_id`) ,  
  UNIQUE INDEX `title_UNIQUE` (`title` ASC) )  
ENGINE = InnoDB;  

CREATE TABLE IF NOT EXISTS `feature_requirement` (  
  `fk_feature_id` INT NOT NULL ,  
  `fk_requirement_id` INT NOT NULL ,  
  PRIMARY KEY (`fk_feature_id`, `fk_requirement_id`) ,  
  INDEX `fk_feature_requirement_requirement1` (`fk_requirement_id` ASC) ,  
  INDEX `fk_feature_requirement_feature1` (`fk_feature_id` ASC) )  
ENGINE = InnoDB;

有些特征可能有多重要求,有些特征也有相同的要求(即对多个特征也有同样的要求),因此,许多至可能的关系的第三个表格。

我希望能够选择特定特征的所有要求(按特性_id),

在很多关于“未知的柱子”和“非独特的别名”的错误信息之后,

CREATE VIEW `project_feature_requirement` AS  
SELECT r.*  
FROM `requirement` `r`  
INNER JOIN `feature_requirement` `fr`  
    on `r`.`requirement_id` = `fr`.`fk_requirement_id`  
INNER JOIN `feature` `f`  
    on `fr`.`fk_feature_id` = `f`.`feature_id`

However, that only gives me requirement_id, title, & description to SELECT from though. ...which is useless

我还有另外四个想法要创造同样的查询类型。

任何帮助都将不胜感激!

是否有免费程序可以查询我的计划, 并通过“ Wizard ” 或其它方法将我带入“ wizard ”, 以具体说明我要哪个表格/ 栏目, 以及我想选择哪个栏目, 并为我构建查询??? 或许我终于可以找到它, 如果可以用这样的工具来玩的话。

Thanks again! Todd

Answer 1

这将对您有效, 但您应该在数据库中进行命名常规工作。在同一查询中包含多个域名标题可能会引起不必要的混淆 :

CREATE VIEW `project_feature_requirement` AS  
    SELECT r.*, f.feature_id, f.title as ftitle, f.description as fdesc  
    FROM `requirement` `r`  
    INNER JOIN `feature_requirement` `fr`  
        on `r`.`requirement_id` = `fr`.`fk_requirement_id`  
    INNER JOIN `feature` `f`  
        on `fr`.`fk_feature_id` = `f`.`feature_id`

以更清楚、更不易出错的方式撰写查询:

SELECT r.*, f.feature_id, f.title as ftitle, f.description as fdesc  
FROM feature_requirement fr 
JOIN requirement r ON r.requirement_id=fr.fk_requirement_id
JOIN feature f on f.feature_id=fr.fk_feature_id

友情链接