如何取回在一个字符串值从主表子表数据

问题描述：

CREATE TABLE IF NOT EXISTS `gf_film` (

    `film_id` bigint(20) NOT NULL AUTO_INCREMENT, 

    `user_id` int(20) NOT NULL, 

    `film_name` varchar(100) DEFAULT NULL, 

    `film_cat` varchar(30) CHARACTER SET latin1 DEFAULT NULL, 

    `film_plot` longtext, 

    `film_release_date` date DEFAULT NULL, 

    `film_post_date` date DEFAULT NULL, 

    `film_type` enum('Movie','Tv') CHARACTER SET latin1 DEFAULT 'Movie', 

    `film_feature` enum('Y','N') CHARACTER SET latin1 NOT NULL DEFAULT 'N', 

    `film_status` enum('ACTIVE','INACTIVE') CHARACTER SET latin1 NOT NULL DEFAULT 'ACTIVE', 

    `film_modify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 

    `film_link_value` varchar(200) NOT NULL, 

    `film_post_link` varchar(255) NOT NULL, 

    PRIMARY KEY (`film_id`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=21435 ;

此子表，通过该我上述gf_film表和最低gf_actor之间进行映射表

CREATE TABLE IF NOT EXISTS `gf_film_actor` (

    `film_id` int(20) NOT NULL, 

    `actor_id` int(20) NOT NULL, 

    KEY `film_id` (`film_id`) 

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

这另一个表从我必须reitrieve演员名字

CREATE TABLE IF NOT EXISTS `gf_actor` (

    `actor_id` bigint(20) NOT NULL AUTO_INCREMENT, 

    `actor_name` varchar(100) DEFAULT NULL, 

    `actor_desc` longtext CHARACTER SET latin1, 

    PRIMARY KEY (`actor_id`), 

    UNIQUE KEY `actor_name` (`actor_name`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=60963 ;

而本作gf_film_poster模式

CREATE TABLE IF NOT EXISTS `gf_film_poster` (

    `film_id` int(20) NOT NULL, 

    `website_poster_url` varchar(255) DEFAULT NULL, 

    `original_poster_url` varchar(255) DEFAULT NULL, 

    `default_poster_url` varchar(255) DEFAULT 'noposter.gif', 

    UNIQUE KEY `film_id` (`film_id`) 

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

执行这个查询

SELECT gf_film.film_id         , 
     film_name          , 
     DATE_FORMAT(film_release_date,'%d') AS DATE  , 
     DATE_FORMAT(film_release_date,'%m') AS month_ori, 
     DATE_FORMAT(film_release_date,'%M') AS MONTH , 
     DATE_FORMAT(film_release_date,'%Y') AS YEAR  , 
     film_release_date        , 
     film_feature         , 
     film_modify          , 
     film_post_link         , 
     website_poster_url 
FROM gf_film 
     LEFT JOIN gf_film_poster 
     ON  gf_film.film_id=gf_film_poster.film_id

我收到从数据库中的这些结果之后

film_id,film_name,date,month_ori,month,year,film_release_date, 
film_feature,film_modify,film_post_link,website_poster_url

所以我需要包含与gf_actor和gf_film_actor

有什么gf_film_poster表的架构joing上述查询演员的名字和actor_id一行与每个电影中的另一列？ – 2010-09-29 20:06:59

我把它加到了我的问题中...... – pravat231 2010-09-29 20:13:11

它是每部电影的演员吗？如果不是，你是否希望每行返回一个actor，其他字段是否重复，或者某个行中的所有actor是否可以？ – 2010-09-29 20:41:28

答

SELECT gf_film.film_id         , 
     film_name          , 
     DATE_FORMAT(film_release_date,'%d') AS DATE  , 
     DATE_FORMAT(film_release_date,'%m') AS month_ori, 
     DATE_FORMAT(film_release_date,'%M') AS MONTH , 
     DATE_FORMAT(film_release_date,'%Y') AS YEAR  , 
     film_release_date        , 
     film_feature         , 
     film_modify          , 
     film_post_link         , 
     website_poster_url        , 
     group_concat(gf_actor.actor_name) AS actors 
FROM  gf_film 
     LEFT JOIN gf_film_poster 
     ON  gf_film.film_id=gf_film_poster.film_id 
     LEFT JOIN gf_film_actor 
     ON  gf_film_actor.film_id = gf_film.film_id 
     LEFT JOIN gf_actor 
     ON  gf_film_actor.actor_id = gf_actor.actor_id 
GROUP BY gf_film.film_id

Thanx这个查询给了我完美的结果，我需要什么。 – pravat231 2010-09-30 04:44:07

你用我的查询添加group_concat，那么你能告诉我我的查询是否被正确优化了。 – pravat231 2010-09-30 04:48:52

如何取回在一个字符串值从主表子表数据

相关推荐