请教一个SQL语句，高手看看！

[lijun_shu]

一个表：
item color amount
a red 20
a blue 35
b red 16
b blue 13
怎么用SQL语句现实红色数目大于蓝色的item,并显示差直

[huahaoyueyuan]

select a.item,a.amount from 表 a,表 b where a.item<>b.item
我的思路是同一个表起两个不同的别名，这样就相当于一个表了，查询这两个表中记录item不相等的记录不就行了吗

[happy_0325]

select a.item from table a,table b where a.item=b.item and a.color='red' and b.color='blue' and a.amount>b.amount

[yoki]

select a.item,(a.amount-b.amount) as 差值 from
(
select * from 表 where color='rde'
)a,
(
select * from 表 where color='blue'
)b
where a.item=b.item and a.amount>b.amount

[ezhou]

要解决这个问题，要应用一个技巧，要给这一个表取两个别名，这样就容易多了。

[vrv0129]

select a.amount-b.amount from(select * from table where color=rde)a,(select * from table where color=blue)b where a.amount>b.amount

[伍子V5]

select a.item,(a.amount-b.amount) as 差值 from
(
select * from 表 where color='red'
)a,
(
select * from 表 where color='blue'
)b
where a.item=b.item and a.amount>b.amount