dongtangu6889 2014-03-30 06:43
浏览 656
已采纳

获取字段和group_concat的计数

table structure is as follows -- Table structure for table category

CREATE TABLE `category` (
  `cat_id` int(10) NOT NULL auto_increment,
  `heading` varchar(255) NOT NULL,
  PRIMARY KEY  (`cat_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `category` (`cat_id`, `heading`) VALUES
(1, 'Fashion'),
(2, 'Kids');

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

-- Table structure for table `shop`

CREATE TABLE `shop` (
  `store_id` int(10) NOT NULL auto_increment,
  `shop_name` varchar(255) NOT NULL,
  `cat_id` int(10) NOT NULL,
  `subcat_id` int(10) NOT NULL,
  PRIMARY KEY  (`store_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `shop` (`store_id`, `shop_name`, `cat_id`, `subcat_id`) VALUES
(1, 'Test Store', 1, 1),
(2, 'Test Store 1', 1, 1),
(3, 'Another Store', 1, 3);

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

-- Table structure for table `subcategory`

CREATE TABLE `subcategory` (
  `subcat_id` int(10) NOT NULL auto_increment,
  `cat_id` int(10) NOT NULL,
  `heading` varchar(255) NOT NULL,
  PRIMARY KEY  (`subcat_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `subcategory` (`subcat_id`, `cat_id`, `heading`) VALUES
(1, 1, 'Women'),
(2, 1, 'General'),
(3, 1, 'Men'),
(4, 2, 'Children');

if i use the below query i get the following output

SELECT
    `category`.`heading` AS `category`
    , `subcategory`.`heading` AS `subcategory`
    , COUNT(`shop`.`subcat_id`) AS cnt
FROM
    `test`.`shop`
    INNER JOIN `test`.`subcategory` 
        ON (`shop`.`subcat_id` = `subcategory`.`subcat_id`)
    INNER JOIN `test`.`category` 
        ON (`shop`.`cat_id` = `category`.`cat_id`)
GROUP BY `shop`.`subcat_id`
HAVING (COUNT(`shop`.`subcat_id`) !='');

<kbd>category</kbd><kbd>subcategory</kbd><kbd>cnt</kbd>
<kbd>Fashion</kbd><kbd>Women</kbd><kbd>2</kbd>
<kbd>Fashion</kbd><kbd>Men</kbd><kbd>1</kbd>

but i want to group concat the subcategory like below

<kbd>category</kbd><kbd>subcategory</kbd>
<kbd>Fashion</kbd><kbd>Women,2|Men,1</kbd>

展开全部

  • 写回答

2条回答 默认 最新

  • doubo4824 2014-03-30 06:54
    关注

    Try this

    SELECT t.category,
    GROUP_CONCAT(CONCAT(t.subcategory,',',t.cnt) SEPARATOR '|') `concat`
    FROM (
    SELECT
        `category`.`heading` AS `category`
        , `subcategory`.`heading` AS `subcategory`
        , COUNT(`shop`.`subcat_id`) AS cnt
    FROM
        `shop`
        INNER JOIN `subcategory` 
            ON (`shop`.`subcat_id` = `subcategory`.`subcat_id`)
        INNER JOIN `category` 
            ON (`shop`.`cat_id` = `category`.`cat_id`)
    GROUP BY `shop`.`subcat_id`
    ) t
    GROUP BY t.category
    

    Note group concat has a default limit of 1024 character but it can be increased by following the manual

    Fiddle Demo

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 QGC打开没有地图显示,离线地图也不显示,如何解决?
  • ¥20 Android Studio 的 webview 与访问网络存在的限制
  • ¥15 某帖子的数据集不清楚来源,求帮助
  • ¥50 tc358743xbg寄存器配置
  • ¥15 idea构建mod报错无效的源发行版项目链接,如何解决?
  • ¥15 springboot中的路径问题
  • ¥80 App Store Connect 中设置了订阅项目,Xcode 中预览可以正确显示价格,真机测试却无法显示
  • ¥15 MATLAB的PIV算法问题
  • ¥15 RflySim例程学习:matlab编译报错
  • ¥20 谁来给我解答一下疑惑
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部