SELECT *
FROM ( SELECT row_number() OVER ( ORDER BY p.sjdate DESC ) AS rowid,
( ISNULL(p.total1, 0.00) * ISNULL(pi.hdcost, 0.00) ) AS hzSum,
p.autoid, p.risk, p.planRequirementShelvesDate, p.sjdate,
p.designStyle, p.zdno,
( SELECT TOP 1
theme
FROM ripreport_DesignStyleRecords dsr
WHERE dsr.designStyle = p.designStyle
) AS theme, p.productRole, p.isjhsxd, p.orderLevel,
p.orderRemark, p.platformProperty
FROM ripreport_productSchedule p
LEFT JOIN ripreport_ProductInformation pi ON p.styleno = pi.productno
AND pi.flag = 0
LEFT JOIN V_productScheduleRowToCol vpsnetc ON vpsnetc.productScheduleAutoid = p.autoid
WHERE p.flag = 0
AND p.status IN ( '0', '1' )
AND p.autoid > 0
) t
FROM ( SELECT row_number() OVER ( ORDER BY p.sjdate DESC ) AS rowid,
( ISNULL(p.total1, 0.00) * ISNULL(pi.hdcost, 0.00) ) AS hzSum,
p.autoid, p.risk, p.planRequirementShelvesDate, p.sjdate,
p.designStyle, p.zdno,
( SELECT TOP 1
theme
FROM ripreport_DesignStyleRecords dsr
WHERE dsr.designStyle = p.designStyle
) AS theme, p.productRole, p.isjhsxd, p.orderLevel,
p.orderRemark, p.platformProperty
FROM ripreport_productSchedule p
LEFT JOIN ripreport_ProductInformation pi ON p.styleno = pi.productno
AND pi.flag = 0
LEFT JOIN V_productScheduleRowToCol vpsnetc ON vpsnetc.productScheduleAutoid = p.autoid
WHERE p.flag = 0
AND p.status IN ( '0', '1' )
AND p.autoid > 0
) t
WHERE t.rowid BETWEEN 1 AND 100
返回结果rowid是不连续的。
没有子查询的时候
( SELECT TOP 1
theme
FROM ripreport_DesignStyleRecords dsr
WHERE dsr.designStyle = p.designStyle
) AS theme
按照1~100查出。
以上需要加入order by rowid才可顺序输出。