SQL SubQuery错误

Question

我想在下面的sample.The中检索数据，下面的SQL将返回两条记录。

SELECT -1 AS NUM
FROM TABLE
WHERE COMP_CODE = 'TEST'
  AND (DETL_REMK = 'Rest Day'
       OR SHFT_CODE = 'WK_PH')
  AND RSRV_DATE_1 IS NOT NULL
  AND RSRV_DATE_1 BETWEEN @TR_FR AND @TR_TO
  AND EMPE_ID = 'TEST'
GROUP BY EMPE_ID,
         RSRV_DATE_1

我想展示下图。

ORG UNIT          EMPE_ID        FAM_NAME       TMS_TYPE     Qty
-----------------------------------------------------------------
''                 ''              ''            Used         -1
''                 ''              ''            Used         -1

所以，我试着在SQL语句中这样做，但是我得到了一个错误：“子查询返回了一个以上的值。当子查询跟在=，!=，<，<=，>，>=之后或者当子查询被用作表达式时，这是不允许的。”

如何构造正确的SQL。请帮帮我。提前谢谢。

SELECT '' ORG_UNIT, '' EMPE_ID, '' FAM_NAME, 'Used' AS TMS_TYPE,
       ISNULL(CAST(
                  (SELECT -1 AS NUM
                   FROM TABLE
                   WHERE COMP_CODE = 'TEST'
                     AND (DETL_REMK = 'Rest Day'
                          OR SHFT_CODE = 'WK_PH')
                     AND RSRV_DATE_1 IS NOT NULL
                     AND RSRV_DATE_1 BETWEEN @TR_FR AND @TR_TO
                     AND EMPE_ID = 'TEST'
                   GROUP BY EMPE_ID, RSRV_DATE_1)AS NVARCHAR(MAX)),0) QTY

Answer 1

如果只是对子查询外部的值进行硬编码，则不需要使用子查询来返回2行，只需执行以下操作：

SELECT '' ORG_UNIT, '' EMPE_ID, '' FAM_NAME, 'Used' AS TMS_TYPE, -1 AS QTY
FROM TABLE
WHERE COMP_CODE = 'TEST'
  AND (DETL_REMK = 'Rest Day'
       OR SHFT_CODE = 'WK_PH')
  AND RSRV_DATE_1 IS NOT NULL
  AND RSRV_DATE_1 BETWEEN @TR_FR AND @TR_TO
  AND EMPE_ID = 'TEST'
GROUP BY EMPE_ID,
         RSRV_DATE_1

这里的问题是，您并没有引用表中的任何列，只是使用它来生成大量的行。请分享完整的问题和代码，因为我相信这会比你所问的更多。

Answer 2

你可以试试这个

SELECT '' ORG_UNIT, '' EMPE_ID, '' FAM_NAME, 'Used' AS TMS_TYPE, -1 as QTY
union all
SELECT '' ORG_UNIT, '' EMPE_ID, '' FAM_NAME, 'Used' AS TMS_TYPE, -1 as QTY

Answer 3

您需要将Top 1添加到您的子查询中，从您得到的错误来看，该子查询似乎返回了超过1行：

SELECT Top 1 -1 AS NUM
FROM TABLE
WHERE COMP_CODE = 'TEST'
AND (DETL_REMK = 'Rest Day',
OR SHFT_CODE = 'WK_PH')
AND RSRV_DATE_1 IS NOT NULL
AND RSRV_DATE_1 BETWEEN @TR_FR AND @TR_TO
AND EMPE_ID = 'TEST'
GROUP BY EMPE_ID, RSRV_DATE_1