Во-первых, я ни в коем случае не гуру SQL, хотя и балуюсь ...
Моя проблема заключается в следующем; У меня есть большая база данных, содержащая таблицу для
iteminfo (основные описания элементов) (. [itemno])
solditems (информация о продаже товара) (. [itemno]. [ratecode]. [qty]. [price])
ставки (в основном цена продажи товаров) (. [код ставки])
Мне нужно создать отчет (отчеты о кристаллах), который показывает для каждого предмета, сколько раз он был продан по каждой ставке, и общую стоимость для этого. Проблема, с которой я сталкиваюсь, заключается в том, что таблица [ставок] может иметь разный размер для разных баз данных, и может быть добавлено больше ставок, а неиспользованные ставки удалены.
Я создал следующий SQL-запрос, который получает эту информацию, но если в базе данных есть 10 разных скоростей, мне нужно скопировать код ниже 10 раз, что делает запрос очень беспорядочным, и, конечно, он не учитывает учетная запись 11-й ставки и начинает возвращать нечетные значения, если ставка удалена (или, что еще хуже, если ставка № 5 удаляется, а 6 становится 5, 7 становится 6 и т. д.).
Примечание. S.itemno относится к основному запросу и является номером элемента для конкретного запрашиваемого элемента.
(select count(solditems.ratecode)
from solditems
where solditems.itemno = s.itemno
and solditems.ratecode = (
select [ratecode]
from (
select row_number() over (order by [ratecode]) as rownumber, *
from dbo.rates
) as mytable
where rownumber = '**1**'
)
) as ratqty**1**,
(select sum(solditems.qty * solditems.price) from solditems
where solditems.itemno = s.itemno and solditems.ratecode = (select
[ratecode] from (select row_number() over (order by [ratecode]) as
rownumber, * from dbo.rates) as mytable where rownumber = '**1**'))
as rateval**1**,
...
Если бы я использовал такой язык программирования, как C, я бы просто написал цикл for, например (Псевдокод): int maxi = count (rates.ratecode) для i = 0 и i! = Maxi {делать что-то для rownumber i} i ++.
В1: Есть ли способ построить такой цикл for в SQL (он должен быть совместим с отчетами Crystal, поэтому чем проще, тем лучше).
Q2: Как мне избежать проблем, если ставка будет удалена, а последующие столы переместятся на одну позицию (6 становится 5, 7 становится 6 и т. д.).
Бонус Q: Вышеупомянутый код не очень элегантен, есть ли способ попроще?
Запрошенный образец вывода:
Товар № 1 был продан 5 раз по 1000 евро каждый по коду тарифа 1, 1 раз по 100 евро по коду тарифа 2 товар № 2 был продан 6 раз по 500 евро каждый по тарифному коду 1, и т.п.
Что вы меняете в этом коде, когда копируете его на другую «ставку»? Значение фильтрации для rownumber
?
Похоже, что GROUP BY
потенциально может решить эту проблему, но без данных об этом сложно сказать. Можете ли вы опубликовать примеры данных и ожидаемый результат?
Добавлено в исходный пост.
Чтобы избежать циклов и копирования-вставки, вам понадобится dynamic pivot
для двух таких агрегированных значений:
DECLARE
@cmd NVARCHAR(MAX) = N'',
@qty_cols NVARCHAR(MAX) = N'',
@val_cols NVARCHAR(MAX) = N'',
@n int = 0
-- this can be done in any other manner like via spt_values or FOR XML and so on
SELECT TOP 100 PERCENT
@n += 1,
@qty_cols += ', [rateqty-' + CAST(@n AS VARCHAR(10)) + ']',
@val_cols += ', [rateval-' + CAST(@n AS VARCHAR(10)) + ']',
@cmd += ', MAX([rateqty-' + CAST(@n AS VARCHAR(10)) + ']) as [rateqty-' + CAST(@n AS VARCHAR(10)) + ']'
+ ', MAX([rateval-' + CAST(@n AS VARCHAR(10)) + ']) as [rateval-' + CAST(@n AS VARCHAR(10)) + ']'
FROM #rates r
ORDER BY r.ratecode
set @cmd = 'SELECT ' + STUFF(@cmd, 1, 2, '') + '
from
(
select
''rateqty-'' + CAST(rn AS VARCHAR(10)) ratecode_qty,
''rateval-'' + CAST(rn AS VARCHAR(10)) ratecode_val,
rateqty, rateval
from (
select ratecode, rateqty, rateval, ROW_NUMBER() OVER(ORDER BY r.ratecode) rn
from #rates r
) r
) r
pivot (max(r.rateqty) for ratecode_qty in (' + STUFF(@qty_cols, 1, 2, '') + ' )) p1
pivot (max(p1.rateval) for ratecode_val in (' + STUFF(@val_cols, 1, 2, '') + ' )) p2
order by 1'
exec(@cmd)
;
Для этих образцов данных:
insert into rates(ratecode)
values (1), (2), (3)
insert into solditems(ratecode, qty, price)
values
(1, 5, 22),
(1, 2, 22),
(3, 1, 33)
вывод будет:
| rateqty-1 | rateval-1 | rateqty-2 | rateval-2 | rateqty-3 | rateval-3 |
|-----------|-----------|-----------|-----------|-----------|-----------|
| 2 | 154 | 0 | 0 | 1 | 33 |
Полный исходник здесь: http://sqlfiddle.com/#!18/6f390/42
Что касается вопроса об упрощении ваших подзапросов в отчете, где участвует больше исходных таблиц, то я предполагаю (поскольку вы показали только часть кода) ratecodes
должен быть CROSS JOIN
подключен к внешнему запросу (что звучит немного пугающе) или присоединен к solditems
напрямую и агрегировано (а потом повернуто) следующим образом:
SELECT
...
FROM ... <outer query>
CROSS APPLY
(
select
r.ratecode,
count(1) rateqty, -- i don't quite understand what is this supposed to be; number of transactions?
sum(si.qty * si.price) rateval
from solditems si
INNER JOIN dbo.rates r
ON si.ratecode = r.ratecode
where si.itemno = s.itemno
group by r.ratecode
) s
upd: после публикации этого подзапроса я понял, что dbo.rates
нигде, кроме ROWNUMBER
, не используется. Итак, если вы выполняете поворот, здесь не требуется присоединение к rates
:
SELECT
...
FROM ... <outer query>
CROSS APPLY
(
select
si.ratecode,
count(1) rateqty,
sum(si.qty * si.price) rateval
from solditems si
where si.itemno = s.itemno
group by si.ratecode
) s
Это великолепно. Мне потребуется немного времени, чтобы понять, как это работает, чтобы я мог его реализовать, но он дает мне именно то, что я хочу. Спасибо!
Пожалуйста, предоставьте образцы данных и ожидаемый результат