高手帮忙看看这个条语句要如何改

高手帮忙看看这个条语句要怎么改

SQL code

select m.*, s.Name as ShopName from (select '1' as Flag,'更正明細' as name, a.*,b.orderno from (select a.*,b.UserName from ItemsDelete a left join S_User b on a.Deletor = b.UserCode or a.Deletor = b.UserName)  a left join orders b on a.orderid=b.id and a.shopid=b.shopid where a.orderid+a.shopid  Not in(select id+shopid from OrdersDelete)   ) m left join (select [Name], ID from shops order by ID)as s on s.ID = m.shopID

提示：消息 1033，级别 15，状态 1，第 8 行
除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

去掉这个Order by ID 是没问题的，但是我想拿shops表里面的ID排序的，或者OrdersDelete表里面的ShopID排序都可以，这个要怎么改

[解决办法]

SQL code

select m.*, s.Name as ShopName from (select '1' as Flag,'更正明細' as name, a.*,b.orderno from (select a.*,b.UserName from ItemsDelete a left join S_User b on a.Deletor = b.UserCode or a.Deletor = b.UserName)  a left join orders b on a.orderid=b.id and a.shopid=b.shopid where a.orderid+a.shopid  Not in(select id+shopid from OrdersDelete)   ) m left join (select [Name], ID from shops)as s on s.ID = m.shopIDUnionselect m.*,s.Name as ShopName from (select '2' as Flag,'刪單明細' as name, a.*,b.orderno from (select a.*,b.UserName from ItemsDelete a left join S_User b on a.Deletor = b.UserCode or a.Deletor = b.UserName)  a,OrdersDelete b where a.OrderID=b.ID and a.shopid=b.shopid) m left join Shops s on s.ID=M.ShopID order by shopID,s.id
[解决办法]
SQL code
select * from (select top 100 percent m.*, s.Name as ShopName from (select '1' as Flag,'更正明細' as name, a.*,b.orderno from (select a.*,b.UserName from ItemsDelete a left join S_User b on a.Deletor = b.UserCode or a.Deletor = b.UserName)  a left join orders b on a.orderid=b.id and a.shopid=b.shopid where a.orderid+a.shopid  Not in(select id+shopid from OrdersDelete)   ) m left join (select [Name], ID from shops)as s on s.ID = m.shopIDorder by s.id) tt Unionselect * from( select top 100 percent m.*,s.Name as ShopName from (select '2' as Flag,'刪單明細' as name, a.*,b.orderno from (select a.*,b.UserName from ItemsDelete a left join S_User b on a.Deletor = b.UserCode or a.Deletor = b.UserName)  a,OrdersDelete b where a.OrderID=b.ID and a.shopid=b.shopid) m left join Shops s on s.ID=M.ShopID order by shopID) kk 

 

查看更多 下一篇