一道题！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！

[wxylvmnn]

表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")

[realfool]

(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 )

[rookie_one]

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

[rookie_one]

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

[leo_lesley]

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

[s_hluo]

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

[wxylvmnn]

不好意思，好像都不对哈

[s_hluo]

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

[marco08]

--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

[wxylvmnn]

谢谢 marco08(天道酬勤)的回答。

强调A没有主键，如果有重复的数据，按照你的sql，会冲洗掉重复的数据。

所以，也不对。

[wxylvmnn]

s_hluo(笨笨熊) 的答案也不对。

[xiaoku]

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

[xiaoku]

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