select a.userid, max(decode(sign(a.value-c.value), 1, a.value, c.value))
from A, B, C
where A.userid = b.user_id
and b.groupid = c.groupid
group by a.userid
from A, B, C
where A.userid = b.user_id
and b.groupid = c.groupid
group by a.userid
from
(select A.UserID, MAX(C.GroupValue) as Value
from A inner join B on A.UserID=B.UserID
inner join C on B.GroupID=C.GroupID
group by A.UserID
union
select A.UserID, MAX(A.Value) as Value
from A inner join B on A.UserID=B.UserID) as D
group by UserID
from
(select A.UserID, MAX(C.GroupValue) as Value
from A inner join B on A.UserID=B.UserID
inner join C on B.GroupID=C.GroupID
group by A.UserID union
select A.UserID, MAX(A.Value) as Value
from A
group by A.UserID
) as D
group by UserID
哈哈,这次我抢先了
from
(select A.UserID,
MAX( case when C.GroupValue>A.Value
then C.GroupValue
else A.Value) as Value
from A inner join B on A.UserID=B.UserID
inner join C on B.GroupID=C.GroupID
group by A.UserID
) as D
group by UserID
MAX( case when C.GroupValue>A.Value then C.GroupValue else A.Value) as Value
from A inner join B on A.UserID=B.UserID
inner join C on B.GroupID=C.GroupID
group by A.UserID
(select A.UserID,max(C.Value) Value
from A,B,C
where A.UserID = B.UserID and B.GroupID = C.GroupID
group by A.UserID union
select UserID,Value from A) tt
group by UserID
如果是ORACLE PL/SQL的话, 还可以用LEAST函数:
select a.userid, -(LEAST(-a.value,-c.value))
...如果是SQL SERVER, 那nononono(null,null)的最后一句效率最高
不過少了一個end.
...
MAX( case when C.GroupValue>A.Value then C.GroupValue else A.Value end ) as Value
...
Modules表
ModuleID ModuleName
我要列举每个ModuleID的UserID和Value值
from A inner join B on (A.UserID=B.UserID and A.ModuleID=B.ModuleID)
inner join C (on B.GroupID=C.GroupID And B.ModuleID=C.ModuleID)
inner join Module AS D on A.ModulID = D.ModuleID
group by A.ModuleID, D.ModuleName, A.UserID依样画葫芦,哈哈!