SELECT
PS_ID = P.PS_ID
,P.PRD_NO AS PRD_NO
,P.PRD_NAME AS PRD_NAME
,QTY =
CASE
WHEN P.PS_ID ='B' THEN ISNULL(P.QTY,0)*-1
WHEN P.PS_ID ='C' THEN ISNULL(P.QTY,0)
END
,ISNULL(P.UP,0) AS UP
,AMT =
CASE
WHEN P.PS_ID ='B' THEN ISNULL(P.AMT,0)*-1
WHEN P.PS_ID ='C' THEN ISNULL(P.AMT,0)
END
,UP_BZ = ISNULL((SELECT TOP 1 ISNULL(UP,0)
FROM TABLE1
WHERE PRD_NO = P.PRD_NO AND PS_ID IN ('B','C')
AND ISNULL(UP,0)<>0 AND (PS_DD < @PS_DD_B)
AND ISNULL(QTY,0)<>0
ORDER BY PS_DD DESC),0)
,AMT_BZ =
CASE
WHEN P.PS_ID ='B' THEN ISNULL((SELECT TOP 1 ISNULL(UP,0)
FROM TABLE1
WHERE PRD_NO = P.PRD_NO AND PS_ID IN ('B','C')
AND ISNULL(UP,0)<>0 AND (PS_DD < @PS_DD_B)
AND ISNULL(QTY,0)<>0
ORDER BY PS_DD DESC),0)*ISNULL(P.QTY,0)*-1
WHEN P.PS_ID ='C' THEN ISNULL((SELECT TOP 1 ISNULL(UP,0)
FROM TABLE1
WHERE PRD_NO = P.PRD_NO AND PS_ID IN ('B','C')
AND ISNULL(UP,0)<>0 AND (PS_DD < @PS_DD_B)
AND ISNULL(QTY,0)<>0
ORDER BY PS_DD DESC),0)*ISNULL(P.QTY,0)
END
FROM TABLE1 P
WHERE (P.PS_DD >= @PS_DD_B) AND (P.PS_DD <= @PS_DD_E)
AND P.PS_ID IN ('B','C') AND ISNULL(P.UP,0) <>0 AND ISNULL(P.QTY,0)<>0
PS_ID = P.PS_ID
,P.PRD_NO AS PRD_NO
,P.PRD_NAME AS PRD_NAME
,QTY =
CASE
WHEN P.PS_ID ='B' THEN ISNULL(P.QTY,0)*-1
WHEN P.PS_ID ='C' THEN ISNULL(P.QTY,0)
END
,ISNULL(P.UP,0) AS UP
,AMT =
CASE
WHEN P.PS_ID ='B' THEN ISNULL(P.AMT,0)*-1
WHEN P.PS_ID ='C' THEN ISNULL(P.AMT,0)
END
,UP_BZ = ISNULL((SELECT TOP 1 ISNULL(UP,0)
FROM TABLE1
WHERE PRD_NO = P.PRD_NO AND PS_ID IN ('B','C')
AND ISNULL(UP,0)<>0 AND (PS_DD < @PS_DD_B)
AND ISNULL(QTY,0)<>0
ORDER BY PS_DD DESC),0)
,AMT_BZ =
CASE
WHEN P.PS_ID ='B' THEN ISNULL((SELECT TOP 1 ISNULL(UP,0)
FROM TABLE1
WHERE PRD_NO = P.PRD_NO AND PS_ID IN ('B','C')
AND ISNULL(UP,0)<>0 AND (PS_DD < @PS_DD_B)
AND ISNULL(QTY,0)<>0
ORDER BY PS_DD DESC),0)*ISNULL(P.QTY,0)*-1
WHEN P.PS_ID ='C' THEN ISNULL((SELECT TOP 1 ISNULL(UP,0)
FROM TABLE1
WHERE PRD_NO = P.PRD_NO AND PS_ID IN ('B','C')
AND ISNULL(UP,0)<>0 AND (PS_DD < @PS_DD_B)
AND ISNULL(QTY,0)<>0
ORDER BY PS_DD DESC),0)*ISNULL(P.QTY,0)
END
FROM TABLE1 P
WHERE (P.PS_DD >= @PS_DD_B) AND (P.PS_DD <= @PS_DD_E)
AND P.PS_ID IN ('B','C') AND ISNULL(P.UP,0) <>0 AND ISNULL(P.QTY,0)<>0
SELECT
PS_ID = P.PS_ID
,P.PRD_NO AS PRD_NO
,P.PRD_NAME AS PRD_NAME
,QTY =
CASE
WHEN P.PS_ID ='B' THEN ISNULL(P.QTY,0)*-1
WHEN P.PS_ID ='C' THEN ISNULL(P.QTY,0)
END
,ISNULL(P.UP,0) AS UP
,AMT =
CASE
WHEN P.PS_ID ='B' THEN ISNULL(P.AMT,0)*-1
WHEN P.PS_ID ='C' THEN ISNULL(P.AMT,0)
END
,UP_BZ = ISNULL((SELECT TOP 1 ISNULL(UP,0)
FROM TABLE1
WHERE PRD_NO = P.PRD_NO AND PS_ID IN ('B','C')
AND ISNULL(UP,0)<>0 AND (PS_DD < @PS_DD_B)
AND ISNULL(QTY,0)<>0
ORDER BY PS_DD DESC),0)
,AMT_BZ =
CASE
WHEN P.PS_ID ='B' THEN ISNULL((SELECT TOP 1 ISNULL(UP,0)
FROM TABLE1
WHERE PRD_NO = P.PRD_NO AND PS_ID IN ('B','C')
AND ISNULL(UP,0)<>0 AND (PS_DD < @PS_DD_B)
AND ISNULL(QTY,0)<>0
ORDER BY PS_DD DESC),0)*ISNULL(P.QTY,0)*-1
WHEN P.PS_ID ='C' THEN ISNULL((SELECT TOP 1 ISNULL(UP,0)
FROM TABLE1
WHERE PRD_NO = P.PRD_NO AND PS_ID IN ('B','C')
AND ISNULL(UP,0)<>0 AND (PS_DD < @PS_DD_B)
AND ISNULL(QTY,0)<>0
ORDER BY PS_DD DESC),0)*ISNULL(P.QTY,0)
END
FROM TABLE1 P
WHERE (P.PS_DD >= @PS_DD_B) AND (P.PS_DD <= @PS_DD_E)
AND P.PS_ID IN ('B','C') AND ISNULL(P.UP,0) <>0 AND ISNULL(P.QTY,0)<>0
FROM TABLE1
WHERE PRD_NO = P.PRD_NO AND PS_ID IN ('B','C')
AND ISNULL(UP,0)<>0 AND (PS_DD < @PS_DD_B) AND
ISNULL(QTY,0)<>0
ORDER BY PS_DD DESC),0)
这一段要改一下才行(效率低)
ISNULL(QTY,0)<>0 group by PRD_NO having max(p.PS_DD)=PS_DD),0)
改为这样
ISNULL(UP,0)<>0 可以改为 楼主一下表的显示为没有还是null.
如果为null 改up is not null
如果为没有
up!=''
SELECT TOP 1 ISNULL(UP,0)
FROM TABLE1
WHERE PRD_NO = P.PRD_NO AND PS_ID IN ('B','C')
AND ISNULL(UP,0)<>0 AND (PS_DD < @PS_DD_B)
AND ISNULL(QTY,0)<>0
ORDER BY PS_DD DESC
这个查询你自己看看