1
jydeng 2016-10-05 11:55:04 +08:00
哈哈哈,这 SQL 写的 6
|
2
lemontang 2016-10-05 11:56:21 +08:00 via Android
其实楼主要的是我们的吐槽吧
|
3
geeglo 2016-10-05 11:56:23 +08:00
=.= 看晕了,里面似乎还带 逻辑部分?
|
5
subpo 2016-10-05 12:00:05 +08:00
就问你怎么写逻辑写到比原生 sql 快?
|
6
ebony0319 OP @subpo 传过来就是一堆字符串了。出了一点问题要查出来太难了。我觉得一段一段 update 比这个好。至少能够快速定位哪里出错了。
|
7
Troevil 2016-10-05 12:11:17 +08:00
66666 这个 sql debug 起来不是一般的困难
|
8
viko16 2016-10-05 12:29:28 +08:00
啊,好像在读英文文章
|
9
22too 2016-10-05 12:40:46 +08:00
突然感觉,这个 sql 写了几天吧,然后调试花费了半个月。
|
10
rannnn 2016-10-05 12:46:50 +08:00
真的不是自动生成出来的吗
|
11
tabris17 2016-10-05 12:47:27 +08:00 1
为什么前端要写 SQL ?
|
12
x86 2016-10-05 12:47:58 +08:00 via iPhone
写成这样也是 666
|
13
amon 2016-10-05 12:56:14 +08:00 via iPhone
前端好辛苦,居然还写 SQL ,莫非是传说中的拍黄片?
|
14
aploium 2016-10-05 13:08:12 +08:00
lz 这是半年份的膝盖请收好 (支持殴打写它的那个人)
|
15
loading 2016-10-05 13:18:16 +08:00 via Android
手机滚了半天……
|
16
scnace 2016-10-05 13:32:33 +08:00 via Android
估计 leetcode 上刷 sql 的题刷惯了 hhhh
|
17
Infernalzero 2016-10-05 13:35:38 +08:00
这种 sql 语句一般不是人写出来的,是用框架生成的,前端不懂数据库这块肯定是直接用框架抽象成表达式调用的
|
18
ebony0319 OP @Infernalzero 我倒前端看了。确实是手写的。
|
19
ByZHkc3 2016-10-05 14:26:13 +08:00
前端还要写 SQL ?
|
20
zsx 2016-10-05 16:52:49 +08:00
这条 SQL ……一次要查多久……
|
21
letitbesqzr 2016-10-05 17:04:51 +08:00 via iPhone
@Infernalzero 但事实是的确有很多人会这样写。。。我们公司的老项目 随处可见这样的 sql 。。虽然三天两头的把 Oracle 都得跑挂。
|
22
ddou 2016-10-05 17:38:59 +08:00 via Android
直接 fire 吧
|
23
jarlyyn 2016-10-05 17:42:38 +08:00 via Android
前端写 SQL ,还能 insert ???
|
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 。。。(字数限制,以下省略。。。铜币在燃烧。。。 |
26
akira 2016-10-05 18:52:43 +08:00
没有可读性的代码一般不敢用
|
27
Smilecc 2016-10-05 19:12:43 +08:00
@ebony0319 navicat 一个 SQL 客户端 可以连各种数据库 里面有个 SQL 美化功能 点一下 就成这个样子啦
|
28
falcon05 2016-10-05 20:14:49 +08:00 via iPhone
真乃神人也
|
30
blacklee 2016-10-05 21:30:51 +08:00
活久见
这个词我几年以前就看过了,一直觉得这么严重的形容词我应该不会拿出来用的,直到今天…… |
31
fhefh 2016-10-05 22:02:25 +08:00
这么复杂的查询语句 怎么写出来的(机器生成的也可以) 求方法~
|
32
ebony0319 OP @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 可以连起所有的表。然后要什么数据去什么表去取。 |
33
billlee 2016-10-05 23:23:27 +08:00
前端传 SQL? 你听说过 SQL 注入吗?
|
34
shenqi 2016-10-05 23:39:56 +08:00
怎么看都是是 orm 自己弄得 sql 啊。我就不信是手写的。
|
35
lwbjing 2016-10-06 04:20:03 +08:00
为啥前端要干这事情...
|
36
zhouzm 2016-10-06 08:15:10 +08:00
能写出这么复杂 sql 的人 sql 水平相当可以啊,为什么要伪装成一个前端?
|
37
thinkif 2016-10-06 08:56:53 +08:00
有很多很多人在写代码的时候有很惰性的心理,总想在一个地方把事情处理了,所以就会出现把很复杂的操作扔到 SQL 中。
写这样的 SQL 不难,特别是很多刚入行的人,经常就会写出这样的,你要是一点一点读的话,会发现它就是把各种判断和运算放进去而已,就像平铺直叙的作文一样。 这样的结果会给自己挖个很大很大的坑,如果逻辑发生变化了,变动会非常困难。 而且更可怕的是过于依赖写死的内容,甚至传说中的 magic number ,一旦出现未知的数据很可能就崩了。。。 |
38
ebony0319 OP @thinkif 对。这里面其实语法是没有问题的,但是在一个联结的时候有一个表出现了认为的错误,所以导致整个结果的为空,但是要定位到这个错误非常困难。
|
39
zhangv 2016-10-06 11:43:56 +08:00
谁都可能写出这种 SQL 吧,前端这枪躺的。。
|
40
sutra 2016-10-06 17:25:40 +08:00
先用工具格式化一下就好了。
|
41
forestyuan 2016-10-07 14:22:38 +08:00
前端直接操作数据库,貌似没有后端什么事了。楼主是 DBA ?
|
42
jsjjdzg 2017-03-05 01:50:51 +08:00
这个应该是生成的吧,人真的能写这么长的 SQL 吗?(不是怀疑能力)
|