查询一个表中的字段和大于另一个表中的固定值的sql语句如何写

Question

表一有：mc,dj,sl三个字段
表二又：mc,xj两个字段
现在希望查询出sum(dj*sl)>xj的sql语句如何写？
已解决，group处少打了by

多谢各位的帮助

Answer 1

写法有很多种，下面这种比较容易理解：

select * from

(select
mc,--商品名称
sum(dj*sl) as je,--从表一中汇总出该商品的销售金额
(select xj from 表二 where mc=a.mc) as xj--从表二取得该商品销售金额
from 表一 a
group by mc) b--把查询结果作为一个虚拟的表

where je>xj--你希望的条件

Answer 2

我想问一下您的目的是不是想查询出：
表一与表二中mc一样的地方(比如说名称都是货物甲)，求出表1中凡是mc为货物甲的sum(dj*sl)，再与表2中mc为货物甲的xj比较，当前者大于后者时，便列出相应的mc单独列表？（还要不要列出其它字段？）

Answer 3

create table A1(
mc int,
dj int,
sl int
);

create table A2(
mc int,
xj int
);

select * from A1 for update;

MC DJ SL
1 1 2 3
2 2 3 4
3 3 4 5

select * from A2 for update;

MC XJ
1 1 17
2 2 3
3 4 5

select A1.MC
from A1,A2
where A1.MC=A2.MC
group by A1.MC,A1.dj,A1.sl,A2.xj
having sum(A1.dj*A1.sl)>A2.xj;

MC
1 2

Answer 4

select a.mc from table1 a,table2 b
where a.mc = b.mc
group by a.mc
having sum(dj*sl)>sum(b.xj)
你看行不

Answer 5

试试这个

select a.mc ,sum(dj*sl) from table1 a,table2 b
where a.mc = b.mc

group by a.mc

having sum(dj*sl)>sum(b.xj)本回答被提问者采纳