M*****R
发帖数: 650 | 1 这样一个表格：
Visitor_ID | Session_ID | Count
1 11 1
1 12 2
2 13 1
2 14 1
3 15 1
需要找到所有的visitor,其count总数>=3，还要同时得到其所有的session，也就是说，
结果应该如下：
Visitor_ID | Session_ID | Count
1 11 1
1 12 2
也就是说，select中要有visitor_ID和session_ID，但是having的层次只能是
visitor_ID.
如果groupby 和having是这样
group by visitor_ID having count(count) >=3
我不能把session_ID放在select里面
如果groupby 和having是这样
group by visitor_ID, session_ID having count(count) >=3
则不会有找到任何行，因为同时在两个列上做了group by.
有没有什么办法解决这个问题？ |
B*****g
发帖数: 34098 | 2 什么数据库，什么version
说，
【在 M*****R 的大作中提到】
: 这样一个表格：
: Visitor_ID | Session_ID | Count
: 1 11 1
: 1 12 2
: 2 13 1
: 2 14 1
: 3 15 1
: 需要找到所有的visitor,其count总数>=3，还要同时得到其所有的session，也就是说，
: 结果应该如下：
: Visitor_ID | Session_ID | Count
|
h*******g
发帖数: 16 | 3 select visitor_id, session_id, count
from t
where exists (select sum(count)
from t t1
where visitor_id = t.visitor_id
having sum(count)>=3) |
M*****R
发帖数: 650 | 4 SQL server 2008，这个有影响吗？
【在 B*****g 的大作中提到】
: 什么数据库，什么version
:
: 说，
|
B*****g
发帖数: 34098 | 5 1.大多数本版sql问题都可以用PARTITION BY
2.老版本的数据库不一定支持PARTITION BY
http://msdn.microsoft.com/en-us/library/ms189461.aspx
【在 M*****R 的大作中提到】
: SQL server 2008，这个有影响吗？
|
M*****R
发帖数: 650 | 6 Thanks for the link, but how can OVER clause help here?
My problem is with the HAVING. I want the condition in HAVING to be
calculated on a different level from the groupby.
OVER doesn't seem to work with HAVING.
【在 B*****g 的大作中提到】
: 1.大多数本版sql问题都可以用PARTITION BY
: 2.老版本的数据库不一定支持PARTITION BY
: http://msdn.microsoft.com/en-us/library/ms189461.aspx
|
v*****r
发帖数: 1119 | 7 OVER can be used together with Groupby/Having, but there is no need to use
OVER in your case.
楼上贴得那个用 subquery (exists) 的就可以解决你的问题呀, 或者你也可以用
inline view, 比如：
select t.visitor_id, t.session_id, t.count, t_v_count.count_again
from t，(select visitor_id, count(count) count_again from t group by visitor
_id having count(count) >=3) t_v_count
where t.visitor_id = t_v_count.visitor_id
说，
【在 M*****R 的大作中提到】
: 这样一个表格：
: Visitor_ID | Session_ID | Count
: 1 11 1
: 1 12 2
: 2 13 1
: 2 14 1
: 3 15 1
: 需要找到所有的visitor,其count总数>=3，还要同时得到其所有的session，也就是说，
: 结果应该如下：
: Visitor_ID | Session_ID | Count
|
B*****g
发帖数: 34098 | 8 为什么一定要用having呢？
SELECT Visitor_ID, Session_ID, Count
FROM (SELECT Visitor_ID, Session_ID, Count, SUM(Count) OVER(PARTITION BY Vis
itor_ID) AS ‘TotalCount’ FROM table)
WHERE TotalCount>=3
【在 M*****R 的大作中提到】
: Thanks for the link, but how can OVER clause help here?
: My problem is with the HAVING. I want the condition in HAVING to be
: calculated on a different level from the groupby.
: OVER doesn't seem to work with HAVING.
|
v*****r
发帖数: 1119 | 9 不是说非要用 having, LZ 的 concern 是能否避免 subquery, 不管用aggregate 或
analytic function，他提的问题无法避免 subquery, aggregate/analytic function
都必须要用在 subquery 里。
Vis
【在 B*****g 的大作中提到】
: 为什么一定要用having呢？
: SELECT Visitor_ID, Session_ID, Count
: FROM (SELECT Visitor_ID, Session_ID, Count, SUM(Count) OVER(PARTITION BY Vis
: itor_ID) AS ‘TotalCount’ FROM table)
: WHERE TotalCount>=3
|
M*****R
发帖数: 650 | 10 多谢多谢。我就是想避免subquery，我在maintain一个query generation code logic
，现在
的设计不支持subquery。
看来是一定要改design了。
但是学到了OVER,也是很有用的，因为我的count要在不同的层上计算。我发现这个OVER
很有意义。
function
【在 v*****r 的大作中提到】
: 不是说非要用 having, LZ 的 concern 是能否避免 subquery, 不管用aggregate 或
: analytic function，他提的问题无法避免 subquery, aggregate/analytic function
: 都必须要用在 subquery 里。
:
: Vis
|
M*****R
发帖数: 650 | 11 OVER可以用在Having里面吗？为啥我的SQL SERVER 2008 R2说OVER cannot be used in
Having clause.
use
visitor
【在 v*****r 的大作中提到】
: OVER can be used together with Groupby/Having, but there is no need to use
: OVER in your case.
: 楼上贴得那个用 subquery (exists) 的就可以解决你的问题呀, 或者你也可以用
: inline view, 比如：
: select t.visitor_id, t.session_id, t.count, t_v_count.count_again
: from t，(select visitor_id, count(count) count_again from t group by visitor
: _id having count(count) >=3) t_v_count
: where t.visitor_id = t_v_count.visitor_id
:
: 说，
|
v*****r
发帖数: 1119 | 12 They can be used together with group/having, but not inside group/having.
The reason is analytic functions always the last step to be computed (except
ORDER BY clause), so only legal to be put in SELECT/ORDER BY.
Almost all analytic functions are designed by Oracle first, then got
accepted into SQL89/92 standards, so they should behave the same for all
rdbms including SQLSerer.
in
【在 M*****R 的大作中提到】
: OVER可以用在Having里面吗？为啥我的SQL SERVER 2008 R2说OVER cannot be used in
: Having clause.
:
: use
: visitor
|
M*****R
发帖数: 650 | 13 Thanks. Taking note of this.
group/having.
(except
【在 v*****r 的大作中提到】
: They can be used together with group/having, but not inside group/having.
: The reason is analytic functions always the last step to be computed (except
: ORDER BY clause), so only legal to be put in SELECT/ORDER BY.
: Almost all analytic functions are designed by Oracle first, then got
: accepted into SQL89/92 standards, so they should behave the same for all
: rdbms including SQLSerer.
:
: in
|
