没有透露我查询的所有细节,我想知道是否有一种方法可以将两个不同表的查询结果连接起来?例如,如果我有一个表
result_table1。
新建表格
如果前两个表格只是普通的表格,我会使用左连接,但实际上这两个表格是由其他使用计数和求和并进行分组的选择语句生成的。当我尝试将这两个选择语句合并时,分组会给我带来意外的结果。
第二个查询
result_table1。
id name address
1 Joe 5 Street
2 Max 6 road
3 Jon 4 place
结果表2
id occupation
1 Student
2 Lawer
3 Carpenter
新建表格
id name address occupation
1 Joe 5 Street Student
2 Max 6 road Lawer
3 Jon 4 place Carpenter
如果前两个表格只是普通的表格,我会使用左连接,但实际上这两个表格是由其他使用计数和求和并进行分组的选择语句生成的。当我尝试将这两个选择语句合并时,分组会给我带来意外的结果。
真实世界的例子
我理解如果我没有提供一个实际的例子,解决这个问题可能会很困难,因此考虑到这一点
第一个查询
SELECT
bbs.id AS bb_id,
count(bb_replies.bbs_id) AS num_replies,
bb_locations.title AS location,
bb_locations.description AS location_description,
bb_categories.title AS category,
bb_categories.description AS category_description,
users.first_name AS first_name,
users.last_name as last_name,
users.id AS user_id,
bbs.title AS post_title,
bbs.content,
bbs.created_date,
bbs.rank
FROM `bbs`
LEFT JOIN bb_locations ON bbs.bb_locations_id = bb_locations.id
LEFT JOIN bb_categories ON bbs.bb_categories_id = bb_categories.id
LEFT JOIN users ON bbs.users_id = users.id
LEFT JOIN bb_replies ON bbs.id = bb_replies.bbs_id
GROUP BY bb_id,location,location_description,category,category_description,first_name,last_name,user_id,post_title,content,created_date,rank
第二个查询
SELECT bbs_id,
sum(CASE WHEN user_id = 2 THEN like_dislike END) AS thisUsersRating,
SUM(CASE WHEN like_dislike = 1 THEN 1 ELSE 0 END) AS likes,
SUM(CASE WHEN like_dislike = 0 THEN 1 ELSE 0 END) AS dislikes
FROM bb_ratings
GROUP BY bbs_id
如何以不会产生奇怪结果的方式合并这两个查询?
数据库
-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 26, 2010 at 05:28 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.1
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `bulletin_board_application`
--
-- --------------------------------------------------------
--
-- Table structure for table `bbs`
--
CREATE TABLE IF NOT EXISTS `bbs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bb_locations_id` int(11) NOT NULL,
`bb_categories_id` int(11) NOT NULL,
`users_id` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`content` text NOT NULL,
`created_date` int(11) NOT NULL,
`rank` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `bbs`
--
INSERT INTO `bbs` (`id`, `bb_locations_id`, `bb_categories_id`, `users_id`, `title`, `content`, `created_date`, `rank`) VALUES
(1, 1, 2, 1, 'Bulletin post 2', 'This is the content of my second bulletin post', 1290792252, 2),
(2, 1, 2, 1, 'Bulletin post 1', 'The content of my first bulletin post', 1290792124, 1),
(3, 1, 2, 2, 'Bulletin Post 3', 'This is the content of bulletin post 3\r\n', 1290792555, 3),
(4, 2, 1, 1, 'bulletin post 4', 'This is my fourth bulletin post', 1290800287, 4);
-- --------------------------------------------------------
--
-- Table structure for table `bb_categories`
--
CREATE TABLE IF NOT EXISTS `bb_categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
`order` varchar(255) NOT NULL,
`admin` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `bb_categories`
--
INSERT INTO `bb_categories` (`id`, `title`, `description`, `order`, `admin`) VALUES
(1, 'Free Stuff', 'Free stuff in the office', '1', 2),
(2, 'Office Anouncements', 'This is an anouncement for the office', '2', 3);
-- --------------------------------------------------------
--
-- Table structure for table `bb_locations`
--
CREATE TABLE IF NOT EXISTS `bb_locations` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`order` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `bb_locations`
--
INSERT INTO `bb_locations` (`id`, `title`, `description`, `address`, `order`) VALUES
(1, 'Washington DC', 'Washington DC chinatown office', 'H street chinatown 20001', 0),
(2, 'San Francisco', 'San Francisco office', 'g street in sf 20395', 1);
-- --------------------------------------------------------
--
-- Table structure for table `bb_ratings`
--
CREATE TABLE IF NOT EXISTS `bb_ratings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bbs_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`like_dislike` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;
--
-- Dumping data for table `bb_ratings`
--
INSERT INTO `bb_ratings` (`id`, `bbs_id`, `user_id`, `like_dislike`) VALUES
(15, 4, 2, 0),
(14, 4, 1, 0),
(13, 3, 1, 0),
(12, 2, 1, 1),
(11, 1, 2, 0),
(10, 1, 1, 1);
-- --------------------------------------------------------
--
-- Table structure for table `bb_replies`
--
CREATE TABLE IF NOT EXISTS `bb_replies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`users_id` int(11) NOT NULL,
`bbs_id` int(11) NOT NULL,
`content` text NOT NULL,
`created_date` int(11) NOT NULL,
`rank` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
--
-- Dumping data for table `bb_replies`
--
INSERT INTO `bb_replies` (`id`, `users_id`, `bbs_id`, `content`, `created_date`, `rank`) VALUES
(1, 1, 1, 'This is a response to the bulletin post with an id of 1', 1290799543, 1),
(2, 1, 1, 'This is a second response to the post with an id of 1', 1290799778, 1),
(3, 1, 2, 'This is a response to the bulletin post with an id of 2\r\n', 1290799827, 1),
(4, 1, 2, 'This is a second response to the bulletin post with an id of 2\r\n', 1290799858, 1),
(5, 1, 3, 'Reply to bulletin with an id of 3', 1290799924, 1),
(6, 1, 3, 'Another reply to the post which has an id of 3\r\n', 1290799962, 1),
(7, 1, 1, 'This is a third reply for the bulletin post with an id of 1\r\n', 1290801268, 1),
(8, 1, 2, '3rd reply for bulletin with id = 2', 1290801445, 2),
(9, 1, 2, '3rd reply for bulletin with id = 2', 1290808030, 3);
-- --------------------------------------------------------
--
-- Table structure for table `bb_reply_ratings`
--
CREATE TABLE IF NOT EXISTS `bb_reply_ratings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bb_replies_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`like_dislike` tinyint(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
--
-- Dumping data for table `bb_reply_ratings`
--
-- --------------------------------------------------------
--
-- Table structure for table `bb_sort_bys`
--
CREATE TABLE IF NOT EXISTS `bb_sort_bys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(20) NOT NULL,
`description` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `bb_sort_bys`
--
INSERT INTO `bb_sort_bys` (`id`, `title`, `description`) VALUES
(1, 'Newest', 'Posts are sorted by their creation date'),
(2, 'Popular', 'Posts are sorted by their rank');
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(10) NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`permission` int(1) NOT NULL,
`bb_sort_bys_id` varchar(10) NOT NULL,
`bb_locations_csv` varchar(255) NOT NULL,
`bb_categories_csv` varchar(255) NOT NULL,
`total_bulletins` int(5) NOT NULL,
`bulletins_per_page` int(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `user_name`, `first_name`, `last_name`, `permission`, `bb_sort_bys_id`, `bb_locations_csv`, `bb_categories_csv`, `total_bulletins`, `bulletins_per_page`) VALUES
(1, 'ahughesg', 'Andrew', 'Hughes-Games', 1, '1', '1,2', '1,2', 20, 5),
(2, 'joeblack', 'Joe', 'Black', 3, '2', '1,2', '1,2', 20, 2),
(3, 'jondoe', 'Jon', 'Doe', 3, '1', '1,2', '1,2', 20, 4);