여러 고수님들 부탁드립니다...꾸벅
문제는 아래와 같습니다.
테이블 --->>display_data
user_id|qa_number|site_id|question_number|upper_number|user_name
--------+---------+--------+-----------+------------+----------------
CL030001| 00000001|site0001| 00000001| 00000000|クライアント-04-
CL030001| 00000003|site0001| 00000002| 00000000|クライアント-04-
CL030002| 00000003|site0001| 00000002| 00000000|クライアント-04-
CL030003| 00000003|site0001| 00000002| 00000000|クライアント-04-
CL030001| 00000004|site0001| 00000003| 00000000|クライアント-04-
CL030002| 00000004|site0001| 00000003| 00000000|クライアント-04-
CL030003| 00000004|site0001| 00000003| 00000000|クライアント-04-
CL030001| 00000006|site0001| 00000005| 00000000|クライアント-04-
*****************************************************************
그리고
두번째 테이블--->>> qa_information
qa_number|site_id |question_number|user_id |type
---------+--------+---------------+--------+----
00000001|site0001| 00000001|CL040001| 0
00000002|site0001| 00000001|CL030001| 1
00000003|site0001| 00000002|CL040001| 0
00000004|site0001| 00000003|CL040001| 0
00000005|site0001| 00000003|CL030001| 1
00000006|site0001| 00000005|CL040001| 0
00000007|site0001| 00000005|CL030001| 1
00000008|site0001| 00000001|CL040001| 0
00000009|site0001| 00000001|CL040001| 0
00000010|site0001| 00000001|CL040001| 0
00000011|site0001| 00000001|CL030001| 1
(11 rows)
입니다.
------------------------------------------------------------------
쿼리는 다음과 같습니다...
SELECT distinct on question_number count(tb1.question_number)as question_count,TB2.qa_number, TB2.site_id, TB2.question_number, TB2.user_name, TB2.date, TB2.preference, TB2.close_flag, TB2.last_update,TB1.USER_ID, TB1.TYPE, TB1.QA_NUMBER as QANUMBER, TB1.title
FROM QA_INFORMATION AS TB1,DISPLAY_DATA AS TB2
WHERE TB1.QA_NUMBER IN
(SELECT TB1.QA_NUMBER FROM QA_INFORMATION AS TB1, DISPLAY_DATA AS TB2 WHERE TB2.USER_ID = 'CL030001' AND
TB2.QUESTION_NUMBER = TB1.QUESTION_NUMBER AND
TB2.SITE_ID = TB1.SITE_ID)
AND TB2.QUESTION_NUMBER = TB1.QUESTION_NUMBER AND TB2.SITE_ID =TB1.SITE_ID AND TB2.USER_ID = 'CL030001'
group by TB2.qa_number, TB2.site_id, TB2.question_number,
TB2.user_name, TB2.date,TB2.preference,
TB2.close_flag, TB2.last_update,TB1.USER_ID, TB1.TYPE,
TB1.QA_NUMBER,TB1.title;
==================================================================
결과
question_count|qa_number|site_id |question_number|user_name|date|preference|close_flag|last_update|
user_id |type|qanumber|title
--------------+---------+--------+---------------+-------------------+-------------+-------------+----------+-------------+--
1| 00000001|site0001| 00000001|クライアント-04-001|1027521639810|1028126439810| 0|1027546362664|CL
030001| 1|00000002|lsh -177
1| 00000003|site0001| 00000002|クライアント-04-001|1027531056519|1028135856519| 0|1027531056519|CL
040001| 0|00000003|aaaaaaaaa
1| 00000004|site0001| 00000003|クライアント-04-001|1027531083768|1028135883768| 0|1027531083768|CL
030001| 1|00000005|111111
1| 00000006|site0001| 00000005|クライアント-04-001|1027542195889|1028146995889| 0|1027542292297|CL
030001| 1|00000007|lsh-test 1-kaitou
(4 rows)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
저는 카운트를 구하려고 하는데 왠지 않되네요...
중간의 서브쿼리는 반드시 필요해요...
왜 COUNT가 모두 1로 나올까요.
분명 2로 나오야 하는것도 있는데.........
고수님 들 부탁 드립니다.
|