#sql #pivot #subquery #pivot-table #sql-server-2019
Вопрос:
Я работаю над SQLServer 2019, и у меня есть таблица заказов. Таблица заказов содержит столбцы идентификатор заказа, идентификатор пользователя, страна, сайт, количество, цена. Мне нужна помощь в написании запроса. Вы можете ознакомиться ниже с подробностями.
Вопрос: Покажите мне, сколько пользователь заказал с 2010 года по настоящее время один, два и т.д. В 5 полосах ([1], [2], [3], [4-10], [10-100]) в зависимости от страны
Пример Результата:
Country 1 2 3 4-10 10-100
---------------------------------------------------------
US 0 0 3 4 5
GB 10 10 8 50 60
NL 20 20 20 100 30
....
Мой вопрос:
Я использовал сводную таблицу от 1 до 3, и у меня правильный результат. Но я не мог записать диапазоны от десяти до четырех и от десяти до ста в сводной таблице.
Я запустил запрос ниже;
select * from (
SELECT Country,
count(*) as total,
count as totalpay
FROM [CRM].[dbo].[Orders]
where date like ' 10%'
group by Country,count
) countrytotalcnt
pivot
(
sum(total) for totalpay in ([1],[2],[3],[4-10],[10-100])
)countrytotal;
I have error for below;
Msg 8114, Level 16, State 1, Line 24
Error converting data type nvarchar to int.
Msg 473, Level 16, State 1, Line 24
The incorrect value "4-10" is supplied in the PIVOT operator.
Completion time: 2021-10-13T13:55:47.1067875 03:00
Комментарии:
1. Зачем нам нужно показывать вам, сколько пользователь заказал с 2010 года по настоящее время? Конечно, это вопрос, на который вам нужно ответить, а не нам (для меня это звучит как домашнее задание). Что не так с решением, которое у вас есть? О чем вы на самом деле нас спрашиваете? (Нет, просить нас сделать вашу домашнюю работу-это не нормально.)
2. Не является домашним заданием. Это технические вопросы, и мой менеджер задал их и ждет моего ответа. Я ищу подсказки, я сам уже задал девять из десяти вопросов. Я не знаю только небольшую часть из 1 из них, я просил вас направлять меня. Спасибо.
3. Ладно, значит, это твоя работа, но о чем ты просишь? Мы здесь не для того, чтобы делать за вас вашу работу. С чем у вас возникли трудности? Как насчет того, что то, что вы сделали, не работает? Смотрите мой первоначальный комментарий, он все еще применяется.
4. «Я хочу» — это не вопрос… Что значит «не работает»? Вы получаете сообщение об ошибке? Неожиданные результаты? Нежелательное поведение? Вызывает возгорание вашего сервера? Помогите нам помочь вам.
5. Я не могу задавать вопросы, от меня ожидают, что я сам их решу.
Ответ №1:
Как я уже упоминал в комментариях, используйте здесь условную агрегацию, она гораздо более универсальна, чем PIVOT оператор.
Кроме того, вы WHERE допустите ошибку, поскольку ' 10%' ее невозможно преобразовать в тип данных даты и времени. Если WHERE «работает», проблема заключается в вашем дизайне, и вы сохраняете значения даты и времени в виде строкового типа данных; это фатальный недостаток, который необходимо исправить. varchar-это НЕ один размер, подходящий для всех типов данных. Я предполагаю, что ваша база данных не имеет фундаментальных недостатков и использует границы дат.
Я не могу это проверить, так как у нас нет выборочных данных, но, скорее всего, это то, что вам нужно. Обратите внимание также на комментарии в SQL (особенно в ваших диапазонах 4-10 и 10-100).
WITH Counts AS(
SELECT O.Country,
COUNT(*) AS Total
O.[Count] AS TotalPay--COUNT is a reserved word, I suggest against naming your columns this
FROM dbo.Orders O
WHERE [date] >= '20200101'
AND [date] < '20210101'
GROUP BY O.Country)
SELECT C.Country,
SUM(CASE TotalPay WHEN 1 THEN Total END) AS [1],
SUM(CASE TotalPay WHEN 2 THEN Total END) AS [2],
SUM(CASE TotalPay WHEN 3 THEN Total END) AS [3],
SUM(CASE WHEN TotalPay BETWEEN 4 AND 10 THEN Total END) AS [4-10], --note this includes 10
SUM(CASE WHEN TotalPay BETWEEN 10 AND 100 THEN Total END) AS [10-100] --Note this includes 10 again
FROM Counts C
GROUP BY C.Country;
Комментарии:
1. Спасибо , сделал это.
2. Если это ответ на вопрос, @user17141323 , то, пожалуйста, примите его как решение, чтобы будущие пользователи знали, что оно было полезным.