我尝试使用不同的子句两次在同一个表中获取相同的列:
我的查询:
SELECT
*
FROM
(SELECT TOP 10
CONVERT(DATE, attemptdate) AS Date,
Max(currentcount) AS A
FROM
logintracking
INNER JOIN
maxuser ON logintracking.loginid = maxuser.loginid
INNER JOIN
site ON site.siteid = maxuser.defsite
WHERE
attemptdate BETWEEN @dateDebut AND @dateFin
AND logintracking.clientaddr IN ('10.118.254.21', '10.118.254.156')
GROUP BY
CONVERT(DATE, attemptdate)
ORDER BY
CONVERT(DATE, attemptdate) ASC
) AS T1,
(SELECT TOP 10
CONVERT(DATE, attemptdate) AS Date,
MAX(currentcount) AS B
FROM
logintracking
INNER JOIN
maxuser ON logintracking.loginid = maxuser.loginid
INNER JOIN
site ON site.siteid = maxuser.defsite
WHERE
attemptdate BETWEEN @dateDebut AND @dateFin
AND logintracking.clientaddr = '10.118.254.35'
GROUP BY
CONVERT(DATE, attemptdate)
ORDER BY
CONVERT(DATE, attemptdate) ASC) AS T2
结果:
想要的结果:
我的目标是两次获得同一列 'max(currentcount)' 并应用不同的 where 子句,以便获得名为 (A & B) 的两列,我还需要在第一列中显示日期,请帮忙? 谢谢
由于A
和之间的唯一区别B
是logintracking.clientaddr
,您可以将该条件放在函数内的CASE
语句中MAX
:
SELECT CONVERT(DATE, attemptdate) AS Date,
MAX(CASE WHEN logintracking.clientaddr IN ( '10.118.254.21', '10.118.254.156' ) THEN currentcount END) AS A,
MAX(CASE WHEN logintracking.clientaddr IN ( '10.118.254.35' ) THEN currentcount END) AS B
FROM logintracking
INNER JOIN maxuser
ON logintracking.loginid = maxuser.loginid
INNER JOIN site
ON site.siteid = maxuser.defsite
WHERE attemptdate BETWEEN @dateDebut AND @dateFin
GROUP BY CONVERT(DATE, attemptdate)
ORDER BY CONVERT(DATE, attemptdate) ASC
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句