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知之甚少~ 求解
...全文
123 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用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能一次搞定 就最好了
benluobo 2014-10-09
  • 打赏
  • 举报
回复
没看懂 用表格形式贴出你要得到的结果吧
岑迅 2014-10-09
  • 打赏
  • 举报
回复
2   => array(0,1,2),
这个字段可以忽略掉 , 只是想按b字段来分组查询~

56,940

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧