ms sql Áú¹®ÇÕ´Ï´Ù..

   Á¶È¸ 5757   Ãßõ 0    

 

7. 세 명 이상의 사원이 참여한 프로젝트의 번호, 이름, 사원의 수를 보이시오.(검색 결과에 사원수열 이름 사용)

 

8. 세 명 이상의 사원이 있는 부서의 사원 이름, 전화번호, 성별, 부서명을 보이시오.


숙제인데..


1~6,9번은 해결을했는데 7번과 8번의 3명이상이라는게 어떤방향인지 감도 안잡히네요..


그룹바이 해빙절 ㎢쨉잘 안되는거 같네요.. 힌트점 부탁드립니다..


밑줄은 기본키입니다.. 쌍 밑줄은 복합키입니다.




Employee                                                               Department


열 이름

데이터타입

널 허용여부

empno

int

허용 안 함

name

varchar(20)

 

phoneno

int

 

address

varchar(20)

 

sex

varchar(20)

 

position

varchar(20)

 

deptno

int

 


열 이름

데이터타입

널 허용여부

deptno

int

허용 안 함

deptname

varchar(20)

 

manager

varchar(20)

 


 


Project                                                                  Works


열 이름

데이터타입

널 허용여부

projno

int

허용 안 함

projname

varchar(20)

 

deptno

int

 


열 이름

데이터타입

널 허용여부

empno

int

허용 안 함

projno

int

허용 안 함

hoursworked

int

 




!...
ªÀº±Û Àϼö·Ï ½ÅÁßÇÏ°Ô.
Ź«Áø 2015-04
Select  work.projno, Project.projename,COUNT(*) as »ç¿ø¼ö
      From work inner join Project
        on work.projno = Project.projno
      group by work.projno,Project.projename
      having COUNT(*) >=3

¶Ç´Â

select z.projno, Project.projename,z.»ç¿ø¼ö
from ( Select  work.projno, COUNT(*) as »ç¿ø¼ö
          From work
          group by work.projno
          having COUNT(*) >=3 ) as z
      inner join Project on z.projno = Project.projno

8¹øÀº ¼÷Á¦ÀÌ´Ï Âü°íÇؼ­ Á÷Á¢ Çغ¸½Ã±â ¹Ù¶ø´Ï´Ù.
     
¼ÛÁøÇö 2015-04
»ý°¢º¸´Ù group by°¡ À߾ȵ˴ϴÙ..

 ÇÏÇÏ..

 °øºÎ°¡ ´õ ÇÊ¿äÇÏ´Ù´Â ½ÅÈ£ÀΰŠ°°½À´Ï;´Ù..
Ź«Áø 2015-04
with Work  As
(
    Select  '1' As empno, 'p1' as projno, 10 as hoursworked Union All
    Select  '2' As empno, 'p1' as projno, 10 as hoursworked Union All
    Select  '3' As empno, 'p1' as projno, 10 as hoursworked Union All
    Select  '1' As empno, 'p2' as projno, 10 as hoursworked Union All
    Select  '1' As empno, 'p3' as projno, 10 as hoursworked Union All
    Select  '3' As empno, 'p3' as projno, 10 as hoursworked Union All   
    Select  '4' As empno, 'p3' as projno, 10 as hoursworked Union All
    Select  '5' As empno, 'p3' as projno, 10 as hoursworked Union All
    Select  '6' As empno, 'p3' as projno, 10 as hoursworked Union All
    Select  '1' As empno, 'p4' as projno, 10 as hoursworked Union All
    Select  '5' As empno, 'p4' as projno, 10 as hoursworked Union All
    Select  '7' As empno, 'p4' as projno, 10 as hoursworked 
   
)
,Project  As
(
    Select  'p1' as projno, 'Project 1' as projename, 'D1' as deptno  Union All
    Select  'p2' as projno, 'Project 2' as projename, 'D1' as deptno  Union All   
    Select  'p3' as projno, 'Project 3' as projename, 'D1' as deptno  Union All
    Select  'p4' as projno, 'Project 4' as projename, 'D1' as deptno  Union All
    Select  'p5' as projno, 'Project 5' as projename, 'D1' as deptno  Union All
    Select  'p6' as projno, 'Project 6' as projename, 'D1' as deptno
)


Select  work.projno, Project.projename,COUNT(*) as »ç¿ø¼ö
      From work inner join Project
        on work.projno = Project.projno
      group by work.projno,Project.projename
      having COUNT(*) >=3


QnA
Á¦¸ñPage 1202/5687
2014-05   4994402   Á¤ÀºÁØ1
2015-12   1530341   ¹é¸Þ°¡
2016-06   5871   ³ªÆÄÀÌ°­½ÂÈÆ
2016-06   8308   ´Ù¿Â
04-03   1153   Ä«ÀÌÁ¦¸°
2015-04   5758   ¼ÛÁøÇö
2013-12   20076   ¹èÁؼ®
2015-04   6965   ±è¿µ±â´ëÀü
2021-01   2229   ÇູÇϼ¼
2011-07   8097   huniz
2019-11   6778   DaVinci
2021-02   4622   ±¸½ÊÆò
04-16   2409   PC¹è¿ì¹Ì
2011-08   6524   ¾Æ¸§´Ù¿î³ëÀ»
2015-05   3935   ¹Ì´ã
2018-09   3025   ±×¸°
2019-11   2895   À¯ÅäÆÙ
2017-06   3158   °­ÇÏ°ÔÇÏÀÚ
2011-08   6851   ÀÓÁø¿í
2014-01   5771   ½Å¼î±â
2015-05   4353   ±èÁ¦¿¬
2018-09   4443   ¹Ú¹®Çü