SQL Server不存在时如何插入？

Question

我有两个表，一个叫做发票，另一个叫做记录。

CREATE TABLE Invoices
(
    InvoiceNum INT NOT NULL,
    Amount DECIMAL,
    RecordPK UNIQUEIDENTIFIER NOT NULL
)

CREATE TABLE Records(
    RecordPK UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    StartNum INT NOT NULL,
    NextNum INT NOT NULL,
    MaxNum INT NOT NULL,
    InvPrefix VARCHAR(2) NOT NULL
)

记录表将记录发票起始编号、我们已创建的发票数量(NextNum)以及我们可以创建的发票数量(MaxNum)。

例如，假设我们在两个表中有几条记录。

发票表：

InvoiceNum    Amount    RecordPk
1             19.00     EDFA0541-5583-4CDD-BDFF-21D6F6504522
2             50.00     EDFA0541-5583-4CDD-BDFF-21D6F6504522
3             3.00      EDFA0541-5583-4CDD-BDFF-21D6F6504522
10            1.00      D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
11            99.00     D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
12            13.00     D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9

记录表：

RecordPk                                StartNum    NextNum    MaxNum    Prefix
EDFA0541-5583-4CDD-BDFF-21D6F6504522    1           4          10        AA
D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9    10          13         14        AA

我的问题是，当我搜索前缀为AA的发票表时，如何得到如下结果，InvoiceNum应该达到MaxNum，不存在的行的金额和RecordPK应该留空，备注列应该填充空白。

InvoiceNum    Amount    RecordPk                                Remark
1             19.00     EDFA0541-5583-4CDD-BDFF-21D6F6504522
2             50.00     EDFA0541-5583-4CDD-BDFF-21D6F6504522
3             3.00      EDFA0541-5583-4CDD-BDFF-21D6F6504522
4                                                               Blank
5                                                               Blank
6                                                               Blank
7                                                               Blank
8                                                               Blank
9                                                               Blank
10            1.00      D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
11            99.00     D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
12            13.00     D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9
13                                                              Blank
14                                                              Blank

Answer 1

您需要生成一个包含数字的表，以涵盖所需的数字范围(对于Records表中的每一行，从StartNum到MaxNum)。例如，您可以通过从一些具有足够行的现有表中进行选择并使用ROW_NUMBER窗口函数来完成此操作。然后过滤此序列，使其仅包含所需的数字。左连接Invoices表以显示对应发票的数据，并使用IIF函数检查是否存在具有此编号的发票。

declare @Invoices table(InvoiceNum INT NOT NULL, Amount DECIMAL, RecordPK UNIQUEIDENTIFIER NOT NULL)
declare @Records table(RecordPK UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, StartNum INT NOT NULL, NextNum INT NOT NULL, MaxNum INT NOT NULL, InvPrefix VARCHAR(2) NOT NULL)

insert into @Invoices(InvoiceNum, Amount, RecordPk) values
(1 ,            19.00,    'EDFA0541-5583-4CDD-BDFF-21D6F6504522'),
(2 ,            50.00,    'EDFA0541-5583-4CDD-BDFF-21D6F6504522'),
(3 ,            3.00 ,    'EDFA0541-5583-4CDD-BDFF-21D6F6504522'),
(10,            1.00 ,    'D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9'),
(11,            99.00,    'D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9'),
(12,            13.00,    'D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9')

insert into @Records(RecordPk, StartNum, NextNum, MaxNum, InvPrefix) values
('EDFA0541-5583-4CDD-BDFF-21D6F6504522',    1 ,          4 ,         10,        'AA'),
('D64EFF0E-65D5-467E-8C82-BFBB6A24AAC9',    10,          13,         14,        'AA')

;with numbers as (select ROW_NUMBER() over(order by object_id) as No from sys.objects)
select
    n.No as InvoiceNum
    , inv.Amount
    , inv.RecordPK
    , IIF(inv.InvoiceNum is null, 'Blank', null) as Remark
from numbers n
left join @Invoices inv on n.No = inv.InvoiceNum
where exists(select * from @Records r where r.StartNum <= n.No and n.No <= r.MaxNum)

Answer 2

你需要一个左连接

SELECT I.*,
       CASE WHEN I.InvoiceNum IS NULL THEN 'Blank' END Remark
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14)) RC (InvoiceNum)
LEFT JOIN Invoices I
     ON RC.InvoiceNum = I.InvoiceNum;

值1是StartNum，14是最大MaxNum。我使用的值是因为数字是已知的，你可以使用一个RecursiveCTE来生成丢失的InvoiceNum，然后左加入你的表的CTE。

Answer 3

@Andrey Nikolov已经报道过了，但是我在过去的15分钟里一直在做这件事，所以我想我还是把它贴出来吧。

本质上，应该使用一个中间表来计算您没有的值，然后在我的答案版本中，我使用了一个联合查询来生成“空白”值。为简洁起见，我没有包含唯一标识符，但应用程序是相同的。

if OBJECT_ID('tempdb..#invoice') is not null drop table #invoice;
if OBJECT_ID('tempdb..#rowcount') is not null drop table #rowcount;

create table #invoice
    (
        invoicenum int,
        amount decimal
    );


insert into #invoice (invoicenum, amount)
values
(1, 19.00),
(2, 50.00),
(3, 3.00),
(10, 1.00),
(11, 99.00),
(12, 13.00);


create table #rowcount

    (
        rownumber int 
    );

declare @max int = 1;

select @max=count(*) from #invoice;

declare @runs int = 1;

while @runs<=@max
begin
insert into #rowcount (rownumber)
values (@runs);
select @runs=@runs+1;
end


select invoicenum, cast(amount as nvarchar(25)) as amount from #invoice
union
select rownumber, 'BLANK' from #rowcount r left join #invoice i on 
r.rownumber=i.invoicenum where i.invoicenum is null
order by invoicenum;

drop table #invoice, #rowcount;