mysql - 错误 #1242 - 子查询返回超过 1 行

Question

每次我尝试运行此查询时都会出现此错误
1242 - 子查询返回超过 1 行

SELECT(SELECT numbers.phone FROM numbers WHERE numbers.filename = 'Jan-2013') AS Jan2013,
(SELECT numbers.phone FROM numbers WHERE numbers.filename = 'Dec-2012') AS Dec2012

其实我也不知道问题出在哪里。
这是表格设计。

CREATE TABLE `numbers` (
  `id` int(11) NOT NULL auto_increment,
  `phone` varchar(255) NOT NULL,
  `phonecalls` int(3) NOT NULL,
  `duration` float NOT NULL,
  `cost` varchar(10) NOT NULL,
  `city` varchar(255) default NULL,
  `category` varchar(255) default NULL,
  `website` varchar(255) default NULL,
  `reported` int(1) NOT NULL default '0',
  `filename` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12065 DEFAULT CHARSET=latin1;

Answer 1

我相信您正在寻找这样的查询

SELECT filename, phone
  FROM numbers
 WHERE filename IN ('Dec-2012', 'Jan-2013')
 ORDER BY filename, phone

示例输出:

| FILENAME |       PHONE |--------------------------| Dec-2012 | 120-1111532 || Dec-2012 | 123-1111111 |...| Jan-2013 | 111-1116677 || Jan-2013 | 133-1111234 |...

It will give you a properly ordered resultset with data that you need in one go which you can easily iterate over in php.

Another approach is to use GROUP_CONCAT to pack all phones per filename in a delimited string which you can easily explode in php while you iterate over the resultset

SELECT filename, GROUP_CONCAT(phone ORDER BY phone) phones
  FROM numbers
 WHERE filename IN ('Dec-2012', 'Jan-2013')
 GROUP BY filename

示例输出:

| FILENAME |                                                                  PHONES |--------------------------------------------------------------------------------------| Dec-2012 | 120-1111532,123-1111111,133-1111144                                     || Jan-2013 | 111-1116677,133-1111234,156-9851115,171-1274116,172-1111089,199-6571114 |

---

Now you definitely don't want to get phone numbers for each filename in separate columns. You can technically do that with a query like this

SELECT rnum, 
       MIN(CASE WHEN filename = 'Dec-2012' THEN phone END) `Dec-2012`,
       MIN(CASE WHEN filename = 'Jan-2013' THEN phone END) `Jan-2013`
  FROM
(
  SELECT n.*, IF(@g = filename, @n := @n + 1, @n := 1) rnum, @g := filename
    FROM numbers n, (SELECT @n := 1) i
   WHERE filename IN ('Dec-2012', 'Jan-2013')
) q
 GROUP BY rnum

但这没有任何意义，因为除了行号之外，您没有任何可以对数据进行分组的公共(public)列。

你的结果集看起来像这样

| RNUM |    DEC-2012 |    JAN-2013 |------------------------------------|    1 | 123-1111111 | 111-1116677 ||    2 | 120-1111532 | 172-1111089 ||    3 | 133-1111144 | 133-1111234 ||    4 |      (null) | 199-6571114 ||    5 |      (null) | 156-9851115 |...

这里是SQLFiddle 演示