dongtangu6889 2014-03-30 14:43
浏览 655
已采纳

获取字段和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 14: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 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 ubuntu系统下挂载磁盘上执行./提示权限不够
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)