데이터베이스 질문과 답변
select *,
(select count(1) from likey where a.ano = l.ano) as likey_cnt,
(select count(1) from likey) as cnt,
ROW_NUMBER () OVER (ORDER BY a.ano) as row_ano
from article a inner join likey l on a.ano = l.ano
where 3>=(select count(1) from likey)
order by row_ano desc
결과창..
ano title contents writer date category_name lno ano writer likey_cnt cnt
442 ㅎㅇㅎㅇ ㅎㅇㅎㅇ gd123 2019... 자유게시판 288 173 442 a 3 3
442 ㅎㅇㅎㅇ ㅎㅇㅎㅇ gd123 2019... 자유게시판 288 174 442 as 3 3
442 ㅎㅇㅎㅇ ㅎㅇㅎㅇ gd123 2019... 자유게시판 288 175 442 asd 3 3
현제 결과창인데요
442 ㅎㅇㅎㅇ ㅎㅇㅎㅇ gd123 2019... 자유게시판 288 173 442 a 3 3
한개만 나오게 하고싶습니다
by 마농
[2019.04.03 08:47:15]
SELECT *
FROM (SELECT a.ano, a.title, a.contents, a.writer, a.date, a.category_name
, l.lno
, l.writer AS lwriter
, ROW_NUMBER() OVER(PARTITION BY a.ano ORDER BY l.lno) rn
, COUNT(l.lno) OVER(PARTITION BY a.ano) likey_cnt
FROM article a
LEFT OUTER JOIN likey l
ON a.ano = l.ano
) a
WHERE rn = 1
AND likey_cnt <= 3
ORDER BY ano DESC
;
) 버튼을 클릭하여 작성 하시면 됩니다.