sql,在sql行中查找“路径”(groupby/distinct)?

jljoyd4f  于 2021-06-18  发布在  Mysql
关注(0)|答案(3)|浏览(322)

希望有人能帮我做这个mysql的例子:

hash, version, action
a1..., A, pageview
a2..., A, pageview
a2..., B, pageview
a2..., X, lead
a3..., A, pageview
a3..., B, pageview
a4..., A, pageview
a4..., X, lead
a5..., B, pageview
a6..., A, pageview
a6..., X, lead

如何用一句话回答以下问题?
列出所有具有version=a行的哈希(仅限!)另一行的action=lead
列出所有具有version=b行的哈希(仅限!)另一行的action=lead
列出具有版本为a的操作、版本为b的另一行和操作为lead的行的所有哈希
我用selectdistinct和group尝试了下面的几个语句,但是没有成功

SELECT *, count(hash) as count 
FROM it_track 
GROUP BY hash, action 
having count > 1 
   and action like 'lead';

非常感谢!
简。

njthzxwz

njthzxwz1#

我想你想要这样的东西:

select hash,
       (case when sum(action = 'lead') and
                  min(version) = max(version) and
                  min(version) = 'A'
             then 'A-only, with lead'
             when sum(action = 'lead') and
                  min(version) = max(version) and
                  min(version) = 'B'
             then 'B-only, with lead'
             when sum(action = 'lead') and
                  min(version) <> max(version) 
             then 'Both, with lead'
             else 'Unknown'
      end) as which_group
from it_track
where (action = 'lead' or
       ( action = 'pageview' and version in ('A', 'B') )
      )
group by hash
ycl3bljg

ycl3bljg2#

另一个连接为的版本:

1> select t1.hash from it_track t1, it_track t2 
   where t2.hash = t1.hash 
   and t1.version = 'A' 
   and t2.action ='lead';

2> select t1.hash from it_track t1, it_track t2 
   where t2.hash = t1.hash 
   and t1.version = 'B' 
   and t2.action ='lead';

3> select t1.hash from it_track t1, it_track t2 , it_track t3
   where t2.hash = t1.hash 
   and t3.hash = t1.hash
   and t1.version = 'A' 
   and t2.version = 'B'
   and t3.action ='lead';
swvgeqrz

swvgeqrz3#

1> select distinct t1.hash from it_track t1 where version = 'A' and exists (select 1 from it_track t2 where t1.hash = t2.hash and t2.action = 'lead');
2> select distinct t1.hash from it_track t1 where version = 'B' and exists (select 1 from it_track t2 where t1.hash = t2.hash and t2.action = 'lead');
3> select distinct t1.hash from it_track t1 where version = 'A' and exists (select 1 from it_track t2 where t2.hash = t1.hash and version = 'B') and exists (select 1 from it_track t3 where t3.action = 'lead' and t3.hash = t1.hash);

相关问题