V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
ebony0319
V2EX  ›  随想

前端把查询插入写成这样是要闹哪样!

  •  
  •   ebony0319 · 2016-10-05 11:53:58 +08:00 · 5330 次点击
    这是一个创建于 2950 天前的主题,其中的信息可能已经有所发展或是发生改变。
    insert into cb (orderno,ordersn,cbtype,slcb,pjcb,rgfscb,rgktcb,rgxscb,rgzscb,rgbzcb,ddcb,slsycb,ktgz,xsgz,zsgz,bzgz,wfj,cbycyy)

    select orderno,ordersn,cbtype,slcb,pjcb,fscb,ktcb,xscb,zscb,bzcb,ddcb,slsycb,ktgz,xsgz,zsgz,bzgz,wfprice,cbycyy from (select a.orderno,a.ordersn,a.cbtype,isnull(fs.cbycyy,'')+isnull(sl.cbycyy,'')+isnull(pj.cbycyy,'')+isnull(rg.cbycyy,'')+isnull(dd.cbycyy,'')+case when a.iswf=1 and isnull(wfj.price,0)=0 then '外发价格为零' else '' end cbycyy,
    wfj.price wfprice,fs.fscb,sl.slcb,pj.pjcb,rg.ktcb,rg.xscb,rg.zscb,rg.bzcb,dd.ddcb,sycb.sycb/a.num slsycb,rggz.ktgz , rggz.xsgz, rggz.zsgz, rggz.bzgz
    From
    (select a.orderno,a.ordersn,a.num,a.productid,0 cbtype,a.jgs,a.iswf,a.orderdate
    from order a where num>0 and isnull(isch,0)=0
    union all
    select a.orderno,a.ordersn,a.num,a.productid,1 cbtype,a.jgs,a.iswf,a.orderdate
    from order a where num>0 and isnull(isch,0)=0 ) a

    left outer join

    (select a.orderno,a.ordersn,case when b.jgs<>'工厂' then 1 else 0 end cbtype,max(case when b.id is null then '空台材料与 BOM 不符' else '' end) cbycyy,
    sum(case when isnull(b.price,0)=0 then c.pjnum*c.bomweight*d.cbj2 else b.price*c.pjnum*c.bomweight end) fscb
    from order a
    inner join bom c on a.productid=c.productid
    inner join product d on d.id=c.pjid
    left outer join orderktrg b on a.orderno=b.orderno and a.ordersn=b.ordersn and c.pjid=b.rgid
    where d.productno like 'K%' and a.orderno = 'S0116050030' and a.ordersn=10 group by a.orderno,a.ordersn,b.jgs) fs

    on a.orderno=fs.orderno and a.ordersn=fs.ordersn and a.cbtype=fs.cbtype
    left outer join
    (select a.orderno,a.ordersn,max(case when c.orderno is not null and isnull(c.wlly,'')<>'客户' and isnull(c.cbj2,0)=0 and isnull(b.cbj2,0)=0 and isnull(b.rkprice,0)=0 then '价格为零' else '' end)+case when isnull(need.need,0) <> cc.slnumtotal*a.num then '数量不符' else '' end cbycyy,
    case when c.wlly<>'工厂' then 1 else 0 end cbtype,sum(c.num*case when isnull(c.cbj2,0)>0 then c.cbj2 when isnull(b.cbj2,0)<isnull(b.rkprice,0) then b.rkprice else b.cbj2 end)/case when a.num=0 then null else a.num end slcb
    from order a left outer join orderslxq c on a.orderno=c.orderno and a.ordersn=c.ordersn and c.wlly<>'客户'
    left outer join product b on c.slid=b.id
    inner join product cc on a.productid=cc.id
    left outer join
    (select orderno,ordersn,sum(num) need
    from orderslxq a
    where 1=1 and a.orderno = 'S0116050030' and a.ordersn=10 group by a.orderno,a.ordersn
    ) need on a.orderno=need.orderno and a.ordersn=need.ordersn
    where 1=1 and a.orderno = 'S0116050030' and a.ordersn=10
    group by a.orderno,a.ordersn,a.num,c.wlly,cc.slnumtotal,need.need) sl

    on a.orderno=sl.orderno and a.ordersn=sl.ordersn and a.cbtype=sl.cbtype
    left outer join
    (select a.orderno,a.ordersn,max(case when isnull(b.cbj2,0)=0 and isnull(b.rkprice,0)=0 then '配件价格为零' else '' end)+ case when need.need <> cc.pjnumtotal*c.num then '数量不符' else '' end cbycyy,
    case when a.wlly<>'工厂' then 1 else 0 end cbtype,sum(a.num*case when isnull(b.cbj2,0)<isnull(b.rkprice,0) then b.rkprice else b.cbj2 end)/case when c.num=0 then null else c.num end pjcb
    from orderpjxq a
    inner join product b on a.xxid=b.id
    inner join order c on a.orderno=c.orderno and a.ordersn=c.ordersn
    inner join product cc on c.productid=cc.id
    inner join (select orderno,ordersn,sum(num) need
    from orderpjxq a where 1=1 and a.orderno = 'S0116050030' and a.ordersn=10 group by a.orderno,a.ordersn) need on a.orderno=need.orderno and a.ordersn=need.ordersn
    where 1=1 and a.wlly<>'客户' and a.orderno = 'S0116050030' and a.ordersn=10 group by a.orderno,a.ordersn,c.num,a.wlly,cc.pjnumtotal,need.need) pj

    on a.orderno=pj.orderno and a.ordersn=pj.ordersn and a.cbtype=pj.cbtype
    left outer join
    (select a.orderno,a.ordersn,max(case when b.id is null and d.productname not like '%镶%' then '项目不符' when d.productname like '%镶%' and a.orderdate>='2012-03-21' and isnull(xsrg2.ycyy,0)>0 then '人工项目与 BOM 不符' else '' end) cbycyy,
    case when b.jgs<>'工厂' then 1 else 0 end cbtype,
    sum(case when d.productno like 'R%' and d.productname not like '%镶%' and d.productname not like '%包装%' and d.productname not like '%抹黑%' and d.productname not like '%粘%' and d.productname not like '%滴%' then case when isnull(b.price,0)=0 then c.bomprice
    else b.price end*c.pjnum*c.bs else Null end ) ktcb,
    case when a.orderdate>='2012-03-21' then xsrg2.xsrg when isnull(xsrg2.xsrg,0)=0 then sum(case when d.productno like 'R%' and d.productname like '%镶%' then case when isnull(b.price,0)=0 then c.bomprice else b.price end*c.pjnum*c.bs else Null end ) else xsrg2.xsrg end xscb,
    sum(case when d.productno like 'R%' and (d.productname like '%粘%' or d.productname like '%抹黑%' or d.productname like '%滴%') then case when isnull(b.price,0)=0 then c.bomprice else b.price end*c.pjnum*c.bs else Null end ) zscb,
    sum(case when d.productno like 'R%' and d.productname like '%包装%' then case when isnull(b.price,0)=0 then c.bomprice else b.price end*c.pjnum*c.bs else Null end ) bzcb
    from order a inner join bom c on a.productid=c.productid
    inner join product d on d.id=c.pjid
    left outer join orderktrg b on a.orderno=b.orderno and a.ordersn=b.ordersn and c.pjid=b.rgid
    left outer join (select a.orderno,a.ordersn,
    case when isnull(a.jgs,'')<>'工厂' and isnull(a.jgs,'')<>'' then 1 else 0 end cbtype,
    sum(case when isnull(a.price_jgs,0)=0 and isnull(b.price_bom,0)=0 then 1 else 0 end) ycyy,
    sum(case when isnull(a.price_jgs,0)=0 then b.price_bom else a.price_jgs end* a.num/case when isnull(c.num,0)=0 then null else c.num end) xsrg
    from orderslxq a
    inner join product p on a.slid=p.id and p.productno not like 'P%'
    left outer join gyname b on a.gyid=b.id
    inner join order c on a.orderno=c.orderno and a.ordersn=c.ordersn where 1=1 and a.orderno like '%S0116050030%' and a.ordersn=10 and isnull(a.gxid,0)<>6
    group by a.orderno,a.ordersn,a.jgs) xsrg2 on a.orderno=xsrg2.orderno and a.ordersn=xsrg2.ordersn and case when b.jgs<>'工厂' then 1 else 0 end=xsrg2.cbtype where 1=1 and d.productno like
    'R%' and a.orderno like '%S0116050030%' and a.ordersn=10 group by a.orderdate,a.orderno,a.ordersn,xsrg2.ycyy,xsrg2.xsrg,
    case when b.jgs<>'工厂' then 1 else 0 end) rg

    on a.orderno=rg.orderno and a.ordersn=rg.ordersn and a.cbtype=rg.cbtype
    left outer join
    (select a.orderno,a.ordersn,a.cbtype,a.productid,max(CASE WHEN a.ddprice=0 then '价格为零' else '' end) cbycyy,
    case when SUM(a.ddnum)=0 then sum(1*a.ddprice) else SUM(a.ddnum * a.ddprice) / case when SUM(a.ddnum)=0 then 1 else sum(a.ddnum) end end as ddcb,
    case when SUM(a.ddnum)=0 then 1 else sum(a.ddnum) end as ddnum
    From (SELECT a.orderno, a.ordersn,a.cbtype,a.productid,a.ddnum,a.cureid,
    case when isnull(a.curprice,0)>0 then a.curprice when c.id is not null then c.ddprice when c1.id is not null then c1.ddprice when c2.id is not null then c2.ddprice else 0 end as ddprice
    FROM (SELECT a.orderno, a.ordersn,case when isnull(b.jgs, '')<>'工厂' and
    isnull(b.jgs,'')<>'' then 1 else 0 end cbtype,b.cureid, b.platcorlor, b.ddnum, b.jgs,C.ProductID , C.Productname, C.productid1, C.productid2, b.curprice
    FROM order a
    inner JOIN orderdd b ON a.orderno = b.orderno AND a.ordersn = b.ordersn and b.platcorlor<>'铜'
    INNER JOIN
    (select a.id as productid,a.photo,a.productname,a.productno, b.id as productid1,c.id as productid2
    from product a
    left outer join product b on replace(CASE WHEN len(a.productno)- len(replace(a.productno, '-', '')) > 1 THEN LEFT(a.productno,charindex('-', a.productno, charindex('-', a.productno, 1) + 1) - 1) ELSE a.productno end,'A','')+'A'=b.productno
    left outer join product c on replace(CASE WHEN len(a.productno)- len(replace(a.productno, '-', '')) > 1 THEN LEFT(a.productno,charindex('-', a.productno, charindex('-', a.productno, 1) + 1) - 1) ELSE a.productno end,'A','')=c.productno ) c
    ON a.productid = c.productid) a
    left outer join productddprice C ON (a.productid=c.productid) AND a.cureid = c.cureid AND a.platcorlor = c.platcorlor
    left outer join productddprice c1 ON (a.productid1=c1.productid) AND a.cureid = c1.cureid AND a.platcorlor = c1.platcorlor
    left outer join productddprice c2 ON (a.productid2=c2.productid) AND a.cureid = c2.cureid AND A.PlatCorlor = c2.PlatCorlor) a
    where 1=1 and a.orderno like '%S0116050030%' and a.ordersn=10 group by a.orderno,a.ordersn,a.productid,a.cbtype) dd

    on a.orderno=dd.orderno and a.ordersn=dd.ordersn and a.cbtype=dd.cbtype
    left outer join
    (select sycb.orderno,sycb.ordersn,case when isnull(sycb.sycb,0)>isnull(tlcb.tlcb,0) then isnull(sycb.sycb,0)-isnull(tlcb.tlcb,0) else null end sycb from (select orderno,ordersn,sum(cb) sycb
    From
    (select orderno,ordersn,outnum*case when isnull(b.cbj2 ,0)<isnull(b.rkprice,0) then b.rkprice else b.cbj2 end cb
    from dbwlsqdetail a
    left outer join product b on a.slid=b.id and isnull(a.outnum,0)>0 where 1=1 and a.orderno like '%S0116050030%' and a.ordersn=10
    Union All
    select orderno,ordersn,outnum*case when isnull(b.cbj2 ,0)<isnull(b.rkprice,0) then b.rkprice else b.cbj2 end cb
    from dbwlsqdetail2 a
    left outer join product b on a.slid=b.id and isnull(a.outnum,0)>0
    where a.orderno ='??????' and a.ordersn=10 ) a group by orderno,ordersn) sycb
    left outer join
    (select a.orderno,a.ordersn,sum(a.shnum*case when isnull(b.cbj2 ,0)<isnull(b.rkprice,0) then b.rkprice else b.cbj2 end) tlcb
    from rksq a
    inner join product b on a.slid=b.id and isnull(a.shnum,0)>0 where isnull(cgdh,'')='' and a.orderno like '%S0116050030%' and a.ordersn=10
    group by a.orderno,a.ordersn) tlcb
    on sycb.orderno=tlcb.orderno and sycb.ordersn=tlcb.ordersn) sycb

    on a.orderno=sycb.orderno and a.ordersn=sycb.ordersn and a.cbtype=0
    left outer join
    (select a.orderno,a.ordersn,'' cbycyy,sum(case when d.productno like 'R%' and d.productname not like '%镶%' and d.productname not like '%包装%' and
    d.productname not like '%抹黑%' and d.productname not like '%粘%' and d.productname not like '%滴%' then b.productnum*b.storenum*b.gxprice else Null end) ktgz,
    sum(case when e.gyname like '%镶%' then b.productnum*b.storenum*b.gxprice else Null end) xsgz,
    sum(case when d.productno like 'R%' and (d.productname like '%粘%' or d.productname like '%抹黑%' or d.productname like '%滴%') then b.productnum*b.storenum*b.gxprice else Null end) zsgz,
    sum(case when d.productno like 'R%' and d.productname like '%包装%' then b.productnum*b.storenum*b.gxprice else Null end) bzgz
    from order a
    inner join productnote b on a.orderno=b.orderno and a.ordersn=b.ordersn
    left outer join bom c on a.productid=c.productid and b.rgid=c.pjid
    left outer join product d on b.rgid=d.id
    left outer join gyname e on b.gyid=e.id
    where 1=1 and a.orderno like '%S0116050030%' and a.ordersn=10 group by a.orderno,a.ordersn) rggz

    on a.orderno=rggz.orderno and a.ordersn=rggz.ordersn and a.cbtype=0
    left outer join cure jgs ON a.jgs = jgs.curename
    LEFT OUTER JOIN wfprice wfj ON a.productid = wfj.productid AND jgs.id = wfj.cureid
    where 1=1 and a.orderno like '%S0116050030%' and a.ordersn=10 ) cb

    嗯,写得完美。
    但是结果 select 那个结果为空了。尼玛。
    开始找问题。用了一个小时才在换行,把层级结构搞清楚一点。然后一段一段试。你一个 insert 语句写这么复杂是要闹哪样。分段来不行么!!!!!!
    43 条回复    2017-03-05 09:27:31 +08:00
    jydeng
        1
    jydeng  
       2016-10-05 11:55:04 +08:00
    哈哈哈,这 SQL 写的 6
    lemontang
        2
    lemontang  
       2016-10-05 11:56:21 +08:00 via Android
    其实楼主要的是我们的吐槽吧
    geeglo
        3
    geeglo  
       2016-10-05 11:56:23 +08:00
    =.= 看晕了,里面似乎还带 逻辑部分?
    ebony0319
        4
    ebony0319  
    OP
       2016-10-05 11:59:26 +08:00
    @geeglo 逻辑,算法都在里面。
    subpo
        5
    subpo  
       2016-10-05 12:00:05 +08:00
    就问你怎么写逻辑写到比原生 sql 快?
    ebony0319
        6
    ebony0319  
    OP
       2016-10-05 12:07:27 +08:00
    @subpo 传过来就是一堆字符串了。出了一点问题要查出来太难了。我觉得一段一段 update 比这个好。至少能够快速定位哪里出错了。
    Troevil
        7
    Troevil  
       2016-10-05 12:11:17 +08:00
    66666 这个 sql debug 起来不是一般的困难
    viko16
        8
    viko16  
       2016-10-05 12:29:28 +08:00
    啊,好像在读英文文章
    22too
        9
    22too  
       2016-10-05 12:40:46 +08:00
    突然感觉,这个 sql 写了几天吧,然后调试花费了半个月。
    rannnn
        10
    rannnn  
       2016-10-05 12:46:50 +08:00
    真的不是自动生成出来的吗
    tabris17
        11
    tabris17  
       2016-10-05 12:47:27 +08:00   ❤️ 1
    为什么前端要写 SQL ?
    x86
        12
    x86  
       2016-10-05 12:47:58 +08:00 via iPhone
    写成这样也是 666
    amon
        13
    amon  
       2016-10-05 12:56:14 +08:00 via iPhone
    前端好辛苦,居然还写 SQL ,莫非是传说中的拍黄片?
    aploium
        14
    aploium  
       2016-10-05 13:08:12 +08:00
    lz 这是半年份的膝盖请收好 (支持殴打写它的那个人)
    loading
        15
    loading  
       2016-10-05 13:18:16 +08:00 via Android
    手机滚了半天……
    scnace
        16
    scnace  
       2016-10-05 13:32:33 +08:00 via Android
    估计 leetcode 上刷 sql 的题刷惯了 hhhh
    Infernalzero
        17
    Infernalzero  
       2016-10-05 13:35:38 +08:00
    这种 sql 语句一般不是人写出来的,是用框架生成的,前端不懂数据库这块肯定是直接用框架抽象成表达式调用的
    ebony0319
        18
    ebony0319  
    OP
       2016-10-05 13:45:54 +08:00
    @Infernalzero 我倒前端看了。确实是手写的。
    ByZHkc3
        19
    ByZHkc3  
       2016-10-05 14:26:13 +08:00
    前端还要写 SQL ?
    zsx
        20
    zsx  
       2016-10-05 16:52:49 +08:00
    这条 SQL ……一次要查多久……
    letitbesqzr
        21
    letitbesqzr  
       2016-10-05 17:04:51 +08:00 via iPhone
    @Infernalzero 但事实是的确有很多人会这样写。。。我们公司的老项目 随处可见这样的 sql 。。虽然三天两头的把 Oracle 都得跑挂。
    ddou
        22
    ddou  
       2016-10-05 17:38:59 +08:00 via Android
    直接 fire 吧
    jarlyyn
        23
    jarlyyn  
       2016-10-05 17:42:38 +08:00 via Android
    前端写 SQL ,还能 insert ???
    jhdxr
        24
    jhdxr  
       2016-10-05 18:06:44 +08:00   ❤️ 2
    @ebony0319 你需要 navicat (的自动格式化

    INSERT INTO cb (
       orderno,
       ordersn,
       cbtype,
       slcb,
       pjcb,
       rgfscb,
       rgktcb,
       rgxscb,
       rgzscb,
       rgbzcb,
       ddcb,
       slsycb,
       ktgz,
       xsgz,
       zsgz,
       bzgz,
       wfj,
       cbycyy
    ) SELECT
       orderno,
       ordersn,
       cbtype,
       slcb,
       pjcb,
       fscb,
       ktcb,
       xscb,
       zscb,
       bzcb,
       ddcb,
       slsycb,
       ktgz,
       xsgz,
       zsgz,
       bzgz,
       wfprice,
       cbycyy
    FROM
      (
         SELECT
           a.orderno,
           a.ordersn,
           a.cbtype,
           isnull(fs.cbycyy, '') + isnull(sl.cbycyy, '') + isnull(pj.cbycyy, '') + isnull(rg.cbycyy, '') + isnull(dd.cbycyy, '') + CASE
         WHEN a.iswf = 1
         AND isnull(wfj.price, 0) = 0 THEN
          '外发价格为零'
         ELSE
          ''
         END cbycyy,
         wfj.price wfprice,
         fs.fscb,
         sl.slcb,
         pj.pjcb,
         rg.ktcb,
         rg.xscb,
         rg.zscb,
         rg.bzcb,
         dd.ddcb,
         sycb.sycb / a.num slsycb,
         rggz.ktgz,
         rggz.xsgz,
         rggz.zsgz,
         rggz.bzgz
       FROM
        (
           SELECT
             a.orderno,
             a.ordersn,
             a.num,
             a.productid,
             0 cbtype,
             a.jgs,
             a.iswf,
             a.orderdate
           FROM
             ORDER a
           WHERE
             num > 0
           AND isnull(isch, 0) = 0
           UNION ALL
             SELECT
               a.orderno,
               a.ordersn,
               a.num,
               a.productid,
               1 cbtype,
               a.jgs,
               a.iswf,
               a.orderdate
             FROM
               ORDER a
             WHERE
               num > 0
             AND isnull(isch, 0) = 0
        ) a
       LEFT OUTER JOIN (
         SELECT
           a.orderno,
           a.ordersn,
           CASE
         WHEN b.jgs <> '工厂' THEN
           1
         ELSE
           0
         END cbtype,
         max(
           CASE
           WHEN b.id IS NULL THEN
            '空台材料与 BOM 不符'
           ELSE
            ''
           END
        ) cbycyy,
         sum(
           CASE
           WHEN isnull(b.price, 0) = 0 THEN
             c.pjnum * c.bomweight * d.cbj2
           ELSE
             b.price * c.pjnum * c.bomweight
           END
        ) fscb
       FROM
         ORDER a
       INNER JOIN bom c ON a.productid = c.productid
       INNER JOIN product d ON d.id = c.pjid
       LEFT OUTER JOIN orderktrg b ON a.orderno = b.orderno
       AND a.ordersn = b.ordersn
       AND c.pjid = b.rgid
       WHERE
         d.productno LIKE 'K%'
       AND a.orderno = 'S0116050030'
       AND a.ordersn = 10
       GROUP BY
         a.orderno,
         a.ordersn,
         b.jgs
      ) fs ON a.orderno = fs.orderno
       AND a.ordersn = fs.ordersn
       AND a.cbtype = fs.cbtype
       LEFT OUTER JOIN (
         SELECT
           a.orderno,
           a.ordersn,
           max(
             CASE
             WHEN c.orderno IS NOT NULL
             AND isnull(c.wlly, '') <> '客户'
             AND isnull(c.cbj2, 0) = 0
             AND isnull(b.cbj2, 0) = 0
             AND isnull(b.rkprice, 0) = 0 THEN
              '价格为零'
             ELSE
              ''
             END
          ) + CASE
         WHEN isnull(need.need, 0) <> cc.slnumtotal * a.num THEN
          '数量不符'
         ELSE
          ''
         END cbycyy,
         CASE
       WHEN c.wlly <> '工厂' THEN
         1
       ELSE
         0
       END cbtype,
       sum(
         c.num * CASE
         WHEN isnull(c.cbj2, 0) > 0 THEN
           c.cbj2
         WHEN isnull(b.cbj2, 0) < isnull(b.rkprice, 0) THEN
           b.rkprice
         ELSE
           b.cbj2
         END
      ) / CASE
    WHEN a.num = 0 THEN
       NULL
    ELSE
       a.num
    END slcb
    FROM
       ORDER a
    LEFT OUTER JOIN orderslxq c ON a.orderno = c.orderno
    AND a.ordersn = c.ordersn
    AND c.wlly <> '客户'
    LEFT OUTER JOIN product b ON c.slid = b.id
    INNER JOIN product cc ON a.productid = cc.id
    LEFT OUTER JOIN (
       SELECT
         orderno,
         ordersn,
         sum(num) need
       FROM
         orderslxq a
       WHERE
         1 = 1
       AND a.orderno = 'S0116050030'
       AND a.ordersn = 10
       GROUP BY
         a.orderno,
         a.ordersn
    ) need ON a.orderno = need.orderno
    AND a.ordersn = need.ordersn
    WHERE
       1 = 1
    AND a.orderno = 'S0116050030'
    AND a.ordersn = 10
    GROUP BY
       a.orderno,
       a.ordersn,
       a.num,
       c.wlly,
       cc.slnumtotal,
       need.need
      ) sl ON a.orderno = sl.orderno
       AND a.ordersn = sl.ordersn
       AND a.cbtype = sl.cbtype
       LEFT OUTER JOIN (
         SELECT
           a.orderno,
           a.ordersn,
           max(
             CASE
             WHEN isnull(b.cbj2, 0) = 0
             AND isnull(b.rkprice, 0) = 0 THEN
              '配件价格为零'
             ELSE
              ''
             END
          ) + CASE
         WHEN need.need <> cc.pjnumtotal * c.num THEN
          '数量不符'
         ELSE
          ''
         END cbycyy,
         CASE
       WHEN a.wlly <> '工厂' THEN
         1
       ELSE
         0
       END cbtype,
       sum(
         a.num * CASE
         WHEN isnull(b.cbj2, 0) < isnull(b.rkprice, 0) THEN
           b.rkprice
         ELSE
           b.cbj2
         END
      ) / CASE
    WHEN c.num = 0 THEN
       NULL
    ELSE
       c.num
    END pjcb
    FROM
       orderpjxq a
    INNER JOIN product b ON a.xxid = b.id
    INNER JOIN ORDER c ON a.orderno = c.orderno
    AND a.ordersn = c.ordersn
    INNER JOIN product cc ON c.productid = cc.id
    INNER JOIN (
       SELECT
         orderno,
         ordersn,
         sum(num) need
       FROM
         orderpjxq a
       WHERE
         1 = 1
       AND a.orderno = 'S0116050030'
       AND a.ordersn = 10
       GROUP BY
         a.orderno,
         a.ordersn
    ) need ON a.orderno = need.orderno
    AND a.ordersn = need.ordersn
    WHERE
       1 = 1
    AND a.wlly <> '客户'
    AND a.orderno = 'S0116050030'
    AND a.ordersn = 10
    GROUP BY
       a.orderno,
       a.ordersn,
       c.num,
       a.wlly,
       cc.pjnumtotal,
       need.need
      ) pj ON a.orderno = pj.orderno
       AND a.ordersn = pj.ordersn
       AND a.cbtype = pj.cbtype
       LEFT OUTER JOIN (
         SELECT
           a.orderno,
           a.ordersn,
           max(
             CASE
             WHEN b.id IS NULL
             AND d.productname NOT LIKE '%镶%' THEN
              '项目不符'
             WHEN d.productname LIKE '%镶%'
             AND a.orderdate >= '2012-03-21'
             AND isnull(xsrg2.ycyy, 0) > 0 THEN
              '人工项目与 BOM 不符'
             ELSE
              ''
             END
          ) cbycyy,
           CASE
         WHEN b.jgs <> '工厂' THEN
           1
         ELSE
           0
         END cbtype,
         sum(
           CASE
           WHEN d.productno LIKE 'R%'
           AND d.productname NOT LIKE '%镶%'
           AND d.productname NOT LIKE '%包装%'
           AND d.productname NOT LIKE '%抹黑%'
           AND d.productname NOT LIKE '%粘%'
           AND d.productname NOT LIKE '%滴%' THEN
             CASE
           WHEN isnull(b.price, 0) = 0 THEN
             c.bomprice
           ELSE
             b.price
           END * c.pjnum * c.bs
           ELSE
             NULL
           END
        ) ktcb,
         CASE
       WHEN a.orderdate >= '2012-03-21' THEN
         xsrg2.xsrg
       WHEN isnull(xsrg2.xsrg, 0) = 0 THEN
         sum(
           CASE
           WHEN d.productno LIKE 'R%'
           AND d.productname LIKE '%镶%' THEN
             CASE
           WHEN isnull(b.price, 0) = 0 THEN
             c.bomprice
           ELSE
             b.price
           END * c.pjnum * c.bs
           ELSE
             NULL
           END
        )
       ELSE
         xsrg2.xsrg
       END xscb,
       sum(
         CASE
         WHEN d.productno LIKE 'R%'
         AND (
           d.productname LIKE '%粘%'
           OR d.productname LIKE '%抹黑%'
           OR d.productname LIKE '%滴%'
        ) THEN
           CASE
         WHEN isnull(b.price, 0) = 0 THEN
           c.bomprice
         ELSE
           b.price
         END * c.pjnum * c.bs
         ELSE
           NULL
         END
      ) zscb,
       sum(
         CASE
         WHEN d.productno LIKE 'R%'
         AND d.productname LIKE '%包装%' THEN
           CASE
         WHEN isnull(b.price, 0) = 0 THEN
           c.bomprice
         ELSE
           b.price
         END * c.pjnum * c.bs
         ELSE
           NULL
         END
      ) bzcb
    FROM
       ORDER a
    INNER JOIN bom c ON a.productid = c.productid
    INNER JOIN product d ON d.id = c.pjid
    LEFT OUTER JOIN orderktrg b ON a.orderno = b.orderno
    AND a.ordersn = b.ordersn
    AND c.pjid = b.rgid
    LEFT OUTER JOIN (
       SELECT
         a.orderno,
         a.ordersn,
         CASE
       WHEN isnull(a.jgs, '') <> '工厂'
       AND isnull(a.jgs, '') <> '' THEN
         1
       ELSE
         0
       END cbtype,
       sum(
         CASE
         WHEN isnull(a.price_jgs, 0) = 0
         AND isnull(b.price_bom, 0) = 0 THEN
           1
         ELSE
           0
         END
      ) ycyy,
       sum(
         CASE
         WHEN isnull(a.price_jgs, 0) = 0 THEN
           b.price_bom
         ELSE
           a.price_jgs
         END * a.num / CASE
         WHEN isnull(c.num, 0) = 0 THEN
           NULL
         ELSE
           c.num
         END
      ) xsrg
    FROM
       orderslxq a
    INNER JOIN product p ON a.slid = p.id
    AND p.productno NOT LIKE 'P%'
    LEFT OUTER JOIN gyname b ON a.gyid = b.id
    INNER JOIN ORDER c ON a.orderno = c.orderno
    AND a.ordersn = c.ordersn
    WHERE
       1 = 1
    AND a.orderno LIKE '%S0116050030%'
    AND a.ordersn = 10
    AND isnull(a.gxid, 0) <> 6
    GROUP BY
       a.orderno,
       a.ordersn,
       a.jgs
    ) xsrg2 ON a.orderno = xsrg2.orderno
    AND a.ordersn = xsrg2.ordersn
    AND CASE
    WHEN b.jgs <> '工厂' THEN
       1
    ELSE
       0
    END = xsrg2.cbtype
    WHERE
       1 = 1
    AND d.productno LIKE 'R%'
    AND a.orderno LIKE '%S0116050030%'
    AND a.ordersn = 10
    GROUP BY
       a.orderdate,
       a.orderno,
       a.ordersn,
       xsrg2.ycyy,
       xsrg2.xsrg,
       CASE
    WHEN b.jgs <> '工厂' THEN
       1
    ELSE
       0
    END
      ) rg ON a.orderno = rg.orderno
       AND a.ordersn = rg.ordersn
       AND a.cbtype = rg.cbtype
       LEFT OUTER JOIN (
         SELECT
           a.orderno,
           a.ordersn,
           a.cbtype,
           a.productid,
           max(
             CASE
             WHEN a.ddprice = 0 THEN
              '价格为零'
             ELSE
              ''
             END
          ) cbycyy,
           CASE
         WHEN SUM(a.ddnum) = 0 THEN
           sum(1 * a.ddprice)
         ELSE
           SUM(a.ddnum * a.ddprice) / CASE
         WHEN SUM(a.ddnum) = 0 THEN
           1
         ELSE
           sum(a.ddnum)
         END
         END AS ddcb,
         CASE
       WHEN SUM(a.ddnum) = 0 THEN
         1
       ELSE
         sum(a.ddnum)
       END AS ddnum
       FROM
        (
           SELECT
             a.orderno,
             a.ordersn,
             a.cbtype,
             a.productid,
             a.ddnum,
             a.cureid,
             CASE

    。。。(字数限制,以下省略。。。铜币在燃烧。。。
    ebony0319
        25
    ebony0319  
    OP
       2016-10-05 18:20:04 +08:00 via Android
    @jhdxr 这个是怎么换的哇,厉害。
    akira
        26
    akira  
       2016-10-05 18:52:43 +08:00
    没有可读性的代码一般不敢用
    Smilecc
        27
    Smilecc  
       2016-10-05 19:12:43 +08:00
    @ebony0319 navicat 一个 SQL 客户端 可以连各种数据库 里面有个 SQL 美化功能 点一下 就成这个样子啦
    falcon05
        28
    falcon05  
       2016-10-05 20:14:49 +08:00 via iPhone
    真乃神人也
    ebony0319
        29
    ebony0319  
    OP
       2016-10-05 21:23:39 +08:00
    @jhdxr 781 行。优化后。
    blacklee
        30
    blacklee  
       2016-10-05 21:30:51 +08:00
    活久见

    这个词我几年以前就看过了,一直觉得这么严重的形容词我应该不会拿出来用的,直到今天……
    fhefh
        31
    fhefh  
       2016-10-05 22:02:25 +08:00
    这么复杂的查询语句 怎么写出来的(机器生成的也可以) 求方法~
    ebony0319
        32
    ebony0319  
    OP
       2016-10-05 22:35:21 +08:00 via Android
    @fhefh 有三种方法。都写伪代码。
    第一种是 insert into table select * from table1
    第二张是 insert into a from table a insert into table2 b
    第三张在 sql server 的: select * into table from table2
    第二种用得多一些。上面写这么多是因为有一个 ordersm 和 orderno 可以连起所有的表。然后要什么数据去什么表去取。
    billlee
        33
    billlee  
       2016-10-05 23:23:27 +08:00
    前端传 SQL? 你听说过 SQL 注入吗?
    shenqi
        34
    shenqi  
       2016-10-05 23:39:56 +08:00
    怎么看都是是 orm 自己弄得 sql 啊。我就不信是手写的。
    lwbjing
        35
    lwbjing  
       2016-10-06 04:20:03 +08:00
    为啥前端要干这事情...
    zhouzm
        36
    zhouzm  
       2016-10-06 08:15:10 +08:00
    能写出这么复杂 sql 的人 sql 水平相当可以啊,为什么要伪装成一个前端?
    thinkif
        37
    thinkif  
       2016-10-06 08:56:53 +08:00
    有很多很多人在写代码的时候有很惰性的心理,总想在一个地方把事情处理了,所以就会出现把很复杂的操作扔到 SQL 中。

    写这样的 SQL 不难,特别是很多刚入行的人,经常就会写出这样的,你要是一点一点读的话,会发现它就是把各种判断和运算放进去而已,就像平铺直叙的作文一样。

    这样的结果会给自己挖个很大很大的坑,如果逻辑发生变化了,变动会非常困难。

    而且更可怕的是过于依赖写死的内容,甚至传说中的 magic number ,一旦出现未知的数据很可能就崩了。。。
    ebony0319
        38
    ebony0319  
    OP
       2016-10-06 09:02:16 +08:00
    @thinkif 对。这里面其实语法是没有问题的,但是在一个联结的时候有一个表出现了认为的错误,所以导致整个结果的为空,但是要定位到这个错误非常困难。
    zhangv
        39
    zhangv  
       2016-10-06 11:43:56 +08:00
    谁都可能写出这种 SQL 吧,前端这枪躺的。。
    sutra
        40
    sutra  
       2016-10-06 17:25:40 +08:00
    先用工具格式化一下就好了。
    forestyuan
        41
    forestyuan  
       2016-10-07 14:22:38 +08:00
    前端直接操作数据库,貌似没有后端什么事了。楼主是 DBA ?
    jsjjdzg
        42
    jsjjdzg  
       2017-03-05 01:50:51 +08:00
    这个应该是生成的吧,人真的能写这么长的 SQL 吗?(不是怀疑能力)
    ebony0319
        43
    ebony0319  
    OP
       2017-03-05 09:27:31 +08:00 via Android
    @jsjjdzg 这个确实是人手写的。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1111 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 19:31 · PVG 03:31 · LAX 12:31 · JFK 15:31
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.