SELECT
RotiInvoiceNo,
max(Tfsqtime) as Tfsqtime FROM YQ_TradeFinancingLog ,
YQ_LoginUsers,YQ_ResultOrder WHERE TForderid=RotiInvoiceId group by RotiInvoiceNo这句SQL语句总是对的。现在我想把SQL语改成SELECT CEILING(CONVERT(DECIMAL,(ROW_NUMBER() OVER
(ORDER BY Rotiid ASC) ))/20) AS PageNumber,
RotiInvoiceNo,
max(Tfsqtime) as Tfsqtime FROM YQ_TradeFinancingLog ,
YQ_LoginUsers,YQ_ResultOrder WHERE TForderid=RotiInvoiceId group by RotiInvoiceNo就报错了。Msg 8120, Level 16, State 1, Line 2
Column 'YQ_ResultOrder.Rotiid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.大致情况是因为新增 CEILING(CONVERT(DECIMAL,(ROW_NUMBER() OVER
(ORDER BY Rotiid ASC) ))/20) AS PageNumber这一列的原因所致。请问大家有什么解决办法么???
RotiInvoiceNo,
max(Tfsqtime) as Tfsqtime FROM YQ_TradeFinancingLog ,
YQ_LoginUsers,YQ_ResultOrder WHERE TForderid=RotiInvoiceId group by RotiInvoiceNo这句SQL语句总是对的。现在我想把SQL语改成SELECT CEILING(CONVERT(DECIMAL,(ROW_NUMBER() OVER
(ORDER BY Rotiid ASC) ))/20) AS PageNumber,
RotiInvoiceNo,
max(Tfsqtime) as Tfsqtime FROM YQ_TradeFinancingLog ,
YQ_LoginUsers,YQ_ResultOrder WHERE TForderid=RotiInvoiceId group by RotiInvoiceNo就报错了。Msg 8120, Level 16, State 1, Line 2
Column 'YQ_ResultOrder.Rotiid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.大致情况是因为新增 CEILING(CONVERT(DECIMAL,(ROW_NUMBER() OVER
(ORDER BY Rotiid ASC) ))/20) AS PageNumber这一列的原因所致。请问大家有什么解决办法么???
from(
select *,rid=row_number() over (order by ...)
from ...
)t
RotiInvoiceNo,
max(Tfsqtime) as Tfsqtime FROM YQ_TradeFinancingLog ,
YQ_LoginUsers,YQ_ResultOrder WHERE TForderid=RotiInvoiceId
group by RotiInvoiceNo
你这里想按照这个字段分组,又想按照Rotiid 这个字段排序,当然会报错了,分组过后只能按照跟聚合函数或者跟分组字段来排序
FROM YQ_TradeFinancingLog ,YQ_LoginUsers,YQ_ResultOrder
WHERE TForderid=RotiInvoiceId group by RotiInvoiceNo
三个表,一个连接条件?CEILING(CONVERT(DECIMAL,(ROW_NUMBER() OVER
(ORDER BY Rotiid ASC) ))/20) AS PageNumber-->(ROW_NUMBER() OVER (ORDER BY Rotiid ASC) - 1) / 20 AS PageNumber对RotiInvoiceNo分组,排序用的是Rotiid什么乱七八糟的?