如何将字段从项目表添加到全部合并到表项目其他代码

Question

查询应该收集原始项目(从Items表中(和替代项目(从ItemOtherCode表中(：

SELECT ItemCode,SelPrice1Default, ItemAraName, ItemLatName, 
       ItemNotes, UnitCode, ItemClassCode, ItemGroupCode, 
       ItemSubGroupCode, TaxSet, ExpireDate, ItemType, ItemEquation, 
       ItemDim, NotActive, UnitCode1
  FROM dbo.Items 
 where ItemCode= 10003
 union all
 select OtherCode,BarcodeUnitPrice 
   from ItemOtherCode 
  where ItemCode= 10003

但是当我对Items表和ItemOtherCode表进行此union all时，出现错误：

使用 UNION、INTERSECT 或 EXCEPT 运算符组合的所有查询必须具有 目标列表中的表达式数相等。

如何解决这个问题？

DDL

项目表

CREATE TABLE [dbo].[Items](
    [ItemCode] [nvarchar](20) NOT NULL,
    [ItemAraName] [nvarchar](100) NULL,
    [ItemLatName] [nvarchar](100) NULL,
    [ItemNotes] [nvarchar](100) NULL,
    [UnitCode] [int] NOT NULL,
    [ItemClassCode] [int] NULL,
    [ItemGroupCode] [int] NULL,
    [ItemSubGroupCode] [int] NULL,
    [TaxSet] [float] NOT NULL,
    [ExpireDate] [bit] NOT NULL,
    [ItemType] [int] NULL,
    [ItemEquation] [nvarchar](50) NULL,
    [ItemDim] [int] NULL,
    [NotActive] [bit] NOT NULL,
    [UnitCode1] [int] NULL,
    [BuyPriceDefault] [float] NOT NULL,
    [PriceTypeCode] [int] NULL,
    [SelPrice1Default] [float] NOT NULL,
    [SelPrice2Default] [float] NOT NULL,
 CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED 
(
    [ItemCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

项其他代码表

CREATE TABLE [dbo].[ItemOtherCode](
    [ItemCode] [nvarchar](20) NOT NULL,
    [OtherCode] [nvarchar](20) NOT NULL,
    [BarcodeUnitPrice] [float] NULL,
 CONSTRAINT [PK_ItemOtherCode] PRIMARY KEY CLUSTERED 
(
    [ItemCode] ASC,
    [OtherCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

它们之间的关系：

SELECT *
FROM dbo.Items i 
INNER JOIN dbo.ItemOtherCode o ON o.ItemCode = i.ItemCode

Answer 1

UNION 在两个查询中需要相同的字段计数和相同的列名：

SELECT FIELD1, FIELD2, FIELD3 FIELD4
FROM   TABLE1
UNION ALL
SELECT OTHER_FIELD1 AS FIELD1, OTHER_FIELD2 AS FIELD2, '' AS FIELD3, 0 AS FIELD4
FROM   TABLE2

Answer 2

我推测[OtherCode]也应该指父表Items。

您尝试过的内容：

SELECT OtherCode,BarcodeUnitPrice 
FROM ItemOtherCode 
WHERE ItemCode= 10003

或者与您在FK解释的问题中显示的join相同：

SELECT *
FROM dbo.Items i 
INNER JOIN dbo.ItemOtherCode o ON o.ItemCode = i.ItemCode
WHERE i.ItemCode = 10003

现在您已经找到了10003的所有替代项目。但是您需要有关这些项目的信息。有关项目的信息存储在哪里？当然Items表中。因此，我们需要再次访问那里，但使用不同的 ItemCode 值：

SELECT io.*
FROM dbo.Items i 
INNER JOIN dbo.ItemOtherCode o ON o.ItemCode = i.ItemCode
INNER JOIN dbo.Items io on io.ItemCode = o.OtherCode --<<<
WHERE i.ItemCode = 10003

现在，我们Items表中的所有字段都用于10003的所有替代项。现在可以做union。

SELECT ItemCode,SelPrice1Default, ItemAraName, ItemLatName, 
       ItemNotes, UnitCode, ItemClassCode, ItemGroupCode, 
       ItemSubGroupCode, TaxSet, ExpireDate, ItemType, ItemEquation, 
       ItemDim, NotActive, UnitCode1
  FROM dbo.Items i
 WHERE i.ItemCode = 10003
 UNION ALL
SELECT ItemCode,SelPrice1Default, ItemAraName, ItemLatName, 
       ItemNotes, UnitCode, ItemClassCode, ItemGroupCode, 
       ItemSubGroupCode, TaxSet, ExpireDate, ItemType, ItemEquation, 
       ItemDim, NotActive, UnitCode1
  FROM dbo.ItemOtherCode o
 INNER JOIN dbo.Items io on io.ItemCode = o.OtherCode
 WHERE o.ItemCode = 10003

不知道BarcodeUnitPrice应该是什么意思，所以不能说该怎么处理它。