2 count(*)+ group by +具有+ join [英] 2 count(*)+group by+having+join
本文介绍了2 count(*)+ group by +具有+ join的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有桌子:
CREATE TABLE IF NOT EXISTS `un_pl` (
`coo` double DEFAULT NULL,
`pl` varchar(255) DEFAULT NULL,
`gal` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `un_pl` (`coo`, `pl`, `gal`) VALUES
(21.33333, 'One', 'Zero'),
(22.33333, 'Mars', 'Sofar');
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_t`)
VALUES
(1, '2018-06-01', 20.33333, 21.33333, 1, 'accident'),
(2, '2018-07-02', 21.33333, 23.33333, 1, 'accident'),
(3, '2018-06-03', 21.33333, 24.33333, 1, 'accident'),
(4, '2018-06-04', 25.33333, 26.33333, 1, 'travel'),
(5, '2018-06-04', 21.33333, 26.33333, 2, 'travel'),
(6, '2018-06-04', 21.33333, 26.33333, 2, 'accident'),
(7, '2018-06-04', 21.33333, 26.33333, 2, 'travel'),
(8, '2018-06-04', 21.33333, 26.33333, 3, 'travel'),
(9, '2018-08-04', 19.33333, 26.33333, 4, 'travel');
我只需要一张记录un_ter和u_t_a
and I need get just one record un_ter and u_t_a
select
count(distinct ut.tr_id) as un_ter,
count(case when ut.ev_t = 'accident' then 1 end) as u_t_a
from un_tr ut
left join un_pl up ON (ut.s_p_c = up.coo) or (ut.d_c = up.coo)
where up.gal = 'Zero' and date between '2018-06-01' and '2018-06-31'
group by ut.tr_id
having count(ut.ut_id)>1
但是我得到了结果: 该查询是错误的,但是它显示了我需要的
but I got the result : this query is wrong, but it shows what I need
record un_ter u_t_a
1 2
1 1
我想得到结果:
record un_ter u_t_a
2 3
您想给我建议,我该怎么办?谢谢.
Would you like give me advice, how do I may do it? Thanks.
推荐答案
该查询是否提供所需的结果?
Wouldn't this query provide the wanted result?
select sum(un_ter), sum(u_t_a) from (
select
count(distinct ut.tr_id) as un_ter,
count(case when ut.ev_t = 'accident' then 1 end) as u_t_a
from un_tr ut
left join un_pl up ON (ut.s_p_c = up.coo) or (ut.d_c = up.coo)
where up.gal = 'Zero' and date between '2018-06-01' and '2018-06-31'
group by ut.tr_id
having count(ut.ut_id)>1
) t1;
这篇关于2 count(*)+ group by +具有+ join的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文