SELECT a.ID AS uid, b.i AS total, a.*
FROM UserList a LEFT OUTER JOIN
(SELECT userid, COUNT(1) AS i
FROM setuplist
WHERE state = 1 GROUP BY userid) b ON a.ID = b.userid
WHERE (a.UserName <> 'test')
ORDER BY total DESC 这个语句,如何让他只返回total大于0和NULL的数据呢? 谢谢
FROM UserList a LEFT OUTER JOIN
(SELECT userid, COUNT(1) AS i
FROM setuplist
WHERE state = 1 GROUP BY userid) b ON a.ID = b.userid
WHERE (a.UserName <> 'test')
ORDER BY total DESC 这个语句,如何让他只返回total大于0和NULL的数据呢? 谢谢
FROM UserList a LEFT OUTER JOIN
(SELECT userid, COUNT(1) AS i
FROM setuplist
WHERE state = 1
GROUP BY userid
) b ON a.ID = b.userid
WHERE (a.UserName <> 'test') And (IsNull(b.i,0)>0 or b.i Is Null)
ORDER BY total DESC
加上带颜色的条件试试。
FROM UserList a LEFT OUTER JOIN
(SELECT userid, COUNT(1) AS i
FROM setuplist
WHERE state = 1 GROUP BY userid) b ON a.ID = b.userid
WHERE (a.UserName <> 'test') and (Not IsNull(b.i) And b.i > 0)
ORDER BY total DESC
SELECT a.ID AS uid, b.i AS total, a.*
FROM UserList a LEFT OUTER JOIN
(SELECT userid, COUNT(1) AS i
FROM setuplist
WHERE state = 1 GROUP BY userid) b ON a.ID = b.userid
WHERE (a.UserName <> 'test')
and (IsNull(b.i, 1) > 0)
ORDER BY total DESC
SELECT a.ID AS uid, b.i AS total, a.*
FROM UserList a LEFT OUTER JOIN
(SELECT userid, COUNT(1) AS i
FROM setuplist
WHERE state = 1 GROUP BY userid) b ON a.ID = b.userid
WHERE (a.UserName <> 'test')
and ((b.i is null) or (isnull(b.i,0) > 0))
ORDER BY total DESC