zen cart系统...目的是查出所有的产品评论.
这个是系统带的一个查询.
SELECT r.reviews_id, left(rd.reviews_text, 100) AS reviews_text, r.reviews_rating, r.date_added, p.products_id, pd.products_name, p.products_image, r.customers_name
FROM " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
WHERE p.products_status = '1'
AND p.products_id = r.products_id
AND r.reviews_id = rd.reviews_id
AND p.products_id = pd.products_id
AND pd.language_id = 1
AND rd.languages_id = 1
ORDER BY r.reviews_id DESCTABLE_REVIEWS
TABLE_REVIEWS_DESCRIPTION
这两个表是评论相关信息.用于页面显示:TABLE_REVIEWS.reviews_text评论内容. TABLE_REVIEWS.customers_name用户名称. TABLE_REVIEWS.reviews_rating产品评级. TABLE_REVIEWS.date_added评论时间TABLE_PRODUCTS
TABLE_PRODUCTS_DESCRIPTION
这两个表是产品相关信息.用于页面显示:TABLE_PRODUCTS.products_image产品图片. TABLE_PRODUCTS.products_name产品名称.TABLE_REVIEWS.reviews_id主键,TABLE_REVIEWS_DESCRIPTION.reviews_id外键.
TABLE_PRODUCTS.products_id主键,TABLE_PRODUCTS_DESCRIPTION.products_id外键.
TABLE_REVIEWS,TABLE_REVIEWS_DESCRIPTION存在TABLE_PRODUCTS的外键.另外还有个单独的表,存储的"自动评论"插件的信息,表名:TABLE_AUTO_REVIEWS.
有字段:auto_reviews_id(不存在主外键),products_id,languages_id,customers_name,auto_reviews_rating,auto_reviews_text,auto_reviews_addtime,auto_reviews_showtime就是要查出包含用户评论,和自动评论插件的评论在内的所有评论.纠结啊~~能给出一句SQL的结果吗?不考虑性能
跪求....
数据表下载
http://115.com/file/bexfy5cw#
这个是系统带的一个查询.
SELECT r.reviews_id, left(rd.reviews_text, 100) AS reviews_text, r.reviews_rating, r.date_added, p.products_id, pd.products_name, p.products_image, r.customers_name
FROM " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
WHERE p.products_status = '1'
AND p.products_id = r.products_id
AND r.reviews_id = rd.reviews_id
AND p.products_id = pd.products_id
AND pd.language_id = 1
AND rd.languages_id = 1
ORDER BY r.reviews_id DESCTABLE_REVIEWS
TABLE_REVIEWS_DESCRIPTION
这两个表是评论相关信息.用于页面显示:TABLE_REVIEWS.reviews_text评论内容. TABLE_REVIEWS.customers_name用户名称. TABLE_REVIEWS.reviews_rating产品评级. TABLE_REVIEWS.date_added评论时间TABLE_PRODUCTS
TABLE_PRODUCTS_DESCRIPTION
这两个表是产品相关信息.用于页面显示:TABLE_PRODUCTS.products_image产品图片. TABLE_PRODUCTS.products_name产品名称.TABLE_REVIEWS.reviews_id主键,TABLE_REVIEWS_DESCRIPTION.reviews_id外键.
TABLE_PRODUCTS.products_id主键,TABLE_PRODUCTS_DESCRIPTION.products_id外键.
TABLE_REVIEWS,TABLE_REVIEWS_DESCRIPTION存在TABLE_PRODUCTS的外键.另外还有个单独的表,存储的"自动评论"插件的信息,表名:TABLE_AUTO_REVIEWS.
有字段:auto_reviews_id(不存在主外键),products_id,languages_id,customers_name,auto_reviews_rating,auto_reviews_text,auto_reviews_addtime,auto_reviews_showtime就是要查出包含用户评论,和自动评论插件的评论在内的所有评论.纠结啊~~能给出一句SQL的结果吗?不考虑性能
跪求....
数据表下载
http://115.com/file/bexfy5cw#
建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样，（并给以简单的算法描述）
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。
-- phpMyAdmin SQL Dump
-- version 3.4.10.1
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2012 年 03 月 07 日 10:05
-- 服务器版本: 5.5.20
-- PHP 版本: 5.3.10SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;--
-- 表的结构 `auto_reviews`
--CREATE TABLE IF NOT EXISTS `auto_reviews` (
`auto_reviews_id` int(11) NOT NULL AUTO_INCREMENT,
`products_id` int(11) NOT NULL DEFAULT '0',
`languages_id` int(11) NOT NULL DEFAULT '0',
`customers_name` varchar(64) NOT NULL DEFAULT '',
`auto_reviews_rating` int(1) NOT NULL DEFAULT '5',
`auto_reviews_text` text NOT NULL,
`auto_reviews_addtime` datetime DEFAULT NULL,
`auto_reviews_showtime` datetime DEFAULT NULL,
PRIMARY KEY (`auto_reviews_id`),
KEY `idx_auto_review_id_zen` (`auto_reviews_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=44 ;--
-- 转存表中的数据 `auto_reviews`
--INSERT INTO `auto_reviews` (`auto_reviews_id`, `products_id`, `languages_id`, `customers_name`, `auto_reviews_rating`, `auto_reviews_text`, `auto_reviews_addtime`, `auto_reviews_showtime`) VALUES
(1, 1, 1, 'Da Noblot', 5, 'A truly gorgeous product. totally worth it!', '2011-12-07 23:49:44', '2011-09-12 05:09:39'),
(2, 2, 1, 'dudoit bourseau', 5, 'Nice!I LIKE IT!', '2011-12-07 23:49:45', '2011-09-12 05:09:39'),
(3, 3, 1, 'Fleur blanc', 5, 'I bought these monster beatss last week , i like the color Very beautiful monster beats and can''t wait to use them as i live in California and I wholeheartedly recommend these monster beatss to everyone!', '2011-12-07 23:49:47', '2011-09-12 05:09:39'),
(4, 4, 1, 'adeline Nirlo', 5, 'Loved the new style for this monster beats. Purchased on-line after seeing them in the store. They did not have my size so I was unable to try them on in the store. They are super cute, fun, and fashionable. Ordered my usual size. The fit was perfect and the monster beatss were very clear. ', '2011-12-07 23:49:48', '2011-09-12 05:09:39'),
(5, 5, 1, 'sokkhara saoud', 5, 'The monster beatss are sleek and safisticated. Let''s get this:-)', '2011-12-07 23:49:49', '2011-09-12 05:09:39'),
(6, 6, 1, 'Christèle nait', 5, 'When I got to try them on. So nice and clear.', '2011-12-07 23:49:50', '2011-09-12 05:09:39');
--
-- 表的结构 `products`
--CREATE TABLE IF NOT EXISTS `products` (
`products_id` int(11) NOT NULL AUTO_INCREMENT,
`products_type` int(11) NOT NULL DEFAULT '1',
`products_quantity` float NOT NULL DEFAULT '0',
`products_model` varchar(256) DEFAULT NULL,
`products_image` varchar(256) DEFAULT NULL,
`products_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
`products_virtual` tinyint(1) NOT NULL DEFAULT '0',
`products_date_added` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
`products_last_modified` datetime DEFAULT NULL,
`products_date_available` datetime DEFAULT NULL,
`products_weight` float NOT NULL DEFAULT '0',
`products_status` tinyint(1) NOT NULL DEFAULT '0',
`products_tax_class_id` int(11) NOT NULL DEFAULT '0',
`manufacturers_id` int(11) DEFAULT NULL,
`products_ordered` float NOT NULL DEFAULT '0',
`products_quantity_order_min` float NOT NULL DEFAULT '1',
`products_quantity_order_units` float NOT NULL DEFAULT '1',
`products_priced_by_attribute` tinyint(1) NOT NULL DEFAULT '0',
`product_is_free` tinyint(1) NOT NULL DEFAULT '0',
`product_is_call` tinyint(1) NOT NULL DEFAULT '0',
`products_quantity_mixed` tinyint(1) NOT NULL DEFAULT '0',
`product_is_always_free_shipping` tinyint(1) NOT NULL DEFAULT '0',
`products_qty_box_status` tinyint(1) NOT NULL DEFAULT '1',
`products_quantity_order_max` float NOT NULL DEFAULT '0',
`products_sort_order` int(11) NOT NULL DEFAULT '0',
`products_discount_type` tinyint(1) NOT NULL DEFAULT '0',
`products_discount_type_from` tinyint(1) NOT NULL DEFAULT '0',
`products_price_sorter` decimal(15,4) NOT NULL DEFAULT '0.0000',
`master_categories_id` int(11) NOT NULL DEFAULT '0',
`products_mixed_discount_quantity` tinyint(1) NOT NULL DEFAULT '1',
`metatags_title_status` tinyint(1) NOT NULL DEFAULT '0',
`metatags_products_name_status` tinyint(1) NOT NULL DEFAULT '0',
`metatags_model_status` tinyint(1) NOT NULL DEFAULT '0',
`metatags_price_status` tinyint(1) NOT NULL DEFAULT '0',
`metatags_title_tagline_status` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`products_id`),
KEY `idx_products_date_added_zen` (`products_date_added`),
KEY `idx_products_status_zen` (`products_status`),
KEY `idx_products_date_available_zen` (`products_date_available`),
KEY `idx_products_ordered_zen` (`products_ordered`),
KEY `idx_products_model_zen` (`products_model`),
KEY `idx_products_price_sorter_zen` (`products_price_sorter`),
KEY `idx_master_categories_id_zen` (`master_categories_id`),
KEY `idx_products_sort_order_zen` (`products_sort_order`),
KEY `idx_manufacturers_id_zen` (`manufacturers_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=71 ;--
-- 转存表中的数据 `products`
--INSERT INTO `products` (`products_id`, `products_type`, `products_quantity`, `products_model`, `products_image`, `products_price`, `products_virtual`, `products_date_added`, `products_last_modified`, `products_date_available`, `products_weight`, `products_status`, `products_tax_class_id`, `manufacturers_id`, `products_ordered`, `products_quantity_order_min`, `products_quantity_order_units`, `products_priced_by_attribute`, `product_is_free`, `product_is_call`, `products_quantity_mixed`, `product_is_always_free_shipping`, `products_qty_box_status`, `products_quantity_order_max`, `products_sort_order`, `products_discount_type`, `products_discount_type_from`, `products_price_sorter`, `master_categories_id`, `products_mixed_discount_quantity`, `metatags_title_status`, `metatags_products_name_status`, `metatags_model_status`, `metatags_price_status`, `metatags_title_tagline_status`) VALUES
(1, 1, 1000, 'Monster Beats Diamond Black', 'monster-beats-diamond-black.jpg', '218.0000', 0, '2011-11-22 10:48:42', '2011-12-01 10:48:42', '0000-00-00 00:00:00', 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, '218.0000', 1, 1, 1, 0, 0, 0, 0),
(2, 1, 1000, 'Monster Beats Diamond Blue', 'monster-beats-diamond-blue.jpg', '218.0000', 0, '2011-11-22 10:48:42', '2011-12-01 10:48:42', '0000-00-00 00:00:00', 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, '218.0000', 1, 1, 1, 0, 0, 0, 0),
(3, 1, 1000, 'Monster Beats Diamond Pink', 'monster-beats-diamond-pink.jpg', '218.0000', 0, '2011-11-22 10:48:42', '2011-12-01 10:48:42', '0000-00-00 00:00:00', 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, '218.0000', 1, 1, 1, 0, 0, 0, 0),
(4, 1, 1000, 'Monster Beats Diamond Red', 'monster-beats-diamond-red.jpg', '218.0000', 0, '2011-11-22 10:48:42', '2011-12-01 10:48:43', '0000-00-00 00:00:00', 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, '218.0000', 1, 1, 1, 0, 0, 0, 0),
(5, 1, 1000, 'Monster Beats Diamond White', 'monster-beats-diamond-white.jpg', '218.0000', 0, '2011-11-22 10:48:42', '2011-12-01 10:48:43', '0000-00-00 00:00:00', 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, '218.0000', 1, 1, 1, 0, 0, 0, 0);
--
-- 表的结构 `products_description`
--CREATE TABLE IF NOT EXISTS `products_description` (
`products_id` int(11) NOT NULL AUTO_INCREMENT,
`language_id` int(11) NOT NULL DEFAULT '1',
`products_name` varchar(256) NOT NULL DEFAULT '',
`products_description` text,
`products_url` varchar(255) DEFAULT NULL,
`products_viewed` int(5) DEFAULT '0',
PRIMARY KEY (`products_id`,`language_id`),
KEY `idx_products_name_zen` (`products_name`(255))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=71 ;
-- 转存表中的数据 `products_description`INSERT INTO `products_description` (`products_id`, `language_id`, `products_name`, `products_description`, `products_url`, `products_viewed`) VALUES
(1, 1, 'Monster Beats Diamond Black Headphones', 'Monster Beats Diamond Black Headphones', '', 10),
(2, 1, 'Monster Beats Diamond Blue Headphones', 'Monster Beats Diamond Blue Headphones', '', 5),
(3, 1, 'Monster Beats Diamond Red Headphones', 'Monster Beats Diamond Blue Headphones', '', 5);--
-- 表的结构 `reviews`
--CREATE TABLE IF NOT EXISTS `reviews` (
`reviews_id` int(11) NOT NULL AUTO_INCREMENT,
`products_id` int(11) NOT NULL DEFAULT '0',
`customers_id` int(11) DEFAULT NULL,
`customers_name` varchar(64) NOT NULL DEFAULT '',
`reviews_rating` int(1) DEFAULT NULL,
`date_added` datetime DEFAULT NULL,
`last_modified` datetime DEFAULT NULL,
`reviews_read` int(5) NOT NULL DEFAULT '0',
`status` int(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`reviews_id`),
KEY `idx_products_id_zen` (`products_id`),
KEY `idx_customers_id_zen` (`customers_id`),
KEY `idx_status_zen` (`status`),
KEY `idx_date_added_zen` (`date_added`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;--
-- 转存表中的数据 `reviews`
--INSERT INTO `reviews` (`reviews_id`, `products_id`, `customers_id`, `customers_name`, `reviews_rating`, `date_added`, `last_modified`, `reviews_read`, `status`) VALUES
(1, 10, NULL, 'gts', 5, NULL, NULL, 0, 1),
(2, 13, NULL, 'gts', 4, NULL, NULL, 1, 1);--
-- 表的结构 `reviews_description`
--CREATE TABLE IF NOT EXISTS `reviews_description` (
`reviews_id` int(11) NOT NULL DEFAULT '0',
`languages_id` int(11) NOT NULL DEFAULT '0',
`reviews_text` text NOT NULL,
PRIMARY KEY (`reviews_id`,`languages_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;--
-- 转存表中的数据 `reviews_description`
--INSERT INTO `reviews_description` (`reviews_id`, `languages_id`, `reviews_text`) VALUES
(1, 1, 'nice'),
(2, 1, 'very good!');
-- 转存表中的数据 `reviews`
--INSERT INTO `reviews` (`reviews_id`, `products_id`, `customers_id`, `customers_name`, `reviews_rating`, `date_added`, `last_modified`, `reviews_read`, `status`) VALUES
(1, 1, NULL, 'gts', 5, NULL, NULL, 0, 1),
(2, 2, NULL, 'gts', 4, NULL, NULL, 1, 1);--
-- 表的结构 `products`
--CREATE TABLE IF NOT EXISTS `products` (
`products_id` int(11) NOT NULL AUTO_INCREMENT,
`products_type` int(11) NOT NULL DEFAULT '1',
`products_quantity` float NOT NULL DEFAULT '0',
`products_model` varchar(256) DEFAULT NULL,
`products_image` varchar(256) DEFAULT NULL,
`products_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
`products_virtual` tinyint(1) NOT NULL DEFAULT '0',
`products_date_added` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
`products_last_modified` datetime DEFAULT NULL,
`products_date_available` datetime DEFAULT NULL,
`products_weight` float NOT NULL DEFAULT '0',
`products_status` tinyint(1) NOT NULL DEFAULT '0',
`products_tax_class_id` int(11) NOT NULL DEFAULT '0',
`manufacturers_id` int(11) DEFAULT NULL,
`products_ordered` float NOT NULL DEFAULT '0',
`products_quantity_order_min` float NOT NULL DEFAULT '1',
`products_quantity_order_units` float NOT NULL DEFAULT '1',
`products_priced_by_attribute` tinyint(1) NOT NULL DEFAULT '0',
`product_is_free` tinyint(1) NOT NULL DEFAULT '0',
`product_is_call` tinyint(1) NOT NULL DEFAULT '0',
`products_quantity_mixed` tinyint(1) NOT NULL DEFAULT '0',
`product_is_always_free_shipping` tinyint(1) NOT NULL DEFAULT '0',
`products_qty_box_status` tinyint(1) NOT NULL DEFAULT '1',
`products_quantity_order_max` float NOT NULL DEFAULT '0',
`products_sort_order` int(11) NOT NULL DEFAULT '0',
`products_discount_type` tinyint(1) NOT NULL DEFAULT '0',
`products_discount_type_from` tinyint(1) NOT NULL DEFAULT '0',
`products_price_sorter` decimal(15,4) NOT NULL DEFAULT '0.0000',
`master_categories_id` int(11) NOT NULL DEFAULT '0',
`products_mixed_discount_quantity` tinyint(1) NOT NULL DEFAULT '1',
`metatags_title_status` tinyint(1) NOT NULL DEFAULT '0',
`metatags_products_name_status` tinyint(1) NOT NULL DEFAULT '0',
`metatags_model_status` tinyint(1) NOT NULL DEFAULT '0',
`metatags_price_status` tinyint(1) NOT NULL DEFAULT '0',
`metatags_title_tagline_status` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`products_id`),
KEY `idx_products_date_added_zen` (`products_date_added`),
KEY `idx_products_status_zen` (`products_status`),
KEY `idx_products_date_available_zen` (`products_date_available`),
KEY `idx_products_ordered_zen` (`products_ordered`),
KEY `idx_products_model_zen` (`products_model`),
KEY `idx_products_price_sorter_zen` (`products_price_sorter`),
KEY `idx_master_categories_id_zen` (`master_categories_id`),
KEY `idx_products_sort_order_zen` (`products_sort_order`),
KEY `idx_manufacturers_id_zen` (`manufacturers_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=71 ;--
-- 转存表中的数据 `products`
--INSERT INTO `products` (`products_id`, `products_type`, `products_quantity`, `products_model`, `products_image`, `products_price`, `products_virtual`, `products_date_added`, `products_last_modified`, `products_date_available`, `products_weight`, `products_status`, `products_tax_class_id`, `manufacturers_id`, `products_ordered`, `products_quantity_order_min`, `products_quantity_order_units`, `products_priced_by_attribute`, `product_is_free`, `product_is_call`, `products_quantity_mixed`, `product_is_always_free_shipping`, `products_qty_box_status`, `products_quantity_order_max`, `products_sort_order`, `products_discount_type`, `products_discount_type_from`, `products_price_sorter`, `master_categories_id`, `products_mixed_discount_quantity`, `metatags_title_status`, `metatags_products_name_status`, `metatags_model_status`, `metatags_price_status`, `metatags_title_tagline_status`) VALUES
(1, 1, 1000, 'Monster Beats Diamond Black', 'monster-beats-diamond-black.jpg', '218.0000', 0, '2011-11-22 10:48:42', '2011-12-01 10:48:42', '0000-00-00 00:00:00', 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, '218.0000', 1, 1, 1, 0, 0, 0, 0),
(2, 1, 1000, 'Monster Beats Diamond Blue', 'monster-beats-diamond-blue.jpg', '218.0000', 0, '2011-11-22 10:48:42', '2011-12-01 10:48:42', '0000-00-00 00:00:00', 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, '218.0000', 1, 1, 1, 0, 0, 0, 0),
(3, 1, 1000, 'Monster Beats Diamond Pink', 'monster-beats-diamond-pink.jpg', '218.0000', 0, '2011-11-22 10:48:42', '2011-12-01 10:48:42', '0000-00-00 00:00:00', 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, '218.0000', 1, 1, 1, 0, 0, 0, 0),
(4, 1, 1000, 'Monster Beats Diamond Red', 'monster-beats-diamond-red.jpg', '218.0000', 0, '2011-11-22 10:48:42', '2011-12-01 10:48:43', '0000-00-00 00:00:00', 0, 1, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, '218.0000', 1, 1, 1, 0, 0, 0, 0);
最终希望查出的是，auto_reviews中的评论加上reviews_description中的评论共8条记录。包含字段：
reviews_text reviews_rating date_added customers_name products_name products_image products_id