写法有很多种,下面这种比较容易理解:select * from (select mc,--商品名称 sum(dj*sl) as je,--从表一中汇总出该商品的销售金额 (select xj from 表二 where mc=a.mc) as xj--从表二取得该商品销售金额from 表一 agroup by mc) b--把查询结果作为一个虚拟的表where je>xj--你希望的条件
我想问一下您的目的是不是想查询出:表一与表二中mc一样的地方(比如说名称都是货物甲),求出表1中凡是mc为货物甲的sum(dj*sl),再与表2中mc为货物甲的xj比较,当前者大于后者时,便列出相应的mc单独列表?(还要不要列出其它字段?)
create table A1(mc int,dj int,sl int);create table A2(mc int,xj int);select * from A1 for update; MC DJ SL1 1 2 32 2 3 43 3 4 5select * from A2 for update; MC XJ1 1 172 2 33 4 5select A1.MCfrom A1,A2where A1.MC=A2.MC group by A1.MC,A1.dj,A1.sl,A2.xjhaving sum(A1.dj*A1.sl)>A2.xj; MC1 2
试试这个select a.mc ,sum(dj*sl) from table1 a,table2 bwhere a.mc = b.mcgroup by a.mchaving sum(dj*sl)>sum(b.xj)