Добавить итоговую колонку с расчетом

Мне нужно показать серийные номера для каждой строки счета. Это означает, что на одной позиции может быть несколько серийных номеров. Наряду с серийным номером должно быть количество, которое, очевидно, всегда одно. К сожалению, могут быть строки с большим количеством элементов, чем серийные номера. Это происходит, когда серийные номера не сканируются в процессе доставки. В моем выводе мне нужна дополнительная строка для этих позиций, где я показываю количество оставшихся элементов. Допустим, есть позиция с 10 предметами, и в процессе доставки сканируется только четыре. Это означало бы, что я печатаю четыре строки с серийными номерами и номером один и пятую строку без серийных номеров и количеством шесть.

Я работаю с SQL Server 2008 и предпочел бы решение без временных таблиц или CTE.

Вот пример того, что я имею в виду:

CREATE TABLE #data (doc int, pos int, qty int)
CREATE TABLE #serial (doc int, pos int, serial varchar(10))

INSERT INTO #data
SELECT 1,1,6
UNION ALL
SELECT 1,2,3
UNION ALL
SELECT 2,1,4

INSERT INTO #serial
select 1,1,'aaaaaaaaaa'
UNION ALL
select 1,1,'bbbbbbbbbb'
UNION ALL
select 1,1,'cccccccccc'
UNION ALL
select 1,1,'dddddddddd'
UNION ALL
select 1,2,'eeeeeeeeee'
UNION ALL
select 1,2,'ffffffffff'
UNION ALL
select 1,2,'gggggggggg'
UNION ALL
select 2,1,'hhhhhhhhhh'

SELECT d.doc, d.pos, s.serial, CASE WHEN s.serial IS NOT NULL THEN 1 ELSE d.qty END qty
FROM #data d
INNER JOIN #serial s ON s.doc = d.doc and s.pos = d.pos

Это желаемый результат:

doc | pos | serial     | qty
1   | 1   |'aaaaaaaaaa'| 1
1   | 1   |'bbbbbbbbbb'| 1
1   | 1   |'cccccccccc'| 1
1   | 1   |'dddddddddd'| 1
1   | 1   | NULL       | 2
1   | 2   |'eeeeeeeeee'| 1
1   | 2   |'ffffffffff'| 1
1   | 2   |'gggggggggg'| 1
2   | 1   |'hhhhhhhhhh'| 1
2   | 1   | NULL       | 3
Источник

3 ответа

Решение
select
  s.doc, s.pos, s.serial, d.qty - s.cnt qty
from
  (  select
    s.doc, s.pos, s.serial, count(*) cnt,
    case when grouping(s.doc) = 0 and grouping(s.pos) = 0 and grouping(s.serial) = 1 then 1 else 0 end grp
  from
    #serial s
  group by
    s.doc, s.pos, s.serial with cube
  having
    grouping(s.doc) = 0 and grouping(s.pos) = 0 and grouping(s.serial) = 1
    or grouping(s.doc) = 0 and grouping(s.pos) = 0 and grouping(s.serial) = 0
    ) s
  left join #data d on s.doc = d.doc and s.pos = d.pos and s.grp = 1
where
  s.grp = 0 or d.qty - s.cnt > 0
order by
  s.doc, s.pos, s.grp
Источник

Динамический подход

 SELECT d.doc, d.pos, s.serial, 1 qty
    FROM #data d
    INNER JOIN #serial s ON s.doc = d.doc and s.pos = d.pos
    UNION
    select t1.doc,t1.pos,null,t1.qty-ss from
    (
        SELECT d.doc,d.pos, SUM(1) SS , d.qty
        FROM #data d
        INNER JOIN #serial s ON s.doc = d.doc and s.pos = d.pos
        group by d.doc,d.pos,d.qty
    )t1 where SS<>qty
    Order by d.doc,d.pos,s.serial
Источник

Вы ищете это?

SELECT d.doc, d.pos, s.serial, CASE WHEN s.serial IS NOT NULL THEN 1 ELSE d.qty END qty
FROM @data d
INNER JOIN @serial s ON s.doc = d.doc AND s.pos = d.pos

UNION ALL

SELECT d.doc, d.pos, NULL serial, d.qty - s.qty
FROM @data d
INNER JOIN (
    SELECT doc, pos, count(*) AS qty
    FROM @serial
    GROUP BY doc, pos
    ) s ON s.doc = d.doc AND s.pos = d.pos
WHERE d.qty - s.qty <> 0
ORDER BY doc, pos

Выход

doc pos serial  qty
1   1   aaaaaaaaaa  1
1   1   bbbbbbbbbb  1
1   1   cccccccccc  1
1   1   dddddddddd  1
1   1   NULL    2
1   2   eeeeeeeeee  1
1   2   ffffffffff  1
1   2   gggggggggg  1
2   1   hhhhhhhhhh  1
2   1   NULL    3
Источник
Другие вопросы по тегам