TABLE1 其中Email是唯一的
-----------------------------------
id|Email |price
1 |[email protected] |1000
2 |[email protected] |800
------------------------------------
TABLE1_child 其中的每一个Email都并不是唯一,但是和TABLE1中一一对应
------------------------------------
id|Email |price
1 |[email protected] |100
2 |[email protected] |50
3 |[email protected] |200
------------------------------------TABEL2 其中email是唯一的,与table1中的email对应,但是price不一样
--------------------------------
id|Email |price
1 |[email protected] |500
2 |[email protected] |1000
----------------------------------
问题是这样的:从TABLE1中读取Email和price,让TABLE1中的price减去TABLE1_child中对应的email的price相加的和,然后与TABLE2中对应的email的price进行比较,,小于TABLE2中的显示在dbgrid中
-----------------------------------
id|Email |price
1 |[email protected] |1000
2 |[email protected] |800
------------------------------------
TABLE1_child 其中的每一个Email都并不是唯一,但是和TABLE1中一一对应
------------------------------------
id|Email |price
1 |[email protected] |100
2 |[email protected] |50
3 |[email protected] |200
------------------------------------TABEL2 其中email是唯一的,与table1中的email对应,但是price不一样
--------------------------------
id|Email |price
1 |[email protected] |500
2 |[email protected] |1000
----------------------------------
问题是这样的:从TABLE1中读取Email和price,让TABLE1中的price减去TABLE1_child中对应的email的price相加的和,然后与TABLE2中对应的email的price进行比较,,小于TABLE2中的显示在dbgrid中
from table1 t,table1_chile s,table2 x
where t.email=s.email(+) and t.email=x.email(+)
and nvl(t.price,0)-nvl(s.price,0)<nvl(x.price)
SELECT a.Email,a.Price-b.Price AS Price
(SELECT Email,Price FROM TABLE1) a ,
(SELECT Email,SUM(Price) AS Price FROM TABLE1_Child GROUP BY Email) b,
(SELECT Email,Price Table2) c WHERE a.Price<c.Price
from table1 t,(select email,sum(price) as price from table1_chile group by email) s,table2 x
where t.email=s.email(+) and t.email=x.email(+)
and nvl(t.price,0)-nvl(s.price,0)<nvl(x.price)1 楼写错了
(
select a.Email,(a.Price-b.Price)as Price from TABLE1 a inner join
(
select Email,sum(Price) as Price from TABLE1_child group by Email
) b on a.Email=b.Email
)a inner join TABEL2 b
on a.Email=b.Email and a.Price<b.Price
sql server 和access 都能用吗
(select A.EMail,A.Price -B.Price Price from TABLE1 A,
(select EMail,Sum(Price) Price from TABLE1_child group by EMail) B
where A.EMail=B.EMail) M,TABEL2 N
where M.EMail=N.EMail and M.Price <N.Price
SELECT a.Email,a.Price-b.Price AS Price
FROM TABLE1 a INNER JOIN
(SELECT Email,Price=SUM(Price) FROM TABLE1_Child GROUP BY Email) b
ON a.Email=b.EmailINNER JOIN Table2 c ON a.Email=c.Email
WHERE a.Price-b.Price<c.Price
(select a.email, a.price, (select sum(price) from table1_child where email=a.email) as childprice_sum,(select price from table2 where email=a.email) table2price
from table1 a) x
where price-childprice_sum<table2price