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

问题描述

这是示例数据和模式

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)

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

Now I am getting following Obvious result.

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

但是我要代替它，而是要在FilteredCount列中使用@invoiceTable的总行数.

But Instead of it I want the Total Rows of @invoiceTable in FilteredCount Column.

预期结果是

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

由于@invoiceTable中只有两行，因此我希望在FilteredCount列中输入 2

As I have only two rows in @invoiceTable I want 2 in the FilteredCount Column

有什么方法可以实现这一目标，请帮我解决这个问题.

Is there any way to achieve this, Help me with this.

推荐答案

如果您只对 Id 1 和 4 感兴趣，那么您可以在 INSERT 查询

If you are only interested in Id 1 and 4, you can filter it in the INSERT query

对于 Anonymous ，您可以使用 case 语句进行

As for the Anonymous, you can use a case statement to do it

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

window function count(*) over() will gives you total rows in the query result

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)

这篇关于在表中插入数据时获取表的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！