logo标签列表

在SELECT语句中动态设置一个变量。

sqlsql-serverdatabasesap-erpsql-data-warehouse
4
我有一个带有多个SAP数据表的SQL数据库，以及一个像这样的SQL语句：
SELECT DISTINCT
AFKO.PLNBEZ AS 'Material',
MAKT.MAKTX AS 'Material Number', 
AFKO.AUFNR AS 'Order', 
AFVC.VORNR AS 'Operation Number',
CRTX.KTEXT AS 'Operation Text', 
AFVV.VGW01 AS 'Estimated Hours 1',
AFVV.VGW02 AS 'Estimated Hours 2',
AFVV.VGW03 AS 'Estimated Hours 3',
AFVV.VGW04 AS 'Estimated Hours 4',
AFVV.VGW05 AS 'Estimated Hours 5',
AFVV.VGW06 AS 'Estimated Hours 6',
AFVV.ISM01 AS 'Actual Hours 1',
AFVV.ISM02 AS 'Actual Hours 2',
AFVV.ISM03 AS 'Actual Hours 3',
AFVV.ISM04 AS 'Actual Hours 4',
AFVV.ISM05 AS 'Actual Hours 5',
AFVV.ISM06 AS 'Actual Hours 6',

(SELECT TOP 1
    AFRU.ISDD
    FROM AFRU 
    WHERE AUFNR = AFKO.AUFNR
      AND RUECK = AFVC.RUECK
    ORDER BY ISDD ASC
) AS 'Op Actual Start Date',

(SELECT TOP 1
    AFRU.ISDZ
    FROM AFRU 
    WHERE AUFNR = AFKO.AUFNR
      AND RUECK = AFVC.RUECK
    ORDER BY ISDZ ASC
) AS 'Op Actual Start Time',

(SELECT TOP 1
    AFRU.IEDD
    FROM AFRU 
    WHERE AUFNR = AFKO.AUFNR
      AND RUECK = AFVC.RUECK
    ORDER BY IEDD DESC
) AS 'Op Actual Finish Date',

(SELECT TOP 1
    AFRU.IEDZ
    FROM AFRU 
    WHERE AUFNR = AFKO.AUFNR
      AND RUECK = AFVC.RUECK
    ORDER BY IEDD DESC
) AS 'Op Actual Finish Time',

AFVC.RUECK AS 'Confirmation Number',
AFVC.ARBID AS 'OBJID',
AFKO.GSTRI AS 'Order Actual Start Date',
AFKO.GETRI AS 'Order Confirmed Finish Date',
COUNT(AFRU.RUECK) AS "No. of Confirmations",

CASE
    WHEN COUNT(AFRU.RUECK) = 0 THEN 'Confirmed on mass'
    WHEN COUNT(AFRU.RUECK) = 1 THEN 'Auto Confirmation'
    ELSE 'User clocked on & off'
END AS Accuracy

FROM AFKO
INNER JOIN afvc ON afvc.AUFPL = AFKO.AUFPL
LEFT OUTER JOIN AFRU afru.rueck = afvc.rueck
INNER JOIN MAKT ON AFKO.PLNBEZ = MAKT.MATNR 
INNER JOIN CRHD ON crhd.OBJID = afvc.ARBID
INNER JOIN CRTX ON AFVC.ARBID = CRTX.OBJID 
INNER JOIN AFVV ON AFVC.AUFPL = AFVV.AUFPL
               AND AFVC.APLZL = AFVV.APLZL 
INNER JOIN AUFK ON AFKO.AUFNR = AUFK.AUFNR
WHERE AUFK.WERKS = 1000
AND (crhd.ARBPL LIKE 'BSCREBAR'
OR crhd.ARBPL LIKE 'BSCFISET'
OR crhd.ARBPL LIKE 'BSCWDSP'
OR crhd.ARBPL LIKE 'BSCPRSET'
OR crhd.ARBPL LIKE 'BSCCAST'
OR crhd.ARBPL LIKE 'BSCDEMLD' )

GROUP BY AFKO.PLNBEZ, MAKT.MAKTX, AFKO.AUFNR, AFVC.VORNR, CRTX.KTEXT, AFVV.VGW01, AFVV.VGW02, AFVV.VGW03, AFVV.VGW04, AFVV.VGW05, AFVV.VGW06, 
AFVV.ISM01, AFVV.ISM02, AFVV.ISM03, AFVV.ISM04, AFVV.ISM05, AFVV.ISM06, AFRU.ISDD, AFRU.ISDZ, AFRU.IEDD, AFRU.IEDZ, AFVC.RUECK, AFVC.ARBID, AFKO.GSTRI, AFKO.GETRI
;

问题是我的COUNT(AFRU.RUECK) AS "No. of Confirmations"返回了错误的值，我认为这与我的某个连接有关，但我不确定。
无论如何，我将选择语句更改为以下内容：
(SELECT COUNT (*) FROM AFRU WHERE RUECK = AFVC.RUECK) AS 'No. of Confirmations',
CASE
    WHEN (SELECT COUNT (*) FROM AFRU WHERE RUECK = AFVC.RUECK) = 0 THEN 'Confirmed on mass'
    WHEN (SELECT COUNT (*) FROM AFRU WHERE RUECK = AFVC.RUECK) = 1 THEN 'Auto Confirmation'
    ELSE 'User clocked on & off'
END AS 'Accuracy'

这个工作非常完美，正是我想要的。然而，这并不是选择数据的最有效方式。由于这个改变，执行该语句大约需要10分钟。
所以我尝试将它从上面改成了这样：
@confs = (SELECT COUNT (*) FROM AFRU WHERE RUECK = AFVC.RUECK),
@confs AS 'No. of Confirmations',
CASE
    WHEN (@confs) = 0 THEN 'Confirmed on mass'
    WHEN (@confs) = 1 THEN 'Auto Confirmation'
    ELSE 'User clocked on & off'
END AS 'Accuracy'

为了消除额外的SELECT语句，我在主SELECT语句之前使用了一个变量 - DECLARE @confs int;。
然而，我遇到了一个错误信息，提示：
Msg 141，Level 15，State 1，Line 3 分配值给变量的SELECT语句不能与数据检索操作结合使用。
我该如何解决这个问题？是否有可能解决这个问题？
我在SQL中看到的每个声明变量的示例都不包括动态WHERE子句。我特别需要引用另一个表（AFRU）来获取特定确认号（RUECK）的记录数量 - 这些确认号来自另一个表（AFVC），作为我的主要SELECT语句的一部分。
编辑：
根据上面的SQL代码（在我做任何更改之前），这是完整输出的示例：
+------------------+-----------------+---------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------------+----------------------+-----------------------+-----------------------+---------------------+----------+-------------------------+-----------------------------+----------------------+-----------------------+
| Material         | Material Number | Order   | Operation Number | Operation Text  | Estimated Hours 1 | Estimated Hours 2 | Estimated Hours 3 | Estimated Hours 4 | Estimated Hours 5 | Estimated Hours 6 | Actual Hours 1 | Actual Hours 2 | Actual Hours 3 | Actual Hours 4 | Actual Hours 5 | Actual Hours 6 | Op Actual Start Date | Op Actual Start Time | Op Actual Finish Date | Op Actual Finish Time | Confirmation Number | OBJID    | Order Actual Start Date | Order Confirmed Finish Date | No. of Confirmations | Accuracy              |
+------------------+-----------------+---------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------------+----------------------+-----------------------+-----------------------+---------------------+----------+-------------------------+-----------------------------+----------------------+-----------------------+
| 1900A-D14MSB-385 | Solid plank     | 1713023 | 60               | BSC Casting     | 0                 | 0                 | 2.132             | 0                 | 0                 | 0                 | 0              | 0              | 2.132          | 0              | 0              | 0              | 20200302             | 100959               | 20200302              | 121124                | 7566152             | 10000385 | 20200226                | 20200303                    | 3                    | User clocked on & off |
+------------------+-----------------+---------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------------+----------------------+-----------------------+-----------------------+---------------------+----------+-------------------------+-----------------------------+----------------------+-----------------------+
| 1900A-D14MSB-406 | Solid plank     | 1713025 | 60               | BSC Casting     | 0                 | 0                 | 2.132             | 0                 | 0                 | 0                 | 0              | 0              | 2.132          | 0              | 0              | 0              | 20200226             | 210329               | 20200226              | 210329                | 7566124             | 10000385 | 20200226                | 20200227                    | 1                    | Auto Confirmation     |
+------------------+-----------------+---------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------------+----------------------+-----------------------+-----------------------+---------------------+----------+-------------------------+-----------------------------+----------------------+-----------------------+
| 1900A-D14MSB-414 | Solid plank     | 1713026 | 40               | BSC Primary Set | 2.132             | 0                 | 0                 | 0                 | 0                 | 0                 | 0.19           | 0              | 0              | 0              | 0              | 0              | 20200227             | 142442               | 20200227              | 152927                | 7566106             | 10000383 | 20200227                | 20200303                    | 2                    | User clocked on & off |
+------------------+-----------------+---------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------------+----------------------+-----------------------+-----------------------+---------------------+----------+-------------------------+-----------------------------+----------------------+-----------------------+
| 1900A-D14MSB-436 | Solid plank     | 1713028 | 60               | BSC Casting     | 0                 | 0                 | 0                 | 2.132             | 0                 | 0                 | 0              | 0              | 2.132          | 0              | 0              | 0              | 20200224             | 142546               | 20200224              | 154025                | 7556163             | 10000385 | 20200221                | 20200225                    | 2                    | User clocked on & off |
+------------------+-----------------+---------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------------+----------------------+-----------------------+-----------------------+---------------------+----------+-------------------------+-----------------------------+----------------------+-----------------------+

我的AFRU表格看起来像这样（对于确认号码0007566152）：
在我上面的例子中，“确认数量”是3，但实际上表格中包含了6条记录，这意味着3的值是不正确的，它应该实际上是6。
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| RUECK   | ERSDA    | ERZET  | ERNAM   | WERKS | ISDD     | ISDZ   | IEDD     | IEDZ   | AUERU | AUFPL  | APLZL | AUFNR   | VORNR | fwk_LineageID | fwk_VersionID |  |  |  |  |  |  |  |  |  |  |  |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| 7566152 | 20200302 | 124517 | DHAWLEY | 1000  | 20200302 | 100959 | 20200302 | 124517 | X     | 717464 | 19    | 1713023 | 60    | 2873485       | 4             |  |  |  |  |  |  |  |  |  |  |  |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| 7566152 | 20200302 | 121124 | DHAWLEY | 1000  | 20200302 | 100959 | 20200302 | 121124 |       | 717464 | 19    | 1713023 | 60    | 2873485       | 4             |  |  |  |  |  |  |  |  |  |  |  |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| 7566152 | 20200302 | 124517 | DHAWLEY | 1000  | 20200302 | 100959 | 20200302 | 124517 | X     | 717464 | 19    | 1713023 | 60    | 2873485       | 4             |  |  |  |  |  |  |  |  |  |  |  |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| 7566152 | 20200302 | 102224 | DHAWLEY | 1000  | 20200302 | 100959 | 20200302 | 102224 |       | 717464 | 19    | 1713023 | 60    | 2873485       | 4             |  |  |  |  |  |  |  |  |  |  |  |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| 7566152 | 20200302 | 124517 | DHAWLEY | 1000  | 20200302 | 100959 | 20200302 | 124517 | X     | 717464 | 19    | 1713023 | 60    | 2873485       | 4             |  |  |  |  |  |  |  |  |  |  |  |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| 7566152 | 20200302 | 102224 | DHAWLEY | 1000  | 20200302 | 100959 | 20200302 | 102224 |       | 717464 | 19    | 1713023 | 60    | 2873485       | 4             |  |  |  |  |  |  |  |  |  |  |  |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+

在我的最佳结果中，我希望看到那个特定记录的值为6，而不是3。
PS crhd.ARBPL LIKE 'BSCREBAR' 只是一个 crhd.ARBPL = 'BSCREBAR'，而整个子句本质上是一种冗长的写法，用于编写 crhd.ARBPL IN 'BSCREBAR', 'BSCFISET',...)。 - Panagiotis Kanavos
谢谢提醒，我改成使用“IN”来编写我的where子句了。然而，“FIRST_VALUE”返回了一个错误信息：“Msg 195, Level 15, State 10, Line 3 'FIRST_VALUE' is not a recognized built-in function name.” 我也包含了一些数据库表的片段，这样问题是否更加清晰了呢？还是我需要再从其他表格中添加更多的样例？ - nopassport1
您正在进行“分组(group by)”之后的计数。根据您在“分组(group by)”部分中指定的数据和列(AFRU.ISDD、AFRU.ISDZ、AFRU.IEDD、AFRU.IEDZ)，您只有3行是独特的。 - Serkan Arslan
@nopassport1 添加一个变量并不能修复一个错误的查询。查询仍然是有问题的。你所试图做的事情是不可能的，因为在同一个SELECT子句中无法赋值和使用已赋值的变量。没有任何东西表明第一个子句会在第二个之前运行。或者说它会运行分开。查询是基于集合的操作，而不是循环。 - Panagiotis Kanavos
1
@MarcGuillot 可能确实很简单 - 这是因为其他行被JOIN过滤掉了，导致期望出错。 - Panagiotis Kanavos
显示剩余10条评论
1个回答
2
您可以使用outer apply代替left join。而且您也不需要group by。
SELECT

  ....,

  AFKO.GSTRI AS 'Order Actual Start Date',
  AFKO.GETRI AS 'Order Confirmed Finish Date',

  T.[No. of Confirmations],
  T.Accuracy

FROM AFKO
INNER JOIN afvc ON afvc.AUFPL = AFKO.AUFPL
INNER JOIN MAKT ON AFKO.PLNBEZ = MAKT.MATNR 
INNER JOIN CRHD ON crhd.OBJID = afvc.ARBID
INNER JOIN CRTX ON AFVC.ARBID = CRTX.OBJID 
INNER JOIN AFVV ON AFVC.AUFPL = AFVV.AUFPL
               AND AFVC.APLZL = AFVV.APLZL 
INNER JOIN AUFK ON AFKO.AUFNR = AUFK.AUFNR
OUTER APPLY ( SELECT 
                COUNT(A.RUECK) AS "No. of Confirmations",
                CASE
                    WHEN COUNT(A.RUECK) = 0 THEN 'Confirmed on mass'
                    WHEN COUNT(A.RUECK) = 1 THEN 'Auto Confirmation'
                ELSE 'User clocked on & off'
                END AS Accuracy
            FROM AFRU A WHERE A.RUECK = AFVC.RUECK ) AS T

WHERE AUFK.WERKS = 1000
    AND (crhd.ARBPL LIKE 'BSCREBAR'
    OR crhd.ARBPL LIKE 'BSCFISET'
    OR crhd.ARBPL LIKE 'BSCWDSP'
    OR crhd.ARBPL LIKE 'BSCPRSET'
    OR crhd.ARBPL LIKE 'BSCCAST'
    OR crhd.ARBPL LIKE 'BSCDEMLD' )
这太完美了。运行得像魔法一样。感谢您向我介绍了“OUTER APPLY”。我猜问题出在我的“LEFT JOIN”和“GROUP BY”，正如评论中所提到的那样。 - nopassport1
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接