连接两个mysql查询的结果

Question

没有实际给出查询的所有详细信息：是否有一种方法可以连接不同表上两个单独查询的结果？ 例如，如果我有一个表

result_table1

id name address
1  Joe  5 Street
2  Max  6 road
3  Jon  4 place

result_table2

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 语句组成的。当我尝试组合两个选择语句时，分组给了我意想不到的结果。

真实世界的示例

我知道，当我不提供实际示例时，这一定很难解决，因此请记住

第一个查询

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);

Answer 1

完成@Microgen的答案...由于您已经有两个第一个选择按您想要的方式工作，您可以将这些结果保留在临时表中：

create temporary table tmp1 as <your first select>;
alter table tmp1 add <some index to accelerate your join later>;
create temporary table tmp2 as <your second select>;
alter table tmp2 add <some index to accelerate your join later>;

然后，您可以应用一个简单的联接来获得最终结果：

select tmp1.id, tmp1.name, tmp1.address, tmp2.occupation
    from tmp1 inner join tmp2 using (id)
    order by tmp1.id;

另一种方法是使用 VIEW，但由于索引不是他们拥有的最好的东西，我会避免它们，特别是当你的前两个 select 一样复杂时。

Answer 2

对前两个表使用临时表，然后使用它们进行连接，但我猜你可能会遇到问题，因为没有通用属性可以连接，除非你希望使用表的 id（如果它们恰好匹配）

Answer 3

这些（临时表、连接两个结果集）都不是必需的。

直接从源表编写查询会更好。不仅是出于性能原因（1 个查询明显优于 2 个连接查询加 1 个），而且是为了您的理解和进步。

Answer 4

从类似的内容开始，

SELECT tablea.id,tablea.name,tablea.address,tableb.occupation

并将类似的内容放入 WHERE 子句中：

WHERE tablea.id = tableb.id

我认为您不需要在这里使用左连接。

Answer 5

我强烈建议您发布您的 SQL，以便获得正确的解决方案。也就是说，您应该使用 MySQL 而不是 PHP 来执行此操作。

但是，您将需要循环第一组记录并构建一个数组。然后循环第二个，将其附加到 id 匹配的第一个集合中。