zerodoc提出的问题 -dba

zerodoc

Asked: 2021-07-08 09:41:20 +0800 CST

根据表 A 在其行中的值将表 A 中的值插入或更新到表 B 的列中？

1

我有一个表，其中包含具有值的属性名称，但每一行都是具有其值的属性，

项目 ID	项目属性名称	项目属性值
405	应用	高载流量连接
405	颜色	黄色
405	环境条件	防水
405	材料	合成的
406	应用	高载流量连接
406	颜色	蓝色的
406	材料	黄铜

所以我创建了另一个表，其中包含所有可能的属性名称以将其展平，以便每一行都包含每个项目的所有值。

项目 ID	应用	颜色	环境条件	材料
405	高载流量连接	黄色	防水	合成的
406	高载流量连接	蓝色的		木头

如何获取第一个表并将每个给定 itemId 的数据插入到相应列下的上表中？

我还需要考虑有超过一百个不同的属性名称，并不是所有的项目都会有每个属性或值。我不确定我应该向哪个方向解决这个问题。

zerodoc

Asked: 2017-02-18 11:35:54 +0800 CST

在插入语句中使用时，CASE WHEN 始终评估 ELSE 值

1

此 AFTER INSERT TRIGGER 始终评估为 OrderTotal

ALTER TRIGGER [dbo].[OrderInformationInsert]
ON [dbo].[Order]
AFTER INSERT
AS
    INSERT INTO [FRTOOLS_Orders]
                (OrderNumber,
                 SubTotal)
    SELECT i.[OrderNumber],
           CASE
             WHEN SUM(oc.Amount) > 0
               THEN i.OrderTotal - SUM(oc.Amount)
             ELSE i.OrderTotal
           END AS subTotal
    FROM   inserted i
           LEFT JOIN [OrderCharge] oc
             ON i.OrderID = oc.OrderID
    WHERE  NOT EXISTS(SELECT '1'
                      FROM   [FRTOOLS_Orders]
                      WHERE  OrderNumber = i.[OrderNumber])
    GROUP  BY i.[OrderNumber],
              i.[OrderTotal]

当我运行 select 语句时。我得到了预期的信息？

SELECT o.[OrderNumber],
       CASE
         WHEN SUM(oc.Amount) > 0
           THEN o.OrderTotal - SUM(oc.Amount)
         ELSE o.OrderTotal
       END AS subTotal
FROM   [Order] o
       LEFT JOIN [OrderCharge] oc
         ON o.OrderID = oc.OrderID
GROUP  BY o.[OrderNumber],
          o.[OrderTotal]

如果 OrderCharge 表还没有值以及 FROM INSERTED 是如何工作的，是否会出现这种情况，我是否应该朝不同的方向前进。

OrderCharge 表编写脚本：

CREATE TABLE [dbo].[OrderCharge](
[OrderChargeID] [bigint] IDENTITY(1021,1000) NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[OrderID] [bigint] NOT NULL,
[Type] [nvarchar](50) NOT NULL,
[Description] [nvarchar](255) NOT NULL,
[Amount] [money] NOT NULL,
CONSTRAINT [PK_OrderCharge] PRIMARY KEY CLUSTERED 
(
[OrderChargeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[OrderCharge]  WITH CHECK ADD  CONSTRAINT [FK_OrderCharge_Order] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Order] ([OrderID])
GO

ALTER TABLE [dbo].[OrderCharge] CHECK CONSTRAINT [FK_OrderCharge_Order]
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'OrderCharge', @level2type=N'COLUMN',@level2name=N'Amount'
GO

我不确定，但 Order Table 我很长？顺便说一句，这不是我们的数据库，这部分是 Shipworks 软件，是的，他们将表命名为 sql 关键字，这就是我们必须操作该表的原因。

USE [ShipWorks]
GO

/****** Object:  Table [dbo].[Order]    Script Date: 2/18/2017 11:23:20 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Order](
    [OrderID] [bigint] IDENTITY(1006,1000) NOT NULL,
    [RowVersion] [timestamp] NOT NULL,
    [StoreID] [bigint] NOT NULL,
    [CustomerID] [bigint] NOT NULL,
    [OrderNumber] [bigint] NOT NULL,
    [OrderNumberComplete] [nvarchar](50) NOT NULL,
    [OrderDate] [datetime] NOT NULL,
    [OrderTotal] [money] NOT NULL,
    [LocalStatus] [nvarchar](100) NOT NULL,
    [IsManual] [bit] NOT NULL,
    [OnlineLastModified] [datetime2](7) NOT NULL,
    [OnlineCustomerID] [sql_variant] NULL,
    [OnlineStatus] [nvarchar](100) NOT NULL,
    [OnlineStatusCode] [sql_variant] NULL,
    [RequestedShipping] [nvarchar](50) NOT NULL,
    [BillFirstName] [nvarchar](30) NOT NULL,
    [BillMiddleName] [nvarchar](30) NOT NULL,
    [BillLastName] [nvarchar](30) NOT NULL,
    [BillCompany] [nvarchar](60) NOT NULL,
    [BillStreet1] [nvarchar](60) NOT NULL,
    [BillStreet2] [nvarchar](60) NOT NULL,
    [BillStreet3] [nvarchar](60) NOT NULL,
    [BillCity] [nvarchar](50) NOT NULL,
    [BillStateProvCode] [nvarchar](50) NOT NULL,
    [BillPostalCode] [nvarchar](20) NOT NULL,
    [BillCountryCode] [nvarchar](50) NOT NULL,
    [BillPhone] [nvarchar](25) NOT NULL,
    [BillFax] [nvarchar](35) NOT NULL,
    [BillEmail] [nvarchar](100) NOT NULL,
    [BillWebsite] [nvarchar](50) NOT NULL,
    [BillAddressValidationSuggestionCount] [int] NOT NULL,
    [BillAddressValidationStatus] [int] NOT NULL,
    [BillAddressValidationError] [nvarchar](300) NOT NULL,
    [BillResidentialStatus] [int] NOT NULL,
    [BillPOBox] [int] NOT NULL,
    [BillUSTerritory] [int] NOT NULL,
    [BillMilitaryAddress] [int] NOT NULL,
    [ShipFirstName] [nvarchar](30) NOT NULL,
    [ShipMiddleName] [nvarchar](30) NOT NULL,
    [ShipLastName] [nvarchar](30) NOT NULL,
    [ShipCompany] [nvarchar](60) NOT NULL,
    [ShipStreet1] [nvarchar](60) NOT NULL,
    [ShipStreet2] [nvarchar](60) NOT NULL,
    [ShipStreet3] [nvarchar](60) NOT NULL,
    [ShipCity] [nvarchar](50) NOT NULL,
    [ShipStateProvCode] [nvarchar](50) NOT NULL,
    [ShipPostalCode] [nvarchar](20) NOT NULL,
    [ShipCountryCode] [nvarchar](50) NOT NULL,
    [ShipPhone] [nvarchar](25) NOT NULL,
    [ShipFax] [nvarchar](35) NOT NULL,
    [ShipEmail] [nvarchar](100) NOT NULL,
    [ShipWebsite] [nvarchar](50) NOT NULL,
    [ShipAddressValidationSuggestionCount] [int] NOT NULL,
    [ShipAddressValidationStatus] [int] NOT NULL,
    [ShipAddressValidationError] [nvarchar](300) NOT NULL,
    [ShipResidentialStatus] [int] NOT NULL,
    [ShipPOBox] [int] NOT NULL,
    [ShipUSTerritory] [int] NOT NULL,
    [ShipMilitaryAddress] [int] NOT NULL,
    [RollupItemCount] [int] NOT NULL,
    [RollupItemName] [nvarchar](300) NULL,
    [RollupItemCode] [nvarchar](300) NULL,
    [RollupItemSKU] [nvarchar](100) NULL,
    [RollupItemLocation] [nvarchar](255) NULL,
    [RollupItemQuantity] [float] NULL,
    [RollupItemTotalWeight] [float] NOT NULL,
    [RollupNoteCount] [int] NOT NULL,
    [BillNameParseStatus] [int] NOT NULL,
    [BillUnparsedName] [nvarchar](100) NOT NULL,
    [ShipNameParseStatus] [int] NOT NULL,
    [ShipUnparsedName] [nvarchar](100) NOT NULL,
    [ShipSenseHashKey] [nvarchar](64) NOT NULL,
    [ShipSenseRecognitionStatus] [int] NOT NULL,
    [ShipAddressType] [int] NOT NULL,
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_Customer] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customer] ([CustomerID])
GO

ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Customer]
GO

ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_Order_Store] FOREIGN KEY([StoreID])
REFERENCES [dbo].[Store] ([StoreID])
GO

ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_Order_Store]
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'StoreID'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'Store' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'StoreID'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'CustomerID'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'Customer' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'CustomerID'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OrderNumber'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'Order Number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OrderNumberComplete'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OrderTotal'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OnlineCustomerID'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'OnlineStatusCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillStateProvCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'BillState' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillStateProvCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'6' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillCountryCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'BillCountry' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillCountryCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'5' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipStateProvCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'ShipState' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipStateProvCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'6' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipCountryCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditName', @value=N'ShipCountry' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipCountryCode'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'BillNameParseStatus'
GO

EXEC sys.sp_addextendedproperty @name=N'AuditFormat', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Order', @level2type=N'COLUMN',@level2name=N'ShipNameParseStatus'
GO

zerodoc

Asked: 2014-07-25 08:47:08 +0800 CST

将选择查询中的 csv 列拆分为多个列

7

我有一个存储过程

SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, 
    CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1)
            THEN MAX([ShipWorks].[dbo].[OrderItem].[Location]) 
         ELSE MAX([Picklist].[dbo].[ItemData].[InventoryLocation])
    END AS Location, 
SUM([ShipWorks].[dbo].[OrderItem].[Quantity]) AS Quantity, 
MAX(LEFT([ShipWorks].[dbo].[Store].[StoreName], 1)) AS Store
FROM [ShipWorks].[dbo].[OrderItem] 
INNER JOIN [ShipWorks].[dbo].[Order] ON [ShipWorks].[dbo].[OrderItem].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID] 
INNER JOIN [ShipWorks].[dbo].[Store] ON [ShipWorks].[dbo].[Order].[StoreID] = [ShipWorks].[dbo].[Store].[StoreID] 
LEFT JOIN [ShipWorks].[dbo].[AmazonOrder] ON [ShipWorks].[dbo].[AmazonOrder].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID]
LEFT JOIN [Picklist].[dbo].[ItemData] ON [Picklist].[dbo].[ItemData].[InventoryNumber] = [ShipWorks].[dbo].[OrderItem].[Code] OR
          [Picklist].[dbo].[ItemData].[MediaCreator] = [OrderItem].[Code]
WHERE [ShipWorks].[dbo] [Order].[LocalStatus] = 'Recently Downloaded' AND ([ShipWorks].[dbo].[AmazonOrder].[FulfillmentChannel] = 2 OR [ShipWorks].[dbo].[Store].[StoreName] <> 'Amazon')
GROUP BY [ShipWorks].[dbo].[OrderItem].[Code]
ORDER BY Location

返回这个：

Code              Location                                  Quantity    Store
L698-W-EA          NULL                                      2           A
L82009-EA          A1K2, A1N2, C4Y3, CBP2                    2           A
L80401-A-EA        A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20      17          A
CWD2132W-BOX-25PK  A-AISLE                                   1           M
GM22660003-EA      B1K2                                      1           M

我想将 Location 列拆分为多个列。

Code               loc1  loc2  loc3  loc4  loc5 loc6        Quantity    Store
L698-W-EA          NULL                                      2           A
L82009-EA          A1K2, A1N2, C4Y3, CBP2                    2           A
L80401-A-EA        A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20      17          A
CWD2132W-BOX-25PK  A-AI                                      1           M
GM22660003-EA      B1K2                                      1           M

我找到了这个答案，但那是针对postgresql的。也是这个，但它返回一个包含多行的表，您可以选择前 1 个，但我想要所有结果，我不确定如何在存储过程中使用 tvf Split(string, ',') 来获取我想要的结果集？

此选择语句导致错误： “无法对包含聚合或子查询的表达式执行聚合函数。”

SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, 
    CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1)
            THEN MAX((SELECT TOP 1 i.* FROM dbo.Split([ShipWorks].[dbo].[OrderItem].[Location], ',') as i)) 
         ELSE MAX((SELECT TOP 1 i.* FROM dbo.Split([ShipWorks].[dbo].[OrderItem].[Location], ',') as i))
    END AS Location, 
SUM([ShipWorks].[dbo].[OrderItem].[Quantity]) AS Quantity, 
MAX(LEFT([ShipWorks].[dbo].[Store].[StoreName], 1)) AS Store
FROM [ShipWorks].[dbo].[OrderItem] 
INNER JOIN [ShipWorks].[dbo].[Order] ON [ShipWorks].[dbo].[OrderItem].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID] 
INNER JOIN [ShipWorks].[dbo].[Store] ON [ShipWorks].[dbo].[Order].[StoreID] = [ShipWorks].[dbo].[Store].[StoreID] 
LEFT JOIN [ShipWorks].[dbo].[AmazonOrder] ON [ShipWorks].[dbo].[AmazonOrder].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID]
LEFT JOIN [Picklist].[dbo].[ItemData] ON [Picklist].[dbo].[ItemData].[InventoryNumber] = [ShipWorks].[dbo].[OrderItem].[Code] OR
          [Picklist].[dbo].[ItemData].[MediaCreator] = [OrderItem].[Code]
WHERE [Order].[LocalStatus] = 'Recently Downloaded' AND ([ShipWorks].[dbo].[AmazonOrder].[FulfillmentChannel] = 2 OR [ShipWorks].[dbo].[Store].[StoreName] <> 'Amazon')
GROUP BY [ShipWorks].[dbo].[OrderItem].[Code]
ORDER BY Location

zerodoc

Asked: 2014-05-24 11:04:16 +0800 CST

如果条件为真，存储过程评估列执行另一个存储过程

2

我有一个使用 Windows 任务管理器执行的存储过程，如果条件为真，我想添加一个执行另一个存储过程的条件语句。我正在考虑一个 case 语句，但我在 CASE 附近得到了一个无效的语法，并且根据这个响应，在 select 或 case 子句中执行存储过程是不允许的，如果满足条件，我将如何在表上执行存储过程。

这是存储过程：

 SET NOCOUNT ON;

 UPDATE Supply
 SET [Quantity] = [Quantity] - [SupplyHalfLife] 

 UPDATE Supply  
 SET SupplyAlert = CASE WHEN Quantity <= SupplyQuantityAlert THEN 'Y' ELSE 'N' END

这就是我要添加的内容：

SELECT a.SupplyName
CASE a.SupplyAlert
WHEN 'Y' THEN dbo.sp_SendMail @body = a.SupplyName END
FROM Supply a

它检查列的警报状态“Y”并执行存储过程。

我最终使用的代码：

DECLARE @name varchar(255)

DECLARE cur CURSOR LOCAL FOR
SELECT SupplyName FROM Supply WHERE SupplyAlert = 'Y'

OPEN cur

FETCH NEXT FROM cur INTO @name

WHILE @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    EXEC dbo.SendMail @name

    FETCH NEXT FROM cur INTO @name
END

CLOSE cur
DEALLOCATE cur

关联

根据表 A 在其行中的值将表 A 中的值插入或更新到表 B 的列中？

在插入语句中使用时，CASE WHEN 始终评估 ELSE 值

将选择查询中的 csv 列拆分为多个列

如果条件为真，存储过程评估列执行另一个存储过程

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

zerodoc's questions