问子查询不能正常工作
EN

Stack Overflow用户

提问于 2015-01-12 00:35:12

回答 1查看 60关注 0票数 0

我试图在另一个临时表中插入子查询的结果。当我只执行它时，subquery工作得很好，但是当我执行子查询时，它就不能正常工作。它会引发语法错误。错误是:不正确的语法靠近')‘。它位于子查询的第一行。

    DECLARE @TempT TABLE
    (
      RowID INT IDENTITY(1, 1) ,
      Date DATETIME ,
      Type NVARCHAR(MAX) ,
      V_No INT ,
      Chq_No INT ,
      Description NVARCHAR(MAX) ,
      Debit MONEY ,
      Credit MONEY ,
      voucher_type_no INT ,
      status NVARCHAR(10) ,
      Clr_Date DATETIME ,
      Voucher_Id INT ,
      Party_Name NVARCHAR(MAX) ,
      DateYYYYMMDD DATETIME
    )

DECLARE @i INT= 1
INSERT  INTO @TempT
        SELECT  *
        FROM    ( SELECT    v.date 'Date' ,
                            vt.voucher_type_shortname 'Type' ,
                            v.voucher_no 'V_No' ,
                            v.cheque_no 'Chq_No' ,
                            a.account_name 'Description' ,
                            ( CASE WHEN SUM(v.amount) > 0 THEN SUM(v.amount)
                                   ELSE 0
                              END ) Debit ,
                            ( CASE WHEN SUM(v.amount) < 0 THEN SUM(-v.amount)
                                   ELSE 0
                              END ) Credit ,
                            v.voucher_type_no ,
                            r.status ,
                            r.recon_date 'Clr_Date' ,
                            MIN(v.voucher_id) Voucher_Id ,
                            '' 'Party_Name' ,
                            CONVERT(VARCHAR(10), r.recon_date, 126) 'Date YYYY-MM-DD'
                  FROM      voucher v
                            LEFT OUTER JOIN reconcilation r ON v.voucher_id = r.voucher_id
                            LEFT OUTER JOIN account a ON v.other_acno = a.account_no ,
                            voucher_type vt
                  WHERE     v.voucher_type_no = vt.voucher_type_no
                            AND v.voucher_type_no > 0
                            AND v.other_acno = a.account_no
                            AND v.acc_year = 51
                            AND v.account_no = 10030
                            AND R.recon_date <= '2015-01-12'
                            AND R.recon_date >= '2009-04-01'
                            AND V.posted IN ( 1, 2 )
                  GROUP BY  v.voucher_no ,
                            v.cheque_no ,
                            v.date ,
                            vt.voucher_type_shortname ,
                            a.account_name ,
                            v.voucher_type_no ,
                            r.status ,
                            r.recon_date
                )

sql

sql-server

tsql

subquery

回答 1

Stack Overflow用户

回答已采纳

发布于 2015-01-12 00:37:35

在最后一个括号后面使用别名。

    DECLARE @TempT TABLE
    (
      RowID INT IDENTITY(1, 1) ,
      Date DATETIME ,
      Type NVARCHAR(MAX) ,
      V_No INT ,
      Chq_No INT ,
      Description NVARCHAR(MAX) ,
      Debit MONEY ,
      Credit MONEY ,
      voucher_type_no INT ,
      status NVARCHAR(10) ,
      Clr_Date DATETIME ,
      Voucher_Id INT ,
      Party_Name NVARCHAR(MAX) ,
      DateYYYYMMDD DATETIME
    )

DECLARE @i INT= 1
INSERT  INTO @TempT
        SELECT  *
        FROM    ( SELECT    v.date 'Date' ,
                            vt.voucher_type_shortname 'Type' ,
                            v.voucher_no 'V_No' ,
                            v.cheque_no 'Chq_No' ,
                            a.account_name 'Description' ,
                            ( CASE WHEN SUM(v.amount) > 0 THEN SUM(v.amount)
                                   ELSE 0
                              END ) Debit ,
                            ( CASE WHEN SUM(v.amount) < 0 THEN SUM(-v.amount)
                                   ELSE 0
                              END ) Credit ,
                            v.voucher_type_no ,
                            r.status ,
                            r.recon_date 'Clr_Date' ,
                            MIN(v.voucher_id) Voucher_Id ,
                            '' 'Party_Name' ,
                            CONVERT(VARCHAR(10), r.recon_date, 126) 'Date YYYY-MM-DD'
                  FROM      voucher v
                            LEFT OUTER JOIN reconcilation r ON v.voucher_id = r.voucher_id
                            LEFT OUTER JOIN account a ON v.other_acno = a.account_no ,
                            voucher_type vt
                  WHERE     v.voucher_type_no = vt.voucher_type_no
                            AND v.voucher_type_no > 0
                            AND v.other_acno = a.account_no
                            AND v.acc_year = 51
                            AND v.account_no = 10030
                            AND R.recon_date <= '2015-01-12'
                            AND R.recon_date >= '2009-04-01'
                            AND V.posted IN ( 1, 2 )
                  GROUP BY  v.voucher_no ,
                            v.cheque_no ,
                            v.date ,
                            vt.voucher_type_shortname ,
                            a.account_name ,
                            v.voucher_type_no ,
                            r.status ,
                            r.recon_date
                ) TAB