一道题！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
表A,B   有著相同的結構     字段分別為：ID,   AMOUNT（字段类型是数字）。B表中的ID是主键。
表A
 
ID Amount

表B
ID(主键) Amount
 

請用一個SELECT語句返回滿足以下要求的數據集。
返回表A所有的ID、返回的字段包括：
ID,
A_AMOUNT(A表中相同ID的AMOUT的和)，
B_AMOUNT(B中對應ID的AMOUNT的值)，
STATUS(如果A_AMOUNT和B_AMOUNT的值相同為 'OK ',否則為 "NG ")


------解决方案--------------------
(select A1.id,A1.amount,A2.amount, 'OK ' from
(select id,sum(amount) as amount from A group by id) as A1 ,
(select id,amount from B ) as A2
where A1.id = A2.id and A1.amount = A2.amount ) union
(select A1.id,A1.amount,A2.amount, 'NG ' from
(select id,sum(amount) as amount from A group by id) as A1 ,
(select id,amount from B ) as A2
where A1.id = A2.id and A1.amount <> A2.amount )
------解决方案--------------------
select A.ID,A.AMOUNT A_AMOUNT,B.AMOUNT B_AMOUNT,
case A.AMOUNT when B.AMOUNT then 'OK ' else 'NG ' end STATUS
from A,B
where A.ID=B.ID
------解决方案--------------------
select A.ID,sum(A.AMOUNT) A_AMOUNT,B.AMOUNT B_AMOUNT,
case sum(A.AMOUNT) when B.AMOUNT then 'OK ' else 'NG ' end STATUS
from A,B
where A.ID=B.ID
group by A.ID,B.AMOUNT
------解决方案--------------------
select a.id,A_AMOUNT = sum(a.Amount),B_AMOUNT = max(b.AMOUNT),
STATUS = case when sum(a.Amount)= max(b.AMOUNT) then 'OK ' else 'NG end
from 表A a , 表B b
where a.id = b.id
group by a.id
------解决方案--------------------
select a.ID, A_AMOUNT, B_AMOUNT, case
when A_AMOUNT = B_AMOUNT then 'OK '
else 'NG '
end
(select ID, sum(a.Amount) as 'A_AMOUNT ' from TableA a group by a.ID )as T1
inner join
(select ID, sum(b.Amount) as 'B_AMOUNT ' from TableB b group by b.ID)as T2
on T1.ID = T2.ID
------解决方案--------------------
select T1.ID, A_AMOUNT, B_AMOUNT, case
when A_AMOUNT = B_AMOUNT then 'OK '
else 'NG '
end as STATUS
from
(select ID, sum(a.Amount) as 'A_AMOUNT ' from TableA a group by a.ID )as T1
inner join
(select ID, sum(b.Amount) as 'B_AMOUNT ' from TableB b group by b.ID)as T2
on T1.ID = T2.ID

------解决方案--------------------
--try

select tmpA.*, B_AMOUNT=isnull(B.Amount, 0),
STATUS=case when tmpA.A_AMOUNT=isnull(B.Amount, 0) then 'OK ' else 'NG ' end
from
(
select ID, A_AMOUNT=sum(Amount)
from A
group by ID
)tmpA
left join B on tmpA.ID=B.ID
------解决方案--------------------
select t.id ,t.A_AMOUNT A_AMOUNT,b.Amount as B_AMOUNT
,case when t.A_AMOUNT = b.Amount then 'OK ' else 'NG ' end as STATUS
from
(
select ID ,sum (AMOUNT) as A_AMOUNT
from A
group by ID
) t
left join b on t.id =b.id