一、物料辅助属性、辅助属性表头资料

--物料辅助属性T_BD_MATERIALAUXPTY

SELECT

A.FAUXPROPERTYID,--辅助属性内码，关联辅助属性基础资料T_BD_FLEXAUXPROPERTY.FAUXPROPERTYID

*

FROM

T_BD_MATERIALAUXPTY A

WHERE

A.FMATERIALID IN (SELECT FMATERIALID FROM T_BD_MATERIAL WHERE FNUMBER = '')

--辅助属性基础资料表T_BD_FLEXAUXPROPERTY

SELECT

A.FVALUESOURCE,--辅助属性值来源

A.FFLEXNUMBER,--辅助属性维度数据对应内码

*

FROM

T_BD_FLEXAUXPROPERTY A

二、在金蝶云星空中，辅助属性是通过弹性域实现的。辅助属性内码，记录的是一个辅助属性的组合值，故查询辅助属性时，需要先通过内码去查组合值，再通过组合值去查询具体的辅助属性信息。

以下为表间关联关系和查询示例：

-- 即时库存

SELECT * FROM T_STK_INVENTORY WHERE FAUXPROPID = 100007

-- 弹性表组合值

SELECT * FROM T_BD_FLEXSITEMDETAILV WHERE FID = 100007

-- 辅助属性表头

SELECT FFLEXNUMBER,* FROM T_BD_FLEXAUXPROPERTY

-- 辅助资料查看具体值

SELECT

A.FID,

A.FNUMBER AS DATA_FNUMBER,

B.FNAME,

C.FNUMBER AS ENTRY_FNUMBER,

C.FENTRYID,

D.FDESCRIPTION

--, *

FROM

T_BAS_ASSISTANTDATA A

INNER JOIN T_BAS_ASSISTANTDATA_L B ON A.FID = B.FID AND B.FLOCALEID= 2052

INNER JOIN T_BAS_ASSISTANTDATAENTRY C ON A.FID = C.FID

INNER JOIN T_BAS_ASSISTANTDATAENTRY_L D ON C.FENTRYID = D.FENTRYID AND D.FLOCALEID = 2052

WHERE

A.FID = '5a797a7d21619f' OR A.FID = '774920fec7964bfba702b45eeb07af61'

三、以下为参考使用的查询辅助属性值的脚本（适用于SQLSERVER）

--查询辅助属性，来源值为辅助资料

DECLARE @MAX_WHILE INT ,

@WHILE_NUM INT,

@FLEXNUMBER NVARCHAR(100),

@FENTRYID NVARCHAR(100),

@FAUXPROPID NVARCHAR(100),

@VALUESOURCE NVARCHAR(100),

@RE NVARCHAR(1000),

@SQL1 NVARCHAR(MAX),

@SQL2 NVARCHAR(MAX)

SET @FAUXPROPID = '100007'--指定辅助属性内码

SELECT @MAX_WHILE = MAX(SEQ) FROM

(SELECT C.NAME,ROW_NUMBER()OVER(ORDER BY C.NAME) SEQ FROM SYSCOLUMNS C,SYSOBJECTS T WHERE C.ID = T.ID AND T.TYPE='U'AND T.NAME='T_BD_FLEXSITEMDETAILV'

AND C.NAME <> 'FID' AND C.NAME <> 'FOPCODE') T2

SET @WHILE_NUM = 1

SET @SQL2 = '' --指定接收拼接结果

WHILE @WHILE_NUM < @MAX_WHILE + 1

BEGIN

SELECT @FLEXNUMBER = NAME FROM

(SELECT C.NAME,ROW_NUMBER()OVER(ORDER BY C.NAME) SEQ FROM SYSCOLUMNS C,SYSOBJECTS T WHERE C.ID = T.ID AND T.TYPE='U'AND T.NAME='T_BD_FLEXSITEMDETAILV'

AND C.NAME <> 'FID' AND C.NAME <> 'FOPCODE') T2 WHERE SEQ = @WHILE_NUM

SET @RE = '' --清空接收变量@RE

SET @WHILE_NUM = @WHILE_NUM + 1

SELECT @SQL1 = 'SELECT @LS= '+ @FLEXNUMBER + ' FROM T_BD_FLEXSITEMDETAILV WHERE FID = '+ @FAUXPROPID

EXEC SP_EXECUTESQL @SQL1,N'@LS NVARCHAR(100) OUTPUT',@FENTRYID OUTPUT

SELECT @VALUESOURCE = FVALUESOURCE FROM T_BD_FLEXAUXPROPERTY WHERE FFLEXNUMBER = @FLEXNUMBER

SELECT @RE = ( DATA_FNUMBER + FNAME + ENTRY_FNUMBER + '('+FDESCRIPTION+')')

FROM (SELECT

A.FID,

A.FNUMBER AS DATA_FNUMBER,

B.FNAME,

C.FNUMBER AS ENTRY_FNUMBER,

C.FENTRYID,

D.FDESCRIPTION

--, *

FROM

T_BAS_ASSISTANTDATA A

INNER JOIN T_BAS_ASSISTANTDATA_L B ON A.FID = B.FID AND B.FLOCALEID= 2052

INNER JOIN T_BAS_ASSISTANTDATAENTRY C ON A.FID = C.FID

INNER JOIN T_BAS_ASSISTANTDATAENTRY_L D ON C.FENTRYID = D.FENTRYID AND D.FLOCALEID = 2052) T1

WHERE FID = @VALUESOURCE AND FENTRYID =@FENTRYID

SET @SQL2 = @SQL2 + @RE

END

PRINT @SQL2

辅助属性后台表结构

恭喜您！