代码之家 › 专栏 › 技术社区 › Mia Chen

SQL,将主查询的结果用于子查询

tsql sql-server sql

Mia Chen · 技术社区 · 7 年前

我正在尝试向视图中添加列。此列使用上一个结果计算新值。

但是,似乎不可能将主查询的结果用于子查询。

为了简化问题,它将给出:

select distinct operation,invoice,corrective,
测试编号=(选择不同的操作,发票
从Tab1
其中操作=表1.纠正)
从Tab1
< /代码> 

但是,它不起作用。

 IGG SRC=“HTTPS://I.STACK.IMGURUR.COM/2HBWN.PNG”ALT=“输入图像描述在这里”/>  

如果我写下以下内容,它会起作用:

select distinct operation,invoice,corrective,
测试编号=(选择不同的操作,发票
从Tab1
其中操作=20483)
从Tab1
< /代码> 



似乎SQL试图提取条件operation=corrective的所有数字。而我真正想要的是把前一个标签修正结果注入到我的子查询中。我该怎么办?

这是真正的代码:

选择distinct dbo.operation.id作为operationid,
dbo.invoice.id作为maxinvoice,
dbo.operation.coractiveid作为coractive,
(
选择不同的dbo.invoice.invoicenumber
来自dbo.invoice内部联接
dbo.invoice.id上的dbo.invoiceclient=dbo.invoiceclient.invoiceid
dbo.invoice.id=dbo.invoiceline.invoiceid上的内部联接dbo.invoiceline
dbo.invoice.operation id=dbo.operation.id上的内部联接dbo.operation
内部连接
选择dbo.operation.id作为operationid,max(distinct dbo.invoice.id)作为maxinvoice
从DBO发票
dbo.invoice.id=dbo.invoiceclient.invoiceid上的内部联接dbo.invoiceclient
dbo.invoice.id=dbo.invoiceline.invoiceid上的内部联接dbo.invoiceline
dbo.invoice.operation id=dbo.operation.id上的内部联接dbo.operation
group by dbo.operation.id)dbo.invoice.id=sub1.maxinvoice上的sub1
和dbo.operation.id=sub1.operationid

其中dbo.operation.id=dbo.operation.coractiveid
作为测试号
从(DBO.发票
dbo.invoice.id=dbo.invoiceclient.invoiceid上的内部联接dbo.invoiceclient
dbo.invoice.id=dbo.invoiceline.invoiceid上的内部联接dbo.invoiceline
dbo.invoiceline.id=dbo.invoicelinehistory.invoicelineid上的内部联接dbo.invoicelinehistory
dbo.invoice.operation id=dbo.operation.id上的内部联接dbo.operation
内部连接
选择dbo.operation.id作为operationid,max(distinct dbo.invoice.id)作为maxinvoice
从DBO发票
dbo.invoice.id=dbo.invoiceclient.invoiceid上的内部联接dbo.invoiceclient
dbo.invoice.id=dbo.invoiceline.invoiceid上的内部联接dbo.invoiceline
dbo.invoice.operation id=dbo.operation.id上的内部联接dbo.operation
group by dbo.operation.id)dbo.invoice.id=sub.maxinvoice上的sub
和dbo.operation.id=sub.operationid
)
按dbo.operation.id排序
< /代码> <可以将主查询的结果用于子查询。

为了简化问题,它将给出:

   SELECT DISTINCT Operation, Invoice, Corrective, 
   TestNumber = ( SELECT DISTINCT Operation, Invoice 
                   FROM Tab1
                   WHERE Operation = Tab1.Corrective)
   FROM Tab1


但是,它不起作用。



如果我写下以下内容,它会起作用:

    SELECT DISTINCT Operation, Invoice, Corrective, 
    TestNumber = ( SELECT DISTINCT Operation, Invoice 
                   FROM Tab1
                   WHERE Operation = 20483)
    FROM Tab1




似乎SQL试图提取条件operation=corrective的所有数字。而我真正想要的是把前一个标签修正结果注入到我的子查询中。我该怎么办?

这是真正的代码:

SELECT DISTINCT dbo.Operation.Id AS OperationID, 
                dbo.Invoice.Id AS maxInvoice, 
                dbo.Operation.CorectiveId as Corective,  
                (
                    SELECT Distinct dbo.Invoice.InvoiceNumber
                    FROM dbo.Invoice INNER JOIN
                    dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId 
                    INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id =  dbo.InvoiceLine.InvoiceId 
                    INNER JOIN dbo.Operation ON dbo.Invoice.OperationId = dbo.Operation.Id
                    INNER JOIN (
                        SELECT dbo.Operation.Id as OperationID, MAX(DISTINCT dbo.Invoice.Id) as MaxInvoice
                        FROM dbo.Invoice 
                        INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId 
                        INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId 
                        INNER JOIN dbo.Operation ON dbo.Invoice.OperationId = dbo.Operation.Id
                    GROUP BY dbo.Operation.Id) sub1 ON dbo.Invoice.Id = sub1.MaxInvoice 
                                                   AND dbo.Operation.Id = sub1.OperationID

                    WHERE dbo.Operation.Id = dbo.Operation.CorectiveId
                ) as testnumber 
FROM (dbo.Invoice 
INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId 
INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId 
INNER JOIN dbo.InvoiceLineHistory ON dbo.InvoiceLine.Id = dbo.InvoiceLineHistory.InvoiceLineId 
INNER JOIN dbo.Operation ON dbo.Invoice.OperationId = dbo.Operation.Id
INNER JOIN (
    SELECT dbo.Operation.Id as OperationID, MAX(DISTINCT dbo.Invoice.Id) as MaxInvoice
    FROM dbo.Invoice 
    INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId 
    INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId 
    INNER JOIN dbo.Operation ON dbo.Invoice.OperationId = dbo.Operation.Id
    GROUP BY dbo.Operation.Id) sub ON dbo.Invoice.Id = sub.MaxInvoice 
                                  AND dbo.Operation.Id = sub.OperationID 
)
ORDER BY dbo.Operation.Id

1 回复 | 直到 7 年前

Goran Kutlaca 7 年前

  SELECT DISTINCT dbo.Operation.Id AS OperationID,
              dbo.Invoice.Id AS maxInvoice,
              dbo.Operation.CorectiveId AS Corective,
              (SELECT DISTINCT dbo.Invoice.InvoiceNumber
                 FROM dbo.Invoice
                      INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId
                      INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId
                      INNER JOIN dbo.Operation operation_inner ON dbo.Invoice.OperationId = operation_inner.Id
                      INNER JOIN (  SELECT dbo.Operation.Id AS OperationID, MAX (DISTINCT dbo.Invoice.Id) AS MaxInvoice
                                      FROM dbo.Invoice
                                           INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId
                                           INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId
                                           INNER JOIN dbo.Operation ON dbo.Invoice.OperationId = dbo.Operation.Id
                                  GROUP BY dbo.Operation.Id) sub1
                          ON dbo.Invoice.Id = sub1.MaxInvoice AND dbo.Operation.Id = sub1.OperationID
                WHERE dbo.Operation.Id = operation_inner.CorectiveId)
                  AS testnumber
FROM (dbo.Invoice
      INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId
      INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId
      INNER JOIN dbo.InvoiceLineHistory ON dbo.InvoiceLine.Id = dbo.InvoiceLineHistory.InvoiceLineId
      INNER JOIN dbo.Operation operation_outer ON dbo.Invoice.OperationId = operation_outer.Id
      INNER JOIN (  SELECT dbo.Operation.Id AS OperationID, MAX (DISTINCT dbo.Invoice.Id) AS MaxInvoice
                      FROM dbo.Invoice
                           INNER JOIN dbo.InvoiceClient ON dbo.Invoice.Id = dbo.InvoiceClient.InvoiceId
                           INNER JOIN dbo.InvoiceLine ON dbo.Invoice.Id = dbo.InvoiceLine.InvoiceId
                           INNER JOIN dbo.Operation ON dbo.Invoice.OperationId = dbo.Operation.Id
                  GROUP BY dbo.Operation.Id) sub
          ON dbo.Invoice.Id = sub.MaxInvoice AND operation_outer.Id = sub.OperationID)
ORDER BY operation_outer.Id