count(*)+按+分组

vlurs2pr 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(306)

CREATE TABLE IF NOT EXISTS `un_tr` (
  `ut_id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `s_p_c` double NOT NULL,
  `d_c` double NOT NULL,
  `tr_id` int(11) DEFAULT NULL,
  `ev_t` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`ut_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `un_tr` (`ut_id`, `date`, 
`s_p_c`, `d_c`, `tr_id`, `ev_type`) VALUES
(1, '2018-06-01', 20.33333, 21.33333, 1, 'accident', NULL),
(2, '2018-07-02', 21.33333, 23.33333, 1, 'accident', NULL),
(3, '2018-06-03', 21.33333, 24.33333, 1, 'accident', NULL),
(4, '2018-06-04', 25.33333, 26.33333, 1, 'travel', NULL),
(5, '2018-06-04', 21.33333, 26.33333, 2, 'travel', NULL),
(6, '2018-06-04', 21.33333, 26.33333, 2, 'accident', NULL),
(7, '2018-06-04', 21.33333, 26.33333, 2, 'travel', NULL),
(8, '2018-06-04', 21.33333, 26.33333, 3, 'travel', NULL),
(9, '2018-08-04', 19.33333, 26.33333, 4, 'travel', NULL);

我只需要得到一个记录计数（ut.tru id）

select  count(distinct ut.tr_id) as count_tr from un_tr ut group  by ut.tr_id having count(ut.ut_id)>1

但我得到的结果是：

count_tr
1
1

我想得到结果：

count_tr
2

你能给我一些建议吗？我该怎么做？谢谢。

sql mysql group-by count having

来源：https://stackoverflow.com/questions/50714473/countgroup-byhaving

2条答案

按热度按时间

xpszyzbs1#

您可以尝试以下查询：

select count(*) as count_tr from (
   select distinct tr_id as dtr_id, count(ut_id)
   from un_tr 
   group by dtr_id
   having count(ut_id) > 1
  ) a;

结果：

+----------+
| count_tr |
+----------+
|        2 |
+----------+

赞(0）回复(0）举报 2021-06-20

n9vozmp42#

你质疑

select  count(distinct ut.tr_id) as count_tr 
from un_tr ut 
group  by ut.tr_id 
having count(ut.ut_id)>1

可以简化为

select  ut.tr_id
from un_tr ut 
group  by ut.tr_id 
having count(ut.ut_id)>1

既然做一个 count(distinct ut.tr_id) ：的 group by ut.tr_id 因此，此计数始终为1% ut.tr_id .
你现在可以数一数 tr_id 发生次数：

select count(*)
from
(
   select  ut.tr_id
   from un_tr ut 
   group  by ut.tr_id 
   having count(ut.ut_id)>1

) as t

赞(0）回复(0）举报 2021-06-20

我来回答

count(*)+按+分组

2条答案

相关问题

热门标签

最新问答