如何解决错误 ORDER BY 子句在视图、内联函数、派生表、子查询中无效

Question

SQL Server 中的此选择语句返回此错误：

ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了 TOP 或 FOR XML。

我需要order by，但我无法删除它

SELECT  *
FROM    ( SELECT    stat_flag ,
                    stat_amount ,
                    stat_desc ,
                    trans_name ,
                    serial
          FROM      PostedVoucher
                    JOIN transactions ON trans_code = stat_trans_code
          WHERE     stat_leger = N'clients'
                    AND branch = '1'
                    AND stat_date BETWEEN '2013-12-03 00:00:00.000'
                                  AND     '2013-12-20 00:00:00.000'
          ORDER BY  stat_date
        ) AS t ,
        ( SELECT    SUM(stat_amount) AS sumDebt
          FROM      PostedVoucher
                    JOIN transactions ON trans_code = stat_trans_code
          WHERE     stat_flag = 'd'
                    AND stat_leger = N'clients'
                    AND branch = '1'
                    AND stat_date BETWEEN '2013-12-03 00:00:00.000'
                                  AND     '2013-12-20 00:00:00.000'
        ) AS t1 ,
        ( SELECT    SUM(stat_amount) AS sumcredit
          FROM      PostedVoucher
          WHERE     stat_flag = 'c'
                    AND stat_leger = N'clients'
                    AND branch = '1'
                    AND stat_date BETWEEN '2013-12-03 00:00:00.000'
                                  AND     '2013-12-20 00:00:00.000'
        ) AS t2 

我的桌子 ：

CREATE TABLE [dbo].[PostedVoucher](
    [stat_date] [datetime] NOT NULL,
    [branch] [nvarchar](100) NULL,
    [stat_trans_code] [int] NULL,
    [serial] [int] NULL,
    [stat_leger] [nvarchar](100) NULL,
    [stat_sub_leger] [nvarchar](100) NULL,
    [stat_desc] [nvarchar](100) NULL,
    [stat_amount] [decimal](20, 3) NULL,
    [stat_flag] [nvarchar](50) NULL,
    [id] [int] IDENTITY(1,1) NOT NULL,
    [ref_dec] [nvarchar](50) NULL,
    [serial1] [nvarchar](50) NULL
)

插入：----

INSERT [dbo].[PostedVoucher] (
    [stat_date], [branch], [stat_trans_code], [serial],
    [stat_leger], [stat_sub_leger], [stat_desc], [stat_amount],
    [stat_flag], [id], [ref_dec], [serial1])   
VALUES (
    CAST(0x0000A28900000000 AS DateTime), N'1', 103, 516,
    N' jkhjkh',  N'uhiuhiu', N' gjhgkjgh', CAST(3.000 AS Decimal(20, 3)),
    N'c', 1, N'system TPL', NULL)

Answer 1

可以使用窗口函数简化查询：

Select stat_flag, stat_amount, stat_desc, trans_name, serial,
       sum(case when stat_flag = 'd' then stat_amount else 0 end) over () as sumDebit,
       sum(case when stat_flag = 'c' then stat_amount else 0 end) over () as sumCredit
from PostedVoucher pv join
     transactions t
     on trans_code = stat_trans_code
where stat_leger = N'clients' and
      branch = '1' and
      stat_date between '2013-12-03' and '2013-12-20'
order by stat_date;

根本不需要子查询。 请注意，您的第三个子查询可能不起作用，因为它没有与transactions表的连接。 其他两个查询似乎需要此表。

我还删除了日期的时间部分。 我认为不必要的时间组件只会使查询混乱。

Answer 2

试试这个：

Select * from 
 (Select stat_date, stat_flag , stat_amount , stat_desc , trans_name , serial  from    
 PostedVoucher join transactions on trans_code = stat_trans_code where      
 stat_leger = N'clients' and branch='1' and stat_date between '2013-12-03   
00:00:00.000' and '2013-12-20 00:00:00.000' ) as t ,     
 (Select sum(stat_amount) as sumDebt from PostedVoucher join transactions on   
 trans_code = stat_trans_code where stat_flag ='d' and stat_leger = 
   N'clients' and branch='1' and stat_date between '2013-12-03 00:00:00.000'
    and'2013-12-20 00:00:00.000' )as t1 ,
 (Select  sum(stat_amount) as sumcredit from PostedVoucher where stat_flag    
  ='c' and stat_leger=N'clients' and branch='1' and stat_date between '2013-   
 12-03 00:00:00.000' and '2013-12-20 00:00:00.000') as t2 
ORDER BY t.stat_date

编辑：

我建议您开始使用 CTE，您的查询将更具可读性。可以通过以下方式重写 obove 查询：

WITH t AS
(
    SELECT stat_date, stat_flag , stat_amount , stat_desc , trans_name , serial  
    FROM    
        PostedVoucher INNER JOIN  transactions 
            on trans_code = stat_trans_code 
    WHERE       
        stat_leger = N'clients' and branch='1' 
        AND stat_date between '20131203' and '20131220'
),
t1 AS
(
    SELECT sum(stat_amount) as sumDebt 
    FROM 
        PostedVoucher INNER JOIN transactions 
            on trans_code = stat_trans_code 
    WHERE 
        stat_flag ='d' 
        AND stat_leger = N'clients' AND branch='1' 
        AND stat_date between '20131203' AND '20131220'
),
t2 AS
(
    SELECT  SUM (stat_amount) as sumcredit 
    FROM 
        PostedVoucher 
    WHERE
        stat_flag ='c' 
        AND stat_leger=N'clients' 
        AND branch='1' 
        AND stat_date between '20131203' and '20131220'
)
SELECT *
FROM
    t CROSS JOIN t1 CROSS JOIN t2
ORDER BY
    t.t.stat_date

Answer 3

如果你真的想使用ORDER BY，SELECT中有TOP 100 PERCENT。这有效地返回所有行，同时允许order by .如下所示：

      SELECT    TOP 100 PERCENT stat_flag ,
                stat_amount ,
                stat_desc ,
                trans_name ,
                serial
      FROM      PostedVoucher
                JOIN transactions ON trans_code = stat_trans_code
      WHERE     stat_leger = N'clients'
                AND branch = '1'
                AND stat_date BETWEEN '2013-12-03 00:00:00.000'
                              AND     '2013-12-20 00:00:00.000'
      ORDER BY  stat_date