select a.*, b.zje from(SELECT tableinfo.TableNo,tableinfo.TableName TableName,
tableinfo.TableType,tableinfo.state,tableinfo.orderstate,
isnull(sum(opentable.qty),0) qty,tableinfo.TableName nameonly
FROM tableinfo
LEFT OUTER JOIN opentable ON tableinfo.TableNo = opentable.TableNo
and opentable.Issettle='N'
WHERE tableinfo.state<>'禁用'
Group BY
tableinfo.TableNo,tableinfo.TableName,tableinfo.TableType,
tableinfo.state,tableinfo.orderstate
Order BY
right(space(10)+tableinfo.tableno,10)) a,(SELECT tableinfo.TableNo, UniteTable.Groupno,
sum( isnull(foodmoney,0)+isnull(methodprice,0) ) zje
FROM tableinfo
LEFT OUTER JOIN ChoiceMenu ON ChoiceMenu.TableNo = tableinfo.TableNo
AND
( ChoiceMenu.IsSettle = 'N' ) AND
( ChoiceMenu.IsGiven = 'N' ) AND (choiceMenu.qty -
choicemenu.delqty)>0
LEFT OUTER JOIN UniteTable ON tableinfo.TableNo = UniteTable.Tableno
WHERE ( tableinfo.state = '占用' )
GROUP BY UniteTable.Groupno, tableinfo.TableNo) bwhere a.TableNO = b.TableNO
tableinfo.TableType,tableinfo.state,tableinfo.orderstate,
isnull(sum(opentable.qty),0) qty,tableinfo.TableName nameonly
FROM tableinfo
LEFT OUTER JOIN opentable ON tableinfo.TableNo = opentable.TableNo
and opentable.Issettle='N'
WHERE tableinfo.state<>'禁用'
Group BY
tableinfo.TableNo,tableinfo.TableName,tableinfo.TableType,
tableinfo.state,tableinfo.orderstate
Order BY
right(space(10)+tableinfo.tableno,10)) a,(SELECT tableinfo.TableNo, UniteTable.Groupno,
sum( isnull(foodmoney,0)+isnull(methodprice,0) ) zje
FROM tableinfo
LEFT OUTER JOIN ChoiceMenu ON ChoiceMenu.TableNo = tableinfo.TableNo
AND
( ChoiceMenu.IsSettle = 'N' ) AND
( ChoiceMenu.IsGiven = 'N' ) AND (choiceMenu.qty -
choicemenu.delqty)>0
LEFT OUTER JOIN UniteTable ON tableinfo.TableNo = UniteTable.Tableno
WHERE ( tableinfo.state = '占用' )
GROUP BY UniteTable.Groupno, tableinfo.TableNo) bwhere a.TableNO = b.TableNO
除非同时指定了 TOP,否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。
服务器: 消息 170,级别 15,状态 1,行 42
第 42 行: 'b' 附近有语法错误。
(SELECT tableinfo.TableNo,tableinfo.TableName TableName,
tableinfo.TableType,tableinfo.state,tableinfo.orderstate,
isnull(sum(opentable.qty),0) qty,tableinfo.TableName nameonly
FROM tableinfo
LEFT OUTER JOIN opentable ON tableinfo.TableNo = opentable.TableNo
and opentable.Issettle='N'
WHERE tableinfo.state<>'禁用'
Group BY
tableinfo.TableNo,tableinfo.TableName,tableinfo.TableType,
tableinfo.state,tableinfo.orderstate)a
left join
(SELECT tableinfo.TableNo, UniteTable.Groupno,
sum( isnull(foodmoney,0)+isnull(methodprice,0) ) zje
FROM tableinfo
LEFT OUTER JOIN ChoiceMenu ON ChoiceMenu.TableNo = tableinfo.TableNo
AND
( ChoiceMenu.IsSettle = 'N' ) AND
( ChoiceMenu.IsGiven = 'N' ) AND (choiceMenu.qty -
choicemenu.delqty)>0
LEFT OUTER JOIN UniteTable ON tableinfo.TableNo = UniteTable.Tableno
WHERE ( tableinfo.state = '占用' )
GROUP BY UniteTable.Groupno, tableinfo.TableNo)b
on a.TableNo=b.TableNo
Order BY Right(space(10)+a.TableNo,10)