with aa as (select ps.P_NO, ps.S_NO, p.P_NAME,ps.PS_QTY from Part_s ps
inner join Part p on ps.P_NO=p.P_NO where 1=1 and charindex(ps.S_NO,'001000')>0 and ps.P_NO>='0000000000000' and ps.P_NO<='ZZZZZZZZZZZZZ' )
select a.*,s.TOT from (select P_NO, P_NAME,[001000] from aa pivot (sum(PS_QTY) for S_NO in ([001000]))p) a
left join (select P_NO,sum(PS_QTY) TOT from aa group by P_NO) s on s.P_NO = a.P_NO where 1=1 and ([001000]!=0)order by a.P_NO
WITH aa
AS ( SELECT ps.P_NO ,
ps.S_NO ,
p.P_NAME ,
ps.PS_QTY
FROM Part_s ps
INNER JOIN Part p ON ps.P_NO = p.P_NO
WHERE 1 = 1
AND CHARINDEX(ps.S_NO, '001000') > 0
AND ps.P_NO >= '0000000000000'
AND ps.P_NO <= 'ZZZZZZZZZZZZZ'
)
SELECT a.* ,
s.TOT
FROM ( SELECT P_NO ,
P_NAME ,
[001000]
FROM aa PIVOT ( SUM(PS_QTY) FOR S_NO IN ( [001000] ) ) p
) a
LEFT JOIN ( SELECT P_NO ,
SUM(PS_QTY) TOT
FROM aa
GROUP BY P_NO
) s ON s.P_NO = a.P_NO
WHERE 1 = 1
AND ( [001000] != 0 )ORDER BY a.P_NO这个要根据具体业务分析
语法上是一个CTE查询
with aa as (select ps.P_NO, ps.S_NO, p.P_NAME,ps.PS_QTY from Part_s ps
inner join Part p on ps.P_NO=p.P_NO where 1=1 and charindex(ps.S_NO,'001000')>0 and ps.P_NO>='0000000000000' and ps.P_NO<='ZZZZZZZZZZZZZ' )
---在aa中查询生成一个自连接查询,加入where条件限制。
select a.*,s.TOT from (select P_NO, P_NAME,[001000] from aa pivot (sum(PS_QTY) for S_NO in ([001000]))p) a
left join (select P_NO,sum(PS_QTY) TOT from aa group by P_NO) s on s.P_NO = a.P_NO where 1=1 and ([001000]!=0)order by a.P_NO
with aa
as (select ps.P_NO, ps.S_NO, p.P_NAME,ps.PS_QTY from Part_s ps
inner join Part p
on ps.P_NO=p.P_NO
where 1=1 and charindex(ps.S_NO,'001000')>0 and ps.P_NO>='0000000000000' and ps.P_NO<='ZZZZZZZZZZZZZ' )
---在aa中查询生成一个自连接查询,加入where条件限制。
select a.*,s.TOT from
(select P_NO, P_NAME,[001000] from aa pivot (sum(PS_QTY) for S_NO in ([001000]))p) a
left join
(select P_NO,sum(PS_QTY) TOT from aa group by P_NO) s
on s.P_NO = a.P_NO
where 1=1 and ([001000]!=0)order by a.P_NO
a.*,s.TOT
from
(select P_NO, P_NAME,[001000] from aa pivot (sum(PS_QTY) for S_NO in ([001000]))p) a---这个子查询是行转列
left join
(select P_NO,sum(PS_QTY) TOT from aa group by P_NO) s --分组求和
on s.P_NO = a.P_NO --连接条件
where
1=1
and
([001000]!=0)--1=1是为了在程序中能构建
order by
a.P_NO---排序