SQL Server: строка за

У меня есть две таблицы: одна поддерживает активы, а другая - вспомогательные:

Таблица 1: assethdr

assetid
0000000002
0000000003

а также

Таблица 2: assetdet

assetsubid  assetid
0000000001  0000000002
0000000002  0000000002
0000000003  0000000002
0000000001  0000000003
0000000001  0000000109
0000000002  0000000109
0000000003  0000000109
0000000004  0000000109
0000000005  0000000109

Я сделал этот запрос:

WITH cte_assets
as
(SELECT
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY h.assetid ORDER BY f.assetsubid) = 1 
    THEN 'BA-'+LTRIM(RTRIM(h.CpnyId))+'-'+right(LTRIM(h.AssetId),5)+'-'+'0001' ELSE '' END as [Business Asset Number]
    , 'BA'+
  +'-'+RIGHT(LTRIM(RTRIM(ltrim(rtrim(f.cpnyid)))),3)
 +'-'+
 RIGHT(f.assetid,5)+'-'+RIGHT(f.assetsubid,4)as [Component Asset ID*]
FROM pssfaassets f
INNER JOIN PSSFAAssetsHdr h 
ON f.AssetId=h.assetid 
AND h.AssetId LIKE '%0000000002'
--GROUP BY h.cpnyid,h.AssetId,f.AssetSubId
)
    SELECT * FROM cte_assets
    WHERE [Business Asset Number]<>[Component Asset ID*]
    ORDER BY [Component Asset ID*],[Business Asset Number]

но я не получаю правильный результат, который должен быть:

Rownum Business Asset Number    rownum2 Component Asset ID*
1      BA-613-00002-0001            1     BA-613-00002-0002
1                                   2     BA-613-00002-0003
2       BA-607-00109-001            1     BA-607-00109-0002
2                                   2     BA-607-00109-0003
2                                   3     BA-607-00109-0004
2                                   4     BA-607-00109-0005
Источник

1 ответ

Давайте сделаем некоторые тестовые данные: я добавил cpnyid (угадывание на основе вашего вывода) для записей заголовка и подробностей.

DECLARE @assethdr TABLE
(
    assetid varchar(20),
    CpnyId varchar(20)
)
INSERT INTO @assethdr
( assetid, CpnyId )
VALUES
('0000000002', '613'),
('0000000003', '605'),
('0000000109', '607');

DECLARE @assetdet TABLE
(
    assetsubid varchar(20),
    assetid varchar(20),
    CpnyId varchar(20)
);

INSERT INTO @assetdet
( assetsubid, assetid, CpnyId )
VALUES
('0000000001', '0000000002', '613'),
('0000000002', '0000000002', '613'),
('0000000003', '0000000002', '613'),
('0000000001', '0000000003', '605'),
('0000000001', '0000000109', '607'),
('0000000002', '0000000109', '607'),
('0000000003', '0000000109', '607'),
('0000000004', '0000000109', '607'),
('0000000005', '0000000109', '607');

Запрос похож, но я добавил номер строки к выводу:

WITH cte_assets
as
(SELECT
    ROW_NUMBER() OVER (PARTITION BY h.assetid ORDER BY f.assetsubid) AS RN,
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY h.assetid ORDER BY f.assetsubid) = 2 
    THEN 'BA-'+LTRIM(RTRIM(h.CpnyId))+'-'+right(LTRIM(h.AssetId),5)+'-'+'0001' ELSE '' END as [Business Asset Number]
    , 'BA'+
  +'-'+RIGHT(LTRIM(RTRIM(ltrim(rtrim(f.cpnyid)))),3)
 +'-'+
 RIGHT(f.assetid,5)+'-'+RIGHT(f.assetsubid,4)as [Component Asset ID*]
FROM @assetdet f
INNER JOIN @assethdr h 
ON f.AssetId=h.assetid 
--AND h.AssetId LIKE '%0000000002'
--GROUP BY h.cpnyid,h.AssetId,f.AssetSubId
)

Теперь с добавлением номера строки вы пытаетесь обойти первое совпадение, которое кажется (из вашего ожидаемого вывода):

    SELECT (RN - 1) AS RowNum, [Business Asset Number], [Component Asset ID*]
    FROM cte_assets
    WHERE [RN] != 1
    ORDER BY [Component Asset ID*],[Business Asset Number]

Вот вывод:

RowNum  Business Asset Number   Component Asset ID*
1   BA-607-00109-0001   BA-607-00109-0002
2                       BA-607-00109-0003
3                       BA-607-00109-0004
4                       BA-607-00109-0005
1   BA-613-00002-0001   BA-613-00002-0002
2                       BA-613-00002-0003
Источник
Другие вопросы по тегам