我需要在SQL Server 2019上编写查询枢轴或您的解决方案

Question

我正在工作SQLServer 2019，我有订单表。订单表有列orderid，用户id，国家，网站，计数，价格。我需要帮助写查询。你可以看到下面的细节。

问题:告诉我从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，我有正确的结果。但是，我不能在数据透视表中写入10到4和10到100的范围。

select * from (
SELECT Country,
count(*) as total,
count as totalpay
FROM [CRM].[dbo].[Orders]
where date like '%2010%'
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

Answer 1

正如我在评论中提到的，在这里使用条件聚合，它比PIVOT操作符要通用得多。

同样，您的WHERE将出错，因为'%2010%'不能转换为日期和时间数据类型。如果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;