dongtangu6889 2014-03-30 14: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 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条)

报告相同问题?

悬赏问题

  • ¥50 同步两个不同结果的array中某些属性
  • ¥15 悬赏15远程操控解决问题
  • ¥15 CST复制的模型无法单独修改参数?
  • ¥15 前端页面想做个定时任务,但是使用requestAnimationFrame,setinterval和settimeout都不行
  • ¥15 根据以下文字信息,做EA模型图
  • ¥15 删除虚拟显示器驱动 删除所有 Xorg 配置文件 删除显示器缓存文件 重启系统 可是依旧无法退出虚拟显示器
  • ¥15 vscode程序一直报同样的错,如何解决?
  • ¥15 关于使用unity中遇到的问题
  • ¥15 开放世界如何写线性关卡的用例(类似原神)
  • ¥15 关于并联谐振电磁感应加热