Question

我正在通过处理一些问题来学习SQ服务器。

在其中一个问题中,我有4个数据库表:<编码> ORDERS,SHIPments, WHFICHES和ATTACHments。

其中一份是关于指定为<编码>的其他表格的附文。 ATTACHments。它有3栏:

LREF (Unique ref as primary key)
RECREF (Records ref)
RECTYPE (Record type)

“alt

通过核对<代码>RECTYPE的价值,我希望通过<代码>RECREF与其他表格合并。

这里,我转接案件:

SELECT LREF,RECREF,RECTYPE,
CASE RECTYPE 
WHEN 100 THEN (SELECT O.ORDERNO FROM AV_ATTACHMENTS A LEFT OUTER JOIN AV_ORDERS O ON A.RECREF = O.LREF WHERE A.RECREF = O.LREF)   
WHEN 200 THEN (SELECT S.FSNNO FROM AV_ATTACHMENTS A LEFT OUTER JOIN AV_SHIPMENTS S ON A.RECREF = S.LREF WHERE A.RECREF = S.LREF)
ELSE (SELECT F.WHFICHENO FROM AV_ATTACHMENTS A LEFT OUTER JOIN AV_WHFICHES F ON A.RECREF = F.WHFICHENO WHERE A.RECREF = F.LREF)
END AS FICHE
 FROM AV_ATTACHMENTS

此外,在其他表格中,I有独特的主要钥匙LREF,另一个数值将用作。 FICHENO。

我收到这一警告:

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , , >= or when the subquery is used as an expression.

What should I do exactly, because I can t find any solution. Should I change my SQL statement? Any suggestions?

My kind regards and thanks in advance.

Answer 1

鉴于你回来的错误需要改变询问。下面几句可能更接近你们想要的东西:

SELECT LREF, RECREF, RECTYPE
  FROM AV_ATTACHMENTS V
LEFT OUTER JOIN (SELECT A.LREF, O.ORDERNO AS FICHE
                   FROM AV_ATTACHMENTS A
                 LEFT OUTER JOIN AV_ORDERS O
                   ON A.RECREF = O.LREF
                 WHERE A.RECREF = O.LREF) T1
  ON (V.RECTYPE = 100 AND
      T1.LREF = V.LREF)
LEFT OUTER JOIN (SELECT A.LREF, S.FSNNO A FICHE
                   FROM AV_ATTACHMENTS A
                 LEFT OUTER JOIN AV_SHIPMENTS S
                   ON A.RECREF = S.LREF
                 WHERE A.RECREF = S.LREF) T2
  ON (V.RECTYPE = 200 AND
      T2.LREF = V.LREF)
LEFT OUTER JOIN (SELECT A.LREF, F.WHFICHENO AS FICHE
                   FROM AV_ATTACHMENTS A
                 LEFT OUTER JOIN AV_WHFICHES F
                   ON A.RECREF = F.WHFICHENO
                 WHERE A.RECREF = F.LREF) T3
  ON (V.RECTYPE NOT IN (100, 200) AND
      T3.LREF = V.LREF);

Share and enjoy.

Answer 2

尝试如下:

SELECT  A.LREF, 
        A.RECREF, 
        A.RECTYPE,
        CASE A.RECTYPE 
            WHEN 100 THEN O.ORDERNO
            WHEN 200 THEN S.FSNNO
            WHEN 300 THEN F.WHFICHENO
            ELSE NULL
        END AS FICHE
FROM        AV_ATTACHMENTS A
LEFT JOIN   AV_ORDERS O 
        ON  A.RECREF = O.LREF
        AND A.RECTYPE = 100
LEFT JOIN   AV_SHIPMENTS S 
        ON  A.RECREF = S.LREF
        AND A.RECTYPE = 200
LEFT JOIN   AV_WHFICHES F 
        ON  A.RECREF = F.WHFICHENO
        AND A.RECTYPE = 300

Answer 3

你的子座放在了选择发言中,因此不可能在一个单一的选择领域有多重结果。这样做的一个途径是将<代码>SlectT TOP 1列入您的分册。

Answer 4

在条件允许的情况下,可插入两个温带。

并加入这些温带!

Answer 5

以下简称表:

SELECT LREF,RECREF,RECTYPE, 
       CASE RECTYPE
          WHEN 100 THEN (
                         SELECT TOP 1 O.ORDERNO 
                           FROM AV_ATTACHMENTS A 
                                LEFT OUTER JOIN AV_ORDERS O 
                                   ON A.RECREF = O.LREF 
                          WHERE A.RECREF = O.LREF
                        )
          WHEN 200 THEN (
                         SELECT TOP 1 S.FSNNO 
                           FROM AV_ATTACHMENTS A 
                                LEFT OUTER JOIN AV_SHIPMENTS S 
                                   ON A.RECREF = S.LREF 
                          WHERE A.RECREF = S.LREF
                        ) 
          ELSE (
                SELECT TOP 1 F.WHFICHENO 
                  FROM AV_ATTACHMENTS A 
                       LEFT OUTER JOIN AV_WHFICHES F 
                          ON A.RECREF = F.WHFICHENO 
                 WHERE A.RECREF = F.LREF
               ) 
       END AS FICHE 
  FROM AV_ATTACHMENTS;

友情链接