admin

在表中插入数据时获取表的行数

sql

这是示例数据和模式

CREATE TABLE [dbo].[Customer]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](500) NOT NULL, [IsDelete] [bit] NOT NULL, 
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
( [Id] 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
CREATE TABLE [dbo].[Invoice]([Id] [int] IDENTITY(1,1) NOT NULL,[CustomerId] [int] NOT NULL, [Amount] [decimal](18, 2) NOT NULL,
 CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED 
( [Id] 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

Insert Into Customer (Name)
Values
('Jhon'),('-Jack'),('Mary'),('-Sam');

Insert into Invoice (CustomerId,Amount)
Values (1, 22.00),(1, 34.50),(2,20.67),(2,34.67),(3,45.89),(3,20.00),(4,30.00),(4,20.00)

ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_IsDelete]  DEFAULT ((0)) FOR [IsDelete]
GO
ALTER TABLE [dbo].[Invoice]  WITH CHECK ADD  CONSTRAINT [FK_Invoice_Invoice] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([Id])
GO
ALTER TABLE [dbo].[Invoice] CHECK CONSTRAINT [FK_Invoice_Invoice]
GO

我有以下SQL查询

DECLARE @invoiceTable TABLE (
Id INT NOT NULL,
CustName NVARCHAR(MAX) NOT NULL,
FilteredRecords INT DEFAULT 0 NOT NULL
)

Insert into @invoiceTable(
Id,
CustName,
FilteredRecords -- ****Here I need help****
)
Select 
I.Id,
R.Name,
(Select Count(Id) from @invoiceTable)
from Invoice I Inner Join Customer R on I.CustomerId = R.Id
UPDATE @invoiceTable SET CustName= 'Anonymous' WHERE CustName Like'%-%'
Select * from @invoiceTable
where Id In (1,4)

现在我正在关注明显的结果。

Id       CustName   FilteredCount  
-------- ---------- ----------------
1        John       0
4        Anonymous  0

但是相反,我需要FilteredCount列中@invoiceTable的总行数。

预期结果是

Id       CustName   FilteredCount  
-------- ---------- ----------------
1        John       2
4        Anonymous  2

因为我在@invoiceTable中只有两行,所以我希望2在FilteredCount列中

有什么方法可以实现这一目标,请帮帮我。


阅读 300

收藏
2021-07-01

共1个答案

admin

如果您仅对Id 1和感兴趣4,则可以在INSERT查询中对其进行过滤

至于Anonymous,您可以使用case语句来完成

窗口函数count(*) over()将为您提供查询结果中的总行数

Insert into @invoiceTable ( Id, CustName, FilteredRecords )
Select  Id = I.Id,
        CustName = CASE WHEN R.Name Like '%-%' then 'Anonymous' else R.Name end,
        FilteredRecords = count(*) over()
from   Invoice I 
       Inner Join Customer R on I.CustomerId = R.Id
where  I.Id In (1,4)
2021-07-01