MySQL:没有像组中那样的字符串?

发布于 2024-09-30 18:02:42 字数 416 浏览 8 评论 0原文

我的表中有两列:VisitorID 和 URL。

对于访问者进行的每个页面视图,表中都存在一个条目。我想查看每个访问者的总访问次数,其中访问者组中没有一个 URL 类似于“%page=checkout%”。

我想它是这样的:

SELECT *, COUNT(*) AS TotalVisits
FROM `VisitorLog`
GROUP BY VisitorID
HAVING `URL` NOT IN (SELECT * FROM ?? WHERE `URL` LIKE "%page=checkout%")

但我不完全理解 HAVING 子句如何与子查询一起使用,如果我需要子查询,等等?又如何让自己变得消极呢?

任何能解释答案的人都会得到奖励积分,这样我下次就可以自己做!

谢谢,

尼克

I have two columns in a table: VisitorID, and URL.

An entry exists in the table for every page view a visitor makes. I'd like to see the total number of visits for each Visitor, where NONE of the URLs in a visitor's group are LIKE "%page=checkout%".

I'm thinking it's something like this:

SELECT *, COUNT(*) AS TotalVisits
FROM `VisitorLog`
GROUP BY VisitorID
HAVING `URL` NOT IN (SELECT * FROM ?? WHERE `URL` LIKE "%page=checkout%")

But I don't entirely understand how the HAVING clause works with a sub-query, if I need a sub-query at all, etc? And how to make having negative?

And bonus points for anyone who can explain the answer so that I can do it myself next time!

Thanks,

Nick

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

箜明 2024-10-07 18:02:42
SELECT *, COUNT(*) AS TotalVisits
FROM `VisitorLog`
WHERE 
    VisitorID NOT IN 
        (SELECT VisitorID FROM `VisitorLog` WHERE `URL` LIKE '%page=checkout%')
GROUP BY VisitorID
SELECT *, COUNT(*) AS TotalVisits
FROM `VisitorLog`
WHERE 
    VisitorID NOT IN 
        (SELECT VisitorID FROM `VisitorLog` WHERE `URL` LIKE '%page=checkout%')
GROUP BY VisitorID
木森分化 2024-10-07 18:02:42
select VisitorID, count(*)
from VisitorLog
where (select count(*) from VisitorLog v2 where v2.VisitorID = VisitorLog.VisitorID and v2.URL like '%page=checkout%') = 0
;
select VisitorID, count(*)
from VisitorLog
where (select count(*) from VisitorLog v2 where v2.VisitorID = VisitorLog.VisitorID and v2.URL like '%page=checkout%') = 0
;
陌上青苔 2024-10-07 18:02:42

我假设您的 visitorLog 表中的 URL 字段是foreign_key 和整数字段。

SELECT v1.VisitorID, COUNT(v1.VisitorID) AS TotalVisits
FROM `VisitorLog` v1, ?? as v2
WHERE v1.URL = v2.URL_ID
AND v2.URL NOT LIKE "%page=checkout%"
GROUP BY VisitorID

据我了解,例如。

id      visitor_id    datetime    page
1       1             Nov4        about us
2       1             Nov4        contact us
3       2             Nov4        about us
4       2             Nov3        about us
5       2             Nov4        home
6       1             Nov4        home


    select visitor_id, count(id) as numberofvisits 
    FROM visit_table_transaction 
    WHERE datetime = "Nov4"
    GROUP BY visitor_id
    HAVING numberofvisits > 2

So the result is 
visitor_id      numberofvisits
1               3

为什么需要使用having,因为聚合函数不能在WHERE中使用

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

你也可以 这样写的

select visitor_id, count(id) as numberofvisits 
FROM visit_table_transaction 
WHERE datetime = "Nov4"
GROUP BY visitor_id
HAVING count(id) > 2

下面是

 select visitor_id, count(id) as numberofvisits 
 FROM visit_table_transaction 
 WHERE datetime = "Nov4"
 AND count(id) > 2
 GROUP BY visitor_id 

,如有错误,请大家指正。
谢谢。

I assume your URL field in visitorLog table is foreign_key and integer field.

SELECT v1.VisitorID, COUNT(v1.VisitorID) AS TotalVisits
FROM `VisitorLog` v1, ?? as v2
WHERE v1.URL = v2.URL_ID
AND v2.URL NOT LIKE "%page=checkout%"
GROUP BY VisitorID

As from my understanding,eg.

id      visitor_id    datetime    page
1       1             Nov4        about us
2       1             Nov4        contact us
3       2             Nov4        about us
4       2             Nov3        about us
5       2             Nov4        home
6       1             Nov4        home


    select visitor_id, count(id) as numberofvisits 
    FROM visit_table_transaction 
    WHERE datetime = "Nov4"
    GROUP BY visitor_id
    HAVING numberofvisits > 2

So the result is 
visitor_id      numberofvisits
1               3

Why you need to use having, because aggregate function can't be use in WHERE

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

You can also write like this

select visitor_id, count(id) as numberofvisits 
FROM visit_table_transaction 
WHERE datetime = "Nov4"
GROUP BY visitor_id
HAVING count(id) > 2

Below is wrong

 select visitor_id, count(id) as numberofvisits 
 FROM visit_table_transaction 
 WHERE datetime = "Nov4"
 AND count(id) > 2
 GROUP BY visitor_id 

Any one please correct me if I am wrong.
Thanks.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文