select decode(count(*),1,'同一组','不是同一组') from (select group_id from tbname where user_id in ('11,12,13') group by group_id);
select decode(count(*),1,'同一组','不是同一组') from (select distinct group_id from tbname where user_id in (11,12,13) );
to nicholaz(九思·逢尤):笔误,是user_id to dihai2000(haifeng):?? to bzszp(SongZip):thanks,现在我的问题是user_id来自别一个表table2: session_id user_id 1 13 2 31 1 12 4 11 2 11 其中primary key为(session_id,user_id),我要查出被分配了多个user_id的session_id,其中若属于该session_id的多个user_id属于同一group,则不算这个session_id被分配了多个user_id,例如对上面的数据来讲: 分配了多个user_id的session_id有:1和2,但分配给1的user_id(13,12)由于属于同一group,所以去除它,最后结果是只有2, 请问如何用sql语句实现这个需求?
table1:session_id user_id table2:group_id user_idselect max(user_id) from ( select session_id,max(user_id) user_id from (select table1.session_id,table1.user_id,table2.group_id from table1,table2 where table1.user_id = table2.user_id) t group by t.session_id,t.group_id having count(*)<2 ) group by session_id having count(*)>1;
12:38:39 SQL> select * from table1;SESSION_ID USER_ID ---------- ---------- 1 13 2 31 1 12 4 11 2 11实际:80 12:38:51 SQL> select * from table2;GROUP_ID USER_ID ---------- ---------- 1 11 2 21 1 12 3 31 1 13实际:80 12:38:56 SQL> select max(session_id) from ( 12:39:04 2 select session_id,max(user_id) user_id from ( 12:39:04 3 select table1.session_id,table1.user_id,table2.group_id from table1,table2 12:39:04 4 where table1.user_id = table2.user_id(+) 12:39:04 5 ) t group by t.session_id,t.group_id having count(*)<2 12:39:04 6 ) group by session_id having count(*)>1;MAX(SESSIO ---------- 2实际:50 12:39:06 SQL>
select a.* from table2 a,( select b.session_id, count(distinct c.group_id) no from table2 b, table1 c where b.user_id=c.user_id group by b.session_id) d where a.session_id = d.session_id and d.no>1
to dihai2000(haifeng):??
to bzszp(SongZip):thanks,现在我的问题是user_id来自别一个表table2:
session_id user_id
1 13
2 31
1 12
4 11
2 11
其中primary key为(session_id,user_id),我要查出被分配了多个user_id的session_id,其中若属于该session_id的多个user_id属于同一group,则不算这个session_id被分配了多个user_id,例如对上面的数据来讲:
分配了多个user_id的session_id有:1和2,但分配给1的user_id(13,12)由于属于同一group,所以去除它,最后结果是只有2,
请问如何用sql语句实现这个需求?
table2:group_id user_idselect max(user_id) from (
select session_id,max(user_id) user_id from (select table1.session_id,table1.user_id,table2.group_id from table1,table2 where table1.user_id = table2.user_id) t
group by t.session_id,t.group_id having count(*)<2
) group by session_id having count(*)>1;
---------- ----------
1 13
2 31
1 12
4 11
2 11实际:80
12:38:51 SQL> select * from table2;GROUP_ID USER_ID
---------- ----------
1 11
2 21
1 12
3 31
1 13实际:80
12:38:56 SQL> select max(session_id) from (
12:39:04 2 select session_id,max(user_id) user_id from (
12:39:04 3 select table1.session_id,table1.user_id,table2.group_id from table1,table2
12:39:04 4 where table1.user_id = table2.user_id(+)
12:39:04 5 ) t group by t.session_id,t.group_id having count(*)<2
12:39:04 6 ) group by session_id having count(*)>1;MAX(SESSIO
----------
2实际:50
12:39:06 SQL>
如:
SESSION_ID USER_ID
---------- ----------
2 31
2 11
则如何改:
用聚集函数的话不适合呀
from table2 b, table1 c where b.user_id=c.user_id group by b.session_id) d
where a.session_id = d.session_id and d.no>1