实际上并没有给出我的查询的所有细节:有没有一种方法可以合并不同表上两个单独查询的结果？ 例如，如果我有一张 table

结果表 1

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 语句时，分组给了我意想不到的结果。

现实世界的例子

我知道如果我不举一个实际的例子，这一定很难解决

第一个查询

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

最佳答案

完成@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一样复杂。

关于mysql - 加入两个查询mysql的结果，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/4288908/