SQL Server yo'q bo'lsa qanday qo'shiladi?

Menda ikkita jadval bor, ulardan biri "Xarajatlarni", boshqasi esa "Records" deb ataladi.

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
)

Jadvallar jadvalida fakturaning boshlang'ich raqamini, biz yaratgan qancha naqdnomani (NextNum) va biz yaratadigan qancha fakturani (MaxNum) yozib qo'yadi.

Misol uchun, ikkita jadvalda bir nechta yozuvlar mavjud.

Faktura jadvali:

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

Yozuvlar jadvali:

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

Mening oldimga kelsak, Prefiks AA bilan hisob-faktura jadvalini qidirib topsam, natijada quyida keltirilgan natijani qanday olishim mumkin, InvoiceNum MaxNum-ga ega bo'lishi kerak, miqdori va RecordPK-ning mavjud bo'lmagan qatorlari bo'sh qoldirilishi kerak.

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
0

5 javoblar

Sizga LEFT JOIN kerak

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)) RC (InvoiceNum)
LEFT JOIN Invoices I
     ON RC.InvoiceNum = I.InvoiceNum;

The value 1 is the MIN InvoiceNum and 12 is the MAX InvoiceNum

Demo

0
qo'shib qo'ydi

Sizga kerakli raqamlar diapazoni ( Yozuvlar jadvalidagi StartNum dan MaxNum qatoriga o'tish uchun raqamlar bilan jadval yaratish kerak) ). Masalan, ba'zi jadvallardan etarli miqdorda tanlab, va ROW_NUMBER oyna funksiyasi. Keyin kerakli raqamlarni kiritish uchun ushbu tartibni sinab ko'ring. Tegishli hisob-fakturaga oid ma'lumotlarni ko'rsatish uchun Faturalar jadvali chapga qo'shiling va IIF funktsiyasi tekshiriladi.

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)
0
qo'shib qo'ydi

Sizga kerakli raqamlar diapazoni ( Yozuvlar jadvalidagi StartNum dan MaxNum qatoriga o'tish uchun raqamlar bilan jadval yaratish kerak) ). Masalan, ba'zi jadvallardan etarli miqdorda tanlab, va ROW_NUMBER oyna funksiyasi. Keyin kerakli raqamlarni kiritish uchun ushbu tartibni sinab ko'ring. Tegishli hisob-fakturaga oid ma'lumotlarni ko'rsatish uchun Faturalar jadvali chapga qo'shiling va IIF funktsiyasi tekshiriladi.

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)
0
qo'shib qo'ydi

@Andrey Nikolov uni yopdi, ammo men so'nggi 15 daqiqada bu ish ustida ishlayapman, shuning uchun hammasini yozib qo'yishimni o'ylardim.

Aslida sizda mavjud bo'lmagan qadriyatlarni hisoblash uchun vositachi jadval foydalanish kerak, keyin bu javobning versiyasida "bo'sh" qiymatini yaratish uchun birlashma so'rovi ishlatilgan. Men qisqartirish uchun noyob identifikatorni kiritmadim, ammo dastur bir xil.

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<[email protected]
begin
insert into #rowcount (rownumber)
values (@runs);
select @[email protected]+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;
0
qo'shib qo'ydi

@Andrey Nikolov uni yopdi, ammo men so'nggi 15 daqiqada bu ish ustida ishlayapman, shuning uchun hammasini yozib qo'yishimni o'ylardim.

Aslida sizda mavjud bo'lmagan qadriyatlarni hisoblash uchun vositachi jadval foydalanish kerak, keyin bu javobning versiyasida "bo'sh" qiymatini yaratish uchun birlashma so'rovi ishlatilgan. Men qisqartirish uchun noyob identifikatorni kiritmadim, ammo dastur bir xil.

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<[email protected]
begin
insert into #rowcount (rownumber)
values (@runs);
select @[email protected]+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;
0
qo'shib qo'ydi