|
0
|
土肥圆发布于2015-6-2 16:10（编辑于 2023-9-6 09:34）
|
|
555
|
-
hualiwenyu(提问者)
SELECT
T.FID,
STOR.FNAME_L2 \'11\',
M.FBarCode \'12\',
M.FNAME_L2 \'13\',
M.FMODEL \'14\',
M.`CFPRICERANGE` \'15\',
T.FCURSTOREQTY \'16\',
IF(T.FLOCKQTY>0,1,0) \'17\',
T.FLOCKBASEQTY \'18\',
T.FSTORESTATUSID \'19\',
T.FSTORETYPEID \'20\',
T.FLOCATIONID \'21\',
T.FCREATETIME
FROM
(SELECT
MAX(T.FID) FID,
MAX(T.FCOMPANYORGUNITID) FCOMPANYORGUNITID,
T.FSTORAGEORGUNITID,
MAX(T.FWAREHOUSEID) FWAREHOUSEID,
MAX(T.FLOCATIONID) FLOCATIONID,
MAX(T.FSTORETYPEID) FSTORETYPEID,
MAX(T.FSTORESTATUSID) FSTORESTATUSID,
MAX(T.FSUPPLIERID) FSUPPLIERID,
MAX(T.FCUSTOMERID) FCUSTOMERID,
T.FMATERIALID,
MAX(T.FUNITID) FUNITID,
SUM(IFNULL(T.FCURSTOREQTY, 0)) FCURSTOREQTY,
MAX(T.FASSISTUNITID) FASSISTUNITID,
MAX(T.FCURSTOREASSISTQTY) FCURSTOREASSISTQTY,
SUM(IFNULL(T.FBASEQTY, 0)) FBASEQTY,
MAX(T.FAMOUNT) FAMOUNT,
MAX(T.FCONTROLUNITID) FCONTROLUNITID,
MAX(T.FBASEUNITID) FBASEUNITID,
MAX(T.FEXP) FEXP,
MAX(T.FASSISTPROPERTYID) FASSISTPROPERTYID,
MAX(T.FLOCKQTY) FLOCKQTY,
MAX(T.FLOCKBASEQTY) FLOCKBASEQTY,
MAX(T.FLOCKASSISTQTY) FLOCKASSISTQTY,
MAX(T.FLASTUPDATETIME) FLASTUPDATETIME,
MAX(T.FCREATORID) FCREATORID,
MAX(T.FLASTUPDATEUSERID) FLASTUPDATEUSERID,
MAX(T.FCREATETIME) FCREATETIME,
MAX(T.FUNIQUEKEY) FUNIQUEKEY,
MAX(T.FPROJECTID) FPROJECTID,
MAX(T.FTRACKNUMBERID) FTRACKNUMBERID,
SUM(IFNULL(T.posQty, 0)) posQty
FROM
(SELECT
MAX(T.FID) FID,
MAX(T.FCOMPANYORGUNITID) FCOMPANYORGUNITID,
MAX(T.FWAREHOUSEID) FWAREHOUSEID,
MAX(T.FLOCATIONID) FLOCATIONID,
MAX(T.FSTORETYPEID) FSTORETYPEID,
MAX(T.FSTORESTATUSID) FSTORESTATUSID,
MAX(T.FSUPPLIERID) FSUPPLIERID,
MAX(T.FCUSTOMERID) FCUSTOMERID,
MAX(T.FUNITID) FUNITID,
SUM(T.FCURSTOREQTY) FCURSTOREQTY,
MAX(T.FASSISTUNITID) FASSISTUNITID,
MAX(T.FCURSTOREASSISTQTY) FCURSTOREASSISTQTY,
SUM(T.FBASEQTY) FBASEQTY,
MAX(T.FAMOUNT) FAMOUNT,
MAX(T.FCONTROLUNITID) FCONTROLUNITID,
MAX(T.FBASEUNITID) FBASEUNITID,
MAX(T.FEXP) FEXP,
MAX(T.FASSISTPROPERTYID) FASSISTPROPERTYID,
MAX(T.FLOCKQTY) FLOCKQTY,
MAX(T.FLOCKBASEQTY) FLOCKBASEQTY,
MAX(T.FLOCKASSISTQTY) FLOCKASSISTQTY,
MAX(T.FLASTUPDATETIME) FLASTUPDATETIME,
MAX(T.FCREATORID) FCREATORID,
MAX(T.FLASTUPDATEUSERID) FLASTUPDATEUSERID,
MAX(T.FCREATETIME) FCREATETIME,
MAX(T.FUNIQUEKEY) FUNIQUEKEY,
MAX(T.FPROJECTID) FPROJECTID,
MAX(T.FTRACKNUMBERID) FTRACKNUMBERID,
T.FMaterialID,
T.FSTORAGEORGUNITID,
NULL posQty
FROM
t_im_inventory T
GROUP BY T.FMaterialID,
T.FSTORAGEORGUNITID
UNION
SELECT
invpos.FID,
invpos.FCOMPANYORGUNITID,
invpos.FWAREHOUSEID,
invpos.FLOCATIONID,
invpos.FSTORETYPEID,
invpos.FSTORESTATUSID,
invpos.FSUPPLIERID,
invpos.FCUSTOMERID,
invpos.FUNITID,
NULL FCURSTOREQTY,
invpos.FASSISTUNITID,
invpos.FCURSTOREASSISTQTY,
NULL FBASEQTY,
invpos.FAMOUNT,
invpos.FCONTROLUNITID,
invpos.FBASEUNITID,
invpos.FEXP,
invpos.FASSISTPROPERTYID,
invpos.FLOCKQTY,
invpos.FLOCKBASEQTY,
invpos.FLOCKASSISTQTY,
invpos.FLASTUPDATETIME,
invpos.FCREATORID,
invpos.FLASTUPDATEUSERID,
invpos.FCREATETIME,
invpos.FUNIQUEKEY,
invpos.FPROJECTID,
invpos.FTRACKNUMBERID,
invpos.FMaterialID,
invpos.FSTORAGEORGUNITID,
invpos.FBASEQTY posQty
FROM
t_im_inventory_pos invpos) T
GROUP BY T.FMaterialID,
T.FSTORAGEORGUNITID) T
INNER JOIN t_bd_material M
ON T.FMaterialID = M.FID
INNER JOIN t_org_storage STOR
ON T.FSTORAGEORGUNITID = STOR.FID
WHERE (
M.FNUMBER LIKE \'%%\'
)
AND STOR.fid like \'%${库存组织}%\'
AND M.CFPRICERANGE like \'%${售价}%\'
AND M.FNAME_L2 like \'%${商品名称}%\'
AND M.FMODEL like \'%${规格型号}%\'
AND M.FBarCode like \'%${商品编码}%\'
ORDER BY FCREATETIME DESC ;
商品名称这个控件，如果我输入一个英文字符就能查找的出来数据，而中文则不行！
|
|
