sql语句查询问题 (IN)

hg70hg70

photo

   PhotoID Name Userid Size
---------- ---------------------------------------- ---------- ---------- ----------
      1234 Photo1 1 258
      1246 Photo2 2 897
      1345 Photo3 3 980
      1890 Photo4 1 9000
      1990 Photo5 2 1087
      1991 Photo5.1 2 899

user

    Userid Name
---------- ---------------------------------------- ----------
      1 Mary
      2 Tom
      3 Tim
      4 Tony


我想用一条查询就可以找出哪一个用户拥有一张或者以上的图片,再显示它.为什么我这样子查询不行,搞不太清楚

select p.PhotoID,p.Name,u.Userid,u.Name
from photo p,user u
where p.Userid = u.p and Userid IN (
select Userid, count(ownerid)
from photo
group by Userid
having count(Userid) >= 1
);

renxiao2003

where p.Userid = u.p and Userid IN (

u.p，请问P在哪里，UserId是那个的UserID

hg70hg70

select p.PhotoID,p.Name,u.Userid,u.Name
from photo p,user u
where p.Userid = u.Userid and p.Userid IN (
select Userid, count(ownerid)
from photo
group by Userid
having count(Userid) >= 1
);

打错了! 还是不行! 那里错

renxiao2003

回复 3# hg70hg70


    in的语法不是这么写的。可以考虑改成用exists或者参照下面的改法：

1. 
   
2. select p.PhotoID,p.Name,u.Userid,u.Name
   
3. from photo p,user u
   
4. where p.Userid = u.Userid and p.Userid IN (
   
5. select userid from (
   
6. select Userid, count(ownerid)
   
7. from photo
   
8. group by Userid
   
9. having count(Userid) >= 1
   
10. )
    
11. );
    
12. 
    

复制代码

renxiao2003

1. select p.PhotoID,p.Name,u.Userid,u.Name
   
2. from photo p,user u
   
3. where p.Userid = u.Userid and p.Userid exists (
   
4. select Userid, count(ownerid)
   
5. from photo
   
6. group by Userid
   
7. having count(Userid) >= 1
   
8. );
   
9. 
   
10. 
    

复制代码

exists的语法，试试。

jacson007

select p.PhotoID,p.Name,u.Userid,u.Name
from photo p,user u
where p.Userid = u.p and Userid IN (
select Userid, count(photoid)
from photo
group by Userid
having count(photoid) >= 1
);

jacson007

说实话，没看出count(ownerid)
里面的ownerid哪冒出来的

doni

回复 4# renxiao2003


    不用再套一层

1. select p.PhotoID,p.Name,u.Userid,u.Name
   
2. from photo p,user u
   
3. where p.Userid = u.Userid and p.Userid IN (
   
4. select Userid
   
5. from photo
   
6. group by Userid
   
7. having count(*) >= 1
   
8. );

复制代码

having子句中的表达式不必要在select子句中出现

linuxboy823

1. hr@TEST15> select a.userid,a.name,b.shu from users a,(select userid,count(*) as shu from photo group by userid) b where a.userid=b.userid;
   
2. 
   
3.     USERID NAME                        SHU
   
4. ---------- -------------------- ----------
   
5.          1 Mary                          3
   
6.          2 Tom                           2
   
7.          3 Tim                           1
   

复制代码

linuxboy823

1. Execution Plan
   
2. ----------------------------------------------------------
   
3. Plan hash value: 3198465657
   
4. 
   
5. ------------------------------------------------------------------------------
   
6. | Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
   
7. ------------------------------------------------------------------------------
   
8. |   0 | SELECT STATEMENT     |       |     6 |   306 |     6  (34)| 00:00:01 |
   
9. |*  1 |  HASH JOIN           |       |     6 |   306 |     6  (34)| 00:00:01 |
   
10. |   2 |   TABLE ACCESS FULL  | USERS |     4 |   100 |     2   (0)| 00:00:01 |
    
11. |   3 |   VIEW               |       |     6 |   156 |     3  (34)| 00:00:01 |
    
12. |   4 |    HASH GROUP BY     |       |     6 |    78 |     3  (34)| 00:00:01 |
    
13. |   5 |     TABLE ACCESS FULL| PHOTO |     6 |    78 |     2   (0)| 00:00:01 |
    
14. ------------------------------------------------------------------------------
    
15. 
    
16. Predicate Information (identified by operation id):
    
17. ---------------------------------------------------
    
18. 
    
19.    1 - access("A"."USERID"="B"."USERID")
    
20. 
    
21. Note
    
22. -----
    
23.    - dynamic sampling used for this statement

复制代码