我有以下两个查询，并希望在store_id上加入它们，以获取在特定邮政编码区域内销售戴尔产品的商店的数量，该商店是否营业，该商店的评论总数以及平均评分评论
SELECT s.* , sh . * , d . * , pd . *,
CASE
When (holiday = "Y") THEN "Holiday"
END AS open_status
FROM delivery d, products_description pd, store s, store_hours sh
WHERE d.deliver_to_postcode =5140
AND d.store_id = pd.store_id
AND (pd.products_name like '%dell%' or pd.products_description like '%dell%')
AND pd.store_id = s.store_id
AND s.store_id = sh.store_id
AND sh.weekday = dayname( curdate( ) )
GROUP BY d.store_id'
'SELECT store_id, count( * ) AS reviews, avg( reviews_rating ) AS rating
FROM reviews
WHERE reviews_status =1'
Total stores | store_id | store_name | Postcode | reviews | rating | open_status
----------------------------------------------------------------------------------------------------------------------
2 | 1 | store 1 | 5140 | 3 | 2 star | Holiday
2 | 2 | store 2 | 5140 | 1 | 2 star | Holiday
我如何加入这两个查询以得到上面的结果？
我已经添加了数据库表
Delivery
Store_id | postcode
--------------------------
1 | 5140
1 | 5200
2 | 5140
Product description
Store_id | product_name
------------------------
1 | pc dell
1 | pc ibm
2 | screen dell
Store
Store_id | Name
-------------------
1 | Store 1
2 | Store 2
Store hours
Store_id | Holiday
-------------------
1 | N
2 | N
Reviews
Store_id | Product | Review | review rating
------------------------------------------------
1 | 1 | review 1 | 3 star
1 | 1 | review 2 | 3 star
1 | 2 | review 1 | 3 star
2 | 1 | review 1 | 2 star
我添加了数据库SQL
-
-表
delivery的表结构 CREATE TABLE IF NOT EXISTS `delivery` (
`store_id` int(11) NOT NULL DEFAULT '1',
`deliver_to_postcode` int(11) NOT NULL,
PRIMARY KEY (`store_id`,`deliver_to_postcode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-
-表
products_description的表结构CREATE TABLE IF NOT EXISTS `products_description` (
`store_id` int(11) NOT NULL DEFAULT '1',
`products_id` int(11) NOT NULL AUTO_INCREMENT,
`language_id` int(11) NOT NULL DEFAULT '1',
`products_name` varchar(255) NOT NULL DEFAULT '',
`products_short_description` text,
`products_description` text,
`products_keyword` varchar(64) DEFAULT NULL,
`products_tags` varchar(255) DEFAULT NULL,
`products_url` varchar(255) DEFAULT NULL,
`products_friendly_url` varchar(255) DEFAULT NULL,
`products_page_title` varchar(255) NOT NULL,
`products_meta_keywords` varchar(255) NOT NULL,
`products_meta_description` varchar(255) NOT NULL,
`products_viewed` int(5) DEFAULT '0',
PRIMARY KEY (`store_id`,`products_id`,`language_id`),
KEY `products_name` (`products_name`),
KEY `products_description_keyword` (`products_keyword`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ;
-
-表
store的表结构CREATE TABLE IF NOT EXISTS `store` (
`store_id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL DEFAULT '1',
`store_name` varchar(64) NOT NULL,
PRIMARY KEY (`store_id`),
KEY `customer_id` (`customer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
-
-表
store_hours的表结构CREATE TABLE IF NOT EXISTS `store_hours` (
`store_id` int(11) NOT NULL,
`weekday` varchar(10) NOT NULL,
`holiday` char(1) NOT NULL DEFAULT 'N',
KEY `store_id` (`store_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-
-表
reviews的表结构 CREATE TABLE IF NOT EXISTS `reviews` (
`store_id` int(11) NOT NULL DEFAULT '1',
`reviews_id` int(11) NOT NULL AUTO_INCREMENT,
`products_id` int(11) NOT NULL,
`customers_id` int(11) DEFAULT NULL,
`reviews_rating` int(1) DEFAULT NULL,
`languages_id` int(11) NOT NULL,
`reviews_text` text NOT NULL,
`date_added` datetime DEFAULT NULL,
`last_modified` datetime DEFAULT NULL,
`reviews_read` int(5) NOT NULL DEFAULT '0',
`reviews_status` tinyint(1) NOT NULL,
PRIMARY KEY (`store_id`,`reviews_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
最佳答案
您可以在此处查看解决方案：http://sqlfiddle.com/#!2/691a1/8
此解决方案有一些注意事项：
我相信您上面的查询是“错误的”。第一个查询具有GROUP BY，而不是聚合函数。您的第二个查询具有汇总功能，但没有GROUP BY行。我将GROUP BY子句移到了第二个查询。
使用星号是多余的。 SQLFiddle不列出重复的列。因此，store_id仅列出一次。
我将您的第一个查询从使用隐式（θ样式）联接更改为显式（ANSI样式）联接。
这是查询
SELECT tmp.*, s.* , sh.* , d.* , pd.*,
CASE
When (holiday = "Y") THEN "Holiday"
END AS open_status
FROM delivery d
JOIN products_description pd
ON d.store_id = pd.store_id
JOIN store s
ON pd.store_id = s.store_id
JOIN store_hours sh
ON s.store_id = sh.store_id
JOIN (
SELECT store_id, count(*) AS reviews, avg(reviews_rating) AS rating
FROM reviews
WHERE reviews_status =1
GROUP BY store_id) AS tmp
ON tmp.store_id = s.store_id
WHERE d.deliver_to_postcode =5140
AND (pd.products_name like '%dell%' or pd.products_description like '%dell%')
AND sh.weekday = dayname( curdate( ) )