时间戳

首页 > 解决方案 > 从子查询 SQL 中获取多行

sql-server - 从子查询 SQL 中获取多行

问题描述

基本上我有 2 个表格，第一个是每个序列号的原材料数量（QT），第二个是批次生产中花费了多少原材料（Qt_Added）。像这样：

表格1

+----------+------------+-----+
| Code_Raw | Serial_Raw | Qt  |
+----------+------------+-----+
|        1 |          1 | 100 |
|        1 |          2 | 150 |
|        2 |          1 |  80 |
|        1 |          3 | 100 |
+----------+------------+-----+

和表 2

+------------+----------+------------+----------+--+
| Code_Batch | Code_Raw | Serial_Raw | Qt_Added |  |
+------------+----------+------------+----------+--+
|          1 |        1 |          1 |       80 |  |
|          2 |        1 |          1 |       10 |  |
|          3 |        1 |          2 |      150 |  |
|          4 |        1 |          3 |       80 |  |
+------------+----------+------------+----------+--+

我试图查询一个特定的Code_Raw，告诉我每个序列号还剩多少，但只有在有一个时才有效serial_raw。

我的查询：

select * 
from 
    (select 
         Serial_Raw,
         (Select QT From Table_1 where Code_Raw = 1) - Sum(qt_added) as Total_Remaining 
     from
         Table_2
     where 
         Cod_Raw = 1
     group by 
         Serial_Raw) e
where 
    Total_Remaining > 0

但它会抛出这个错误

子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的

我期望：

Serial_Raw     Total_Remaining
-------------------------------
    1                10
    3                20

是否有结构问题或其他方式来做到这一点？

我正在使用 SQL Server 2014

多谢你们

标签： sql-serversql-server-2014

解决方案

试试这个：

DECLARE @tbl1 TABLE
(   CodeRaw INT,
    Serial_Raw     INT,
    Qty INT)


DECLARE @tbl2 TABLE
(   
    CodeBatch INT,
    CodeRaw INT,
    Serial_Raw     INT,
    QtyAdded    INT)

    INSERT INTO @tbl1 VALUES(1,1,100)
    INSERT INTO @tbl1 VALUES(1,2,150)
    INSERT INTO @tbl1 VALUES(2,1,80)
    INSERT INTO @tbl1 VALUES(1,3,100)

    INSERT INTO @tbl2 VALUES(1,1,1,80)
    INSERT INTO @tbl2 VALUES(2,1,1,10)
    INSERT INTO @tbl2 VALUES(3,1,2,150)
    INSERT INTO @tbl2 VALUES(4,1,3,80)

    --Inner table has the summary of the Quantity added with columns CodeRaw and SerialRaw. Outer table make join with inner table and just substruct with the Qty and Sum of Qty Added.  
    SELECT t2.Serial_Raw, t1.Qty - t2.QtyAdded AS Total_Remaining  FROM @tbl1 t1
        INNER JOIN (SELECT CodeRaw, Serial_Raw , SUM(QtyAdded) QtyAdded FROM @tbl2
                    GROUP BY CodeRaw, Serial_Raw) AS t2 ON t2.CodeRaw = t1.CodeRaw AND t1.Serial_Raw = t2.Serial_Raw
    WHERE t1.Qty - t2.QtyAdded > 0

推荐阅读