本文介绍了一个使用T-SQL动态构造复杂查询的过程。该查询通过连接BS_GoodsInfo和BS_dSaleBill表，按商品ID和销售单号分组，获取每个商品的销售数量汇总。动态SQL允许根据运行时变量调整查询条件。
declare
@sql
varchar
(
8000
)
set
@sql
=
'
select d.sBillNo,g.goodsId
'
;
SELECT
@sql
=
@sql
+
'
,
'''
+
g.GoodName
+
'''
=isnull(sum(case g.GoodName when
'''
+
g.goodName
+
'''
then d.Qty end),0)
'
from
(
SELECT
distinct
g.goodsId, g.GoodName
FROM
BS_GoodsInfo g
inner
join
BS_dSaleBill d
on
g.CompanyId
=
d.CompanyId
and
g.GoodsId
=
d.GoodsId
and
d.CompanyId
=
'
002
'
--
WHERE ID >20 AND ID < 100
)
as
g
execute
(
@sql
+
'
FROM BS_dSaleBill d inner join BS_GoodsInfo g on g.CompanyId = d.CompanyId and g.GoodsId = d.GoodsId group by g.goodsId,d.sBillNo
'
)
扫一扫
5987
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
