linghb99
linghb99
2015-08-11 23:29
采纳率: 100%
浏览 1.6k

这个sql语句如何写??

有3个表格,A、B、C

A表结构 (订单表)
id

Workfee (工时费)

B表结构 (订单产品表,一个订单有多个产品)
id
Aid (A表id的外键)
chanpinid (仓库产品的id)
shuliang (数量)

备注:A表与B表外键相连的。

C表格 (仓库表,B表的订单产品,都来自仓库!)
id

danjia (单价)

现在想查A的订单记录数:工时费 + 产品数量*单价 > 100块的。如何写sql??

我写的不成功。如下:

select distinct(A.id) from ((A JOIN B ON A.id=B.Aid ) inner join C on B.chanpinid=C.id GROUP BY Ai.d HAVING SUM(C.danjia*B.shuliang+A.workfee) >100

就好比:订购一个面包,有手工费,然后还有原料:面粉3克、盐巴1克,糖2克等,面粉等原材料价格保存在仓库表中的。

现在想知道: 手工费+面粉3克*价格 +盐巴1克*价格 +糖2克*价格 >100的面包,有几个!!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

5条回答 默认 最新

  • iherolp
    iherolp 2015-08-12 01:20
    已采纳

    标准答案:

    
    create table Table_A (
    id int,
    Workfee int
    )
    
    create table Table_B (
    id int,
    Aid int,
    chanpinid int ,
    shuliang int
    )
    
    create table Table_C (
    id int,
    danjia int
    );
    
    insert into Table_A values(100,30);
    insert into Table_A values(200,20);
    
    
    
    insert into Table_B values(1,100,10,10);
    insert into Table_B values(2,100,11,5);
    insert into Table_B values(3,100,12,5);
    insert into Table_B values(4,200,10,2);
    insert into Table_B values(5,200,11,2);
    
    
    insert into Table_C values(10,5);
    insert into Table_C values(11,1);
    insert into Table_C values(12,20);
    
    
    SQL:
    
    select a.id,SUM(Workfee + d.shangpinjiage ) from Table_A a,
        (select Aid,SUM(c.danjia * b.shuliang) shangpinjiage
           from Table_B b,Table_C c 
          where b.chanpinid = c.id and b.chanpinid =c.id
          group by Aid)  d
     where a.id = d.Aid
     group by a.id
    having SUM(Workfee + d.shangpinjiage ) > 100
    
    
    点赞 评论
  • CSDNXIAOS
    Robot-S 2015-08-11 23:41

    出自:http://community.csdn.net/Expert/topic/3372/3372925.xml?temp=.7849237
    --测试:
    create table js (年 int,月 int,降水 float)insert into jsselect 1971,1,34.5 unionselect 1971,2,23 unionselect 1971,3,56.0 un......
    答案就在这里:如何写这样的SQL语句?
    ----------------------Hi,地球人,我是问答机器人小S,上面的内容就是我狂拽酷炫叼炸天的答案,除了赞同,你还有别的选择吗?

    点赞 评论
  • oSuiFengPiaoDang12
    oSuiFengPiaoDang12 2015-08-12 00:23

    select a.id from a
    inner join (
    select *,C.danjia*B.shuliang as je from b
    inner join c on B.chanpinid=C.id) b
    on a.id=b.id
    where a.workfee+b.je>100
    大概就是这个思路吧,

    点赞 评论
  • danielinbiti
    danielinbiti 2015-08-12 01:14
     select a表.id,workfee from a
    left join
    (
    select aid,sum(num1) as num1 from 
    (
    select id,aid,(select danjia from c表 where c表.id=b表.chanpinid) * shuliang as num1 from b表
    ) k group by aid
    ) r on a.id=r.aid where workfee+num1>100
    
    点赞 评论
  • linghb99
    linghb99 2015-08-12 09:17

    select distinct(A.id) from A inner join
    ( select B.Aid,sum(C.danjia*B.shuliang) as yuanliaofee from B join C on B.Cid=C.id group by B.Aid) as temp
    on temp.Aid=A.id where A.workfee + temp.yuanliaofee >100

    这是我后来测试出来的。好像也能用。也谢谢大家的参与了。

    点赞 评论

相关推荐