--V_CDStatistic SELECT B.GoodsCount, B.UnitName, B.GoodsName, B.GoodsCode, B.GoodsModel, F.SortName, C.DepartName, D.DepotCode, D.DepotName, G.CollName, B.GoodsState, CONVERT(decimal, B.GoodsCount) * CAST(B.GoodsState AS decimal) AS GoodsSum, A.DrawTime, A.DepartCode, A.DrawAttribute, E.SortCode FROM dbo.TCKSort AS F INNER JOIN dbo.TCKGoods AS E ON F.SortCode = E.SortCode INNER JOIN dbo.TCKDrawMain AS A INNER JOIN dbo.TCKCommonDrawDetial AS B ON A.DrawCode = B.DrawCode INNER JOIN dbo.tXtDepart AS C ON A.DepartCode = C.DepartCode INNER JOIN dbo.TCKDepot AS D ON B.DepotCode = D.DepotCode ON E.GoodsCode = B.GoodsCode INNER JOIN dbo.TCKColliery AS G ON D.DepotColliery = G.CollCode WHERE (A.DrawClass = '1')
Goodstate存在null值,会影响xml结果。 看下面的小例子: if object_id('[N]') is not null drop table [N] go create table [N]([P] int,[GoodsState] int) insert [N] select 1,3 union all select 1,6 union all select 2,5 union all select 3,null union all select 4,null union all select 5,2 goselect GoodsState from [N] for xml auto /* <N GoodsState="3"/><N GoodsState="6"/><N GoodsState="5"/><N/><N/><N GoodsState="2"/> */select STUFF(REPLACE(REPLACE( (select GoodsState from [N] where P>2 for xml auto ), '<N GoodsState="','和'), '"/>', ''), 1, 1, '') /* N/><N/>和2 */
加个条件,过滤掉null值 SELECT GoodsState FROM V_CDStatistic N WHERE GoodsName = A.GoodsName and DepartName =A.DepartName FOR XML AUTO---->SELECT GoodsState FROM V_CDStatistic N WHERE GoodsName = A.GoodsName and DepartName =A.DepartName and GoodsState is not null FOR XML AUTO
--V_CDStatistic SELECT B.GoodsCount, B.UnitName, B.GoodsName, B.GoodsCode, B.GoodsModel, F.SortName, C.DepartName, D.DepotCode, D.DepotName, G.CollName,
B.GoodsState, CONVERT(decimal, B.GoodsCount) * CAST(B.GoodsState AS decimal) AS GoodsSum, A.DrawTime, A.DepartCode, A.DrawAttribute,
E.SortCode
FROM dbo.TCKSort AS F INNER JOIN
dbo.TCKGoods AS E ON F.SortCode = E.SortCode INNER JOIN
dbo.TCKDrawMain AS A INNER JOIN
dbo.TCKCommonDrawDetial AS B ON A.DrawCode = B.DrawCode INNER JOIN
dbo.tXtDepart AS C ON A.DepartCode = C.DepartCode INNER JOIN
dbo.TCKDepot AS D ON B.DepotCode = D.DepotCode ON E.GoodsCode = B.GoodsCode INNER JOIN
dbo.TCKColliery AS G ON D.DepotColliery = G.CollCode
WHERE (A.DrawClass = '1')
1.00 张 石粉机筛子 CKP00098 53A 设备类 机修班 CKD002 2号仓库 富泓煤矿 NULL NULL 2009-01-12 14:49:24 D0003 CKF013
2.00 件 锣丝 CKP00079 16MM 五金类 采购部 CKD002 2号仓库 富泓煤矿 NULL NULL 2009-03-05 15:02:57 D0006 CKF015
2.00 件 工作服 CKP00001 175 劳保用品 矿办 CKD001 1号仓库 富泓煤矿 NULL NULL 2009-03-06 15:12:47 D0005 CKF001
2.00 件 工作服 CKP00001 175 劳保用品 采掘二班{李海文} CKD001 1号仓库 富泓煤矿 NULL NULL 2009-03-06 15:51:42 D0008 CKF001
2.00 瓶 乙炔 CKP00171 1 设备类 采购部 CKD003 3号仓库 富泓煤矿 NULL NULL 2009-04-07 09:42:08 D0006 CKF013
1.00 瓶 乙炔 CKP00171 1 设备类 采购部 CKD003 3号仓库 富泓煤矿 NULL NULL 2009-04-07 09:45:11 D0006 CKF013
2.00 扇 钢质门 CKP00178 2 设备类 采购部 CKD003 3号仓库 富泓煤矿 NULL NULL 2009-04-07 09:45:11 D0006 CKF013
2.00 瓶 乙炔 CKP00171 1 设备类 机电班 CKD003 3号仓库 富泓煤矿 6.00 12 2009-05-12 17:33:46 D0004 CKF013
1.00 瓶 乙炔 CKP00171 1 设备类 采购部 CKD003 3号仓库 富泓煤矿 11.00 11 2009-05-13 09:49:09 D0006 CKF013
1.00 瓶 乙炔 CKP00171 1 设备类 机电班 CKD003 3号仓库 富泓煤矿 11.00 11 2009-05-13 09:46:46 D0004 CKF013
3.00 包 升柱器修理包 CKP00172 5吨 设备类 机电班 CKD003 3号仓库 富泓煤矿 22.00 66 2009-05-13 09:46:46 D0004 CKF013
看下面的小例子:
if object_id('[N]') is not null drop table [N]
go
create table [N]([P] int,[GoodsState] int)
insert [N]
select 1,3 union all
select 1,6 union all
select 2,5 union all
select 3,null union all
select 4,null union all
select 5,2
goselect GoodsState from [N] for xml auto
/*
<N GoodsState="3"/><N GoodsState="6"/><N GoodsState="5"/><N/><N/><N GoodsState="2"/>
*/select STUFF(REPLACE(REPLACE(
(select GoodsState from [N] where P>2 for xml auto
), '<N GoodsState="','和'), '"/>', ''), 1, 1, '')
/*
N/><N/>和2
*/
SELECT GoodsState FROM V_CDStatistic N
WHERE GoodsName = A.GoodsName and DepartName =A.DepartName
FOR XML AUTO---->SELECT GoodsState FROM V_CDStatistic N
WHERE GoodsName = A.GoodsName and DepartName =A.DepartName and GoodsState is not null
FOR XML AUTO