아래의 쿼리 중 5번째 줄 select top 20을 10으로 바꾸었을뿐인데 결과 값이 달라집니다
select top 10의 의미가 조회시 10건만 보여줘라라는 뜻이 아닌가요?
무슨이유로 이렇듯 결과 값이 달라졌을까요?
결과값은 원본으로 돌리면 아무것도 안나오고
수정본으로 돌리면 3건이 출력됩니다
--원본
Select Top 10 A.EmpGuid as GUID, C.DeptGuid as DeptGUID, TA_DB.dbo.fn_MakeDeptName
((SELECT DeptName FROM TA_DB.dbo.TB_MDEPTDATA D1 WHERE D1.DeptGUID = C.ParentDeptGuid ),C.DeptName) as DeptName,
TA_DB.DBO.FN_IPNUM2STR(USERIP) AS S_IP, A.EmpName, B.UserID, B.Type, B.AccessTime FROM TA_LOG.dbo.TB_WEBHISTORY
AS B JOIN TA_DB.dbo.TB_MEMPDATA AS A on A.EmpGuid=B.MemGuid left outer join TA_DB.dbo.TB_MDEPTDATA AS C on C.DeptGuid=A.DeptGuid
WHERE B.Guid NOT IN (Select Top 20 B.Guid FROM TA_LOG.dbo.TB_WEBHISTORY AS B JOIN TA_DB.dbo.TB_MEMPDATA AS A on A.EmpGuid=B.MemGuid
left outer join TA_DB.dbo.TB_MDEPTDATA AS C on C.DeptGuid=A.DeptGuid WHERE 1=1 and b.accountType=1 AND B.AccessTime >= '20151023 00:00'
AND B.AccessTime <= '20151023 23:59' ORDER BY AccessTime DESC, [type]) and b.accountType=1
AND B.AccessTime >= '20151023 00:00' AND B.AccessTime <= '20151023 23:59' ORDER BY AccessTime DESC, [type]
--수정
Select Top 10 A.EmpGuid as GUID, C.DeptGuid as DeptGUID, TA_DB.dbo.fn_MakeDeptName
((SELECT DeptName FROM TA_DB.dbo.TB_MDEPTDATA D1 WHERE D1.DeptGUID = C.ParentDeptGuid ),C.DeptName) as DeptName,
TA_DB.DBO.FN_IPNUM2STR(USERIP) AS S_IP, A.EmpName, B.UserID, B.Type, B.AccessTime FROM TA_LOG.dbo.TB_WEBHISTORY
AS B JOIN TA_DB.dbo.TB_MEMPDATA AS A on A.EmpGuid=B.MemGuid left outer join TA_DB.dbo.TB_MDEPTDATA AS C on C.DeptGuid=A.DeptGuid
WHERE B.Guid NOT IN (Select Top 10 B.Guid FROM TA_LOG.dbo.TB_WEBHISTORY AS B JOIN TA_DB.dbo.TB_MEMPDATA AS A on A.EmpGuid=B.MemGuid
left outer join TA_DB.dbo.TB_MDEPTDATA AS C on C.DeptGuid=A.DeptGuid WHERE 1=1 and b.accountType=1 AND B.AccessTime >= '20151023 00:00'
AND B.AccessTime <= '20151023 23:59' ORDER BY AccessTime DESC, [type]) and b.accountType=1
AND B.AccessTime >= '20151023 00:00' AND B.AccessTime <= '20151023 23:59' ORDER BY AccessTime DESC, [type]
감사합니다
|