求一高难度的SQL语句,基本信息我想要,但是我还想根据工艺号求和作为判断条件,来执行不同的价格
求一高难度的SQL语句,基本信息我想要,但是我还想根据工艺号求和作为判断条件,来执行不同的价格:
SELECT S.MANUFACTURENO "工艺号",
S.SAMPLENO "样品号",
S.C_SHORT_DE "品名",
M.BM_NUM "单号",
S.ITEMBASIS "成份",
SI.SIZENAME "规格",
SI.SIZEID "规格代码",
SG.SIZEGROUPNAME "尺码组",
G.DESCRIPTION "性别" ,
C.COLOR_NAME "颜色" ,
D.COLOR_CODE "颜色代码",
C.COLORSCHEME "配色",
M.SELL_QTY "数量",
CU.CUST_CNAME "客户",
D.SUGGEST_RETAILPRICE*0.5 "批发价",
D.SUGGEST_RETAILPRICE "零售价",
D.BASE_UNIT_PRICE*M.SELL_QTY "批发金额",
D.SUGGEST_RETAILPRICE*M.SELL_QTY "零售金额",
D.DELIDATE "交期",
CG.CATE_CNAME "种类",
V.VEND_SHORTNAME "供应商",
SB.SUBG_CNAME "子类",
LB.LINE_ENAME "系列",
P.LOCA_NAME "子送货地点",
W.WARE_CNAME "货仓名称"
FROM BM_MTX M
LEFT JOIN BM_DTL D
ON D.LINE_NBR = M.LINE_NBR
AND D.BM_NUM =M.BM_NUM
AND D.SHOP_LINE=M.SHOP_LINE
LEFT JOIN BM_SHOP P
ON D.BM_NUM = P.BM_NUM
AND D.SHOP_LINE = P.SHOP_LINE
LEFT JOIN WAREHOUSE W
ON W.WARE_CODE = P.SHOP_CODE
LEFT OUTER JOIN SAMPLE S
ON D.ITEM_CODE = S.SAMPLENO
LEFT OUTER JOIN SIZEGROUP SG
ON S.SIZEGROUPID = SG.SIZEGROUPID
LEFT OUTER JOIN GENDER G
ON S.GEND_CODE = G.GEND_CODE
LEFT OUTER JOIN SAMPLE_COLORS C
ON D.ITEM_CODE = C.SAMPLENO
AND D.COLOR_CODE = C.COLOR_CODE
LEFT OUTER JOIN VENDOR V
ON D.SUP_CODE=V.VEND_CODE
LEFT JOIN CATEGORY CG
ON CG.CATE_CODE = S.CATE_CODE
LEFT JOIN SUBGROUP SB
ON SB.CATE_CODE = S.CATE_CODE
AND SB.SUBG_CODE = S.SUBG_CODE
LEFT JOIN SUBGROUPDESC SBD
ON SBD.CATE_CODE = S.CATE_CODE
AND SBD.SUBG_CODE = S.SUBG_CODE
AND SBD.LINE_NBR = S.SUBDESC_LINE
LEFT JOIN LINETABLE LB
ON LB.LINE_CODE = S.LINE_CODE
LEFT JOIN SIZERATIO SI
ON SI.SIZEGROUPID=S.SIZEGROUPID
AND SI.SIZEID =M.SIZEID
LEFT JOIN BM_HDR H
ON H.BM_NUM= D.BM_NUM
LEFT JOIN CUSTOMER CU
ON H.CUST_CODE=CU.CUST_CODE GROUP BY S.MANUFACTURENO,S.SAMPLENO HAVING S.MANUFACTURENO LIKE '%8180003%' AND SUM(M.SELL_QTY)>40
我想针对某个工艺号求和比方说达到了40件,执行 D.SUGGEST_RETAILPRICE*0.5 "批发价",
我想针对某个工艺号求和比方说达到了50件,执行 D.SUGGEST_RETAILPRICE*0.4 "批发价",
1、看看大家有什么好方法?我准备在上面的语句间用联合 union 创建视图V1,这样能把满足2种情况的问题都考虑到,真正执行时我通过单号来动态生成结果: WHERE V.单号 like '%danhao1%',
2、另我上面的写法有问题:
选择列表中的列 'SI.SIZENAME' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
但是我如果把 SI.SIZENAME增加放到GROUP BY 后面,那样又不会出现我想要的结果的,因为只针对工艺号,求和,
(一个工艺号有可能对应多个颜色和规格,这个大家也好理解)