mysql分组查询

岑迅 2014-10-09 10:31:26

服务器: localhost via TCP/IP
软件: MySQL
软件版本: 5.5.38 - MySQL Community Server (GPL)
协议版本: 10
用户: root@localhost
服务器字符集: UTF-8 Unicode (utf8)

表一表二
a b c b e
1 1 12 1 笔记本
2 1 15 2 手机
3 1 17 3 一体机
4 2 9
5 2 6
6 2 5
7 3 20
8 3 30
9 3 27

如果我要查询

array(

    0   => array(

            'a'   =>  1,

            'b'   =>  1,

            'c'   =>  12,

            ),...

    1   => array(

            'a'   =>  4,

            'b'   =>  2,

            'c'   =>  9,

            ),

    2   => array(0,1,2),

)

这种数据，可以么？ group by的只有第一条~ 对mysql知之甚少~ 求解

...全文

125 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

岑迅 2014-10-12

打赏
举报

中二了~ 好好进修MySQL

岑迅 2014-10-09

打赏
举报

引用 2 楼 benluobobo 的回复:

没看懂用表格形式贴出你要得到的结果吧

--
-- 表的结构 `goods`
CREATE TABLE IF NOT EXISTS `goods` (
  `goods_id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_id` int(11) NOT NULL,
  `sn` varchar(60) NOT NULL,
  `name` varchar(60) NOT NULL,
  `desc` varchar(255) NOT NULL,
  `link` varchar(300) NOT NULL,
  `sort` tinyint(3) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `thumb` varchar(300) NOT NULL,
  `img` varchar(300) NOT NULL,
  PRIMARY KEY (`goods_id`),
  UNIQUE KEY `goods_id` (`goods_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;
--
-- 转存表中的数据 `goods`
--
INSERT INTO `goods` (`goods_id`, `cat_id`, `sn`, `name`, `desc`, `link`, `sort`, `status`, `thumb`, `img`) VALUES
(1, 1, 'P14012', 'x', 'x', '', 1, 1, '', ''),
(2, 2, 'P15013', 'b', 'b', '', 1, 1, '', ''),
(3, 3, 'P17013', 'c', 'c', '', 1, 1, '', ''),
(4, 1, 'P14014', 'd', 'd', '', 2, 1, '', ''),
(5, 1, 'P14015', 'd', 'd', '', 3, 1, '', ''),
(6, 1, 'P14017', 'd', 'd', '', 4, 1, '', ''),
(7, 2, 'P15014', 'd', 'd', '', 2, 1, '', ''),
(8, 3, 'P15014', 'z', 'z', '', 3, 1, '', ''),
(9, 2, 'P15014', 'z', 'z', '', 3, 1, '', ''),
(10, 3, 'P17016', 'z', 'z', '', 2, 1, '', '');

-- --------------------------------------------------------

--
-- 表的结构 `goods_cat`
CREATE TABLE IF NOT EXISTS `goods_cat` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL,
  `type` varchar(20) NOT NULL,
  `name` varchar(50) NOT NULL,
  `desc` varchar(255) NOT NULL,
  `sort` tinyint(3) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

-- 转存表中的数据 `goods_cat`

INSERT INTO `goods_cat` (`cat_id`, `pid`, `type`, `name`, `desc`, `sort`, `status`) VALUES
(1, 0, 'jiezhi', '戒指', '戒指系列', 1, 1),
(2, 0, 'shouzhuo', '手镯', '手镯系列', 2, 1),
(3, 0, 'erhuan', '耳环', '耳环系列', 3, 1),
(4, 0, 'shoulian', '手链', '手链', 4, 1);

结果

array(4) {
  [0] => array(2) {
    ["cat"] => array(7) {
      ["cat_id"] => string(1) "1"
      ["pid"] => string(1) "0"
      ["type"] => string(6) "jiezhi"
      ["name"] => string(6) ""
      ["desc"] => string(12) ""
      ["sort"] => string(1) "1"
      ["status"] => string(1) "1"
    }
    ["goods"] => array(4) {
      [0] => array(10) {
        ["goods_id"] => string(1) "1"
        ["cat_id"] => string(1) "1"
        ["sn"] => string(6) "P14012"
        ["name"] => string(6) ""
        ["desc"] => string(6) ""
        ["link"] => string(0) ""
        ["sort"] => string(1) "1"
        ["status"] => string(1) "1"
        ["thumb"] => string(0) ""
        ["img"] => string(0) ""
      }
      [1] => array(10) {
        ["goods_id"] => string(1) "4"
        ["cat_id"] => string(1) "1"
        ["sn"] => string(6) "P14014"
        ["name"] => string(6) ""
        ["desc"] => string(6) ""
        ["link"] => string(0) ""
        ["sort"] => string(1) "2"
        ["status"] => string(1) "1"
        ["thumb"] => string(0) ""
        ["img"] => string(0) ""
      }
      [2] => array(10) {
        ["goods_id"] => string(1) "5"
        ["cat_id"] => string(1) "1"
        ["sn"] => string(6) "P14015"
        ["name"] => string(6) ""
        ["desc"] => string(6) ""
        ["link"] => string(0) ""
        ["sort"] => string(1) "3"
        ["status"] => string(1) "1"
        ["thumb"] => string(0) ""
        ["img"] => string(0) ""
      }
      [3] => array(10) {
        ["goods_id"] => string(1) "6"
        ["cat_id"] => string(1) "1"
        ["sn"] => string(6) "P14017"
        ["name"] => string(6) ""
        ["desc"] => string(6) ""
        ["link"] => string(0) ""
        ["sort"] => string(1) "4"
        ["status"] => string(1) "1"
        ["thumb"] => string(0) ""
        ["img"] => string(0) ""
      }
    }
  }
  [1] => array(2) {
    ["cat"] => array(7) {
      ["cat_id"] => string(1) "2"
      ["pid"] => string(1) "0"
      ["type"] => string(8) "shouzhuo"
      ["name"] => string(6) ""
      ["desc"] => string(12) ""
      ["sort"] => string(1) "2"
      ["status"] => string(1) "1"
    }
    ["goods"] => array(3) {
      [0] => array(10) {
        ["goods_id"] => string(1) "2"
        ["cat_id"] => string(1) "2"
        ["sn"] => string(6) "P15013"
        ["name"] => string(7) ""
        ["desc"] => string(7) ""
        ["link"] => string(0) ""
        ["sort"] => string(1) "1"
        ["status"] => string(1) "1"
        ["thumb"] => string(0) ""
        ["img"] => string(0) ""
      }
      [1] => array(10) {
        ["goods_id"] => string(1) "7"
        ["cat_id"] => string(1) "2"
        ["sn"] => string(6) "P15014"
        ["name"] => string(6) ""
        ["desc"] => string(6) ""
        ["link"] => string(0) ""
        ["sort"] => string(1) "2"
        ["status"] => string(1) "1"
        ["thumb"] => string(0) ""
        ["img"] => string(0) ""
      }
      [2] => array(10) {
        ["goods_id"] => string(1) "9"
        ["cat_id"] => string(1) "2"
        ["sn"] => string(6) "P15014"
        ["name"] => string(6) ""
        ["desc"] => string(6) ""
        ["link"] => string(0) ""
        ["sort"] => string(1) "3"
        ["status"] => string(1) "1"
        ["thumb"] => string(0) ""
        ["img"] => string(0) ""
      }
    }
  }
  [2] => array(2) {
    ["cat"] => array(7) {
      ["cat_id"] => string(1) "3"
      ["pid"] => string(1) "0"
      ["type"] => string(6) "erhuan"
      ["name"] => string(6) ""
      ["desc"] => string(12) ""
      ["sort"] => string(1) "3"
      ["status"] => string(1) "1"
    }
    ["goods"] => array(3) {
      [0] => array(10) {
        ["goods_id"] => string(1) "3"
        ["cat_id"] => string(1) "3"
        ["sn"] => string(6) "P17013"
        ["name"] => string(12) ""
        ["desc"] => string(12) ""
        ["link"] => string(0) ""
        ["sort"] => string(1) "1"
        ["status"] => string(1) "1"
        ["thumb"] => string(0) ""
        ["img"] => string(0) ""
      }
      [1] => array(10) {
        ["goods_id"] => string(1) "8"
        ["cat_id"] => string(1) "3"
        ["sn"] => string(6) "P15014"
        ["name"] => string(7) ""
        ["desc"] => string(7) ""
        ["link"] => string(0) ""
        ["sort"] => string(1) "3"
        ["status"] => string(1) "1"
        ["thumb"] => string(0) ""
        ["img"] => string(0) ""
      }
      [2] => array(10) {
        ["goods_id"] => string(2) "10"
        ["cat_id"] => string(1) "3"
        ["sn"] => string(6) "P17016"
        ["name"] => string(6) ""
        ["desc"] => string(6) ""
        ["link"] => string(0) ""
        ["sort"] => string(1) "2"
        ["status"] => string(1) "1"
        ["thumb"] => string(0) ""
        ["img"] => string(0) ""
      }
    }
  }
  [3] => array(2) {
    ["cat"] => array(7) {
      ["cat_id"] => string(1) "4"
      ["pid"] => string(1) "0"
      ["type"] => string(8) "shoulian"
      ["name"] => string(6) ""
      ["desc"] => string(6) ""
      ["sort"] => string(1) "4"
      ["status"] => string(1) "1"
    }
    ["goods"] => NULL
  }
}

php循环出来了(一共查询5次)~ 但我不知道mysql能不能查出来 ~ 如果 mysql能一次搞定就最好了