sql查询查找两个值之间的计数数据 [英] sql query for find count data between two values

问题描述

查询两个值之间的查找计数数据的SQL查询

sql query for find count data between two values

userid      values     packetsNumbers
206         100        10
206         101        15
206         102        20
206         103        25
208         104        10
209         105        32
209         106        38

结果如下所示：

The result will display like below:

UseeID    count    packets between(10 t0 15)
206        201         10to19
206        205         20to29
208        104         10to19
209        211         30to40

推荐答案

如果不想写下很多案例，请使用以下技巧...

if do not want to write lot of cases use below trick...

with a as
(
select 206 userid, 100 [values], 10 packetsNumbers
union all
select 206, 101, 15
union all
select 206, 102, 20
union all
select 206, 103, 25
union all
select 208, 104, 10
union all
select 209, 105, 32
union all
select 209, 106, 38
)
SELECT userid, SUM([values]) AS [Count], Convert(varchar , PacketCategory) + ' To ' + Convert(varchar , PacketCategory+9)
FROM (
SELECT userid, [values], round(packetsNumbers,-1,1) AS PacketCategory
FROM a
) as aa
GROUP BY userid, PacketCategory
ORDER BY userid

快乐编码！

：）

Happy Coding!
:)

试试这个：

Try this:

DECLARE @tmp TABLE (userid INT, [values] INT, packetsNumbers INT)

INSERT INTO @tmp (userid, [values],packetsNumbers)
VALUES(206, 100, 10)
INSERT INTO @tmp (userid, [values],packetsNumbers)
VALUES(206, 101, 15)
INSERT INTO @tmp (userid, [values],packetsNumbers)
VALUES(206, 102, 20)
INSERT INTO @tmp (userid, [values],packetsNumbers)
VALUES(206, 103, 25)
INSERT INTO @tmp (userid, [values],packetsNumbers)
VALUES(208, 104, 10)
INSERT INTO @tmp (userid, [values],packetsNumbers)
VALUES(209, 105, 32)
INSERT INTO @tmp (userid, [values],packetsNumbers)
VALUES(209, 106, 38)


SELECT userid, SUM([values]) AS [Count], PacketCategory
FROM (
	SELECT userid, [values], CASE
			WHEN packetsNumbers>=0 AND packetsNumbers<10 THEN '0 to 9'
			WHEN packetsNumbers>=10 AND packetsNumbers<20 THEN '10 to 19'
			WHEN packetsNumbers>=20 AND packetsNumbers<30 THEN '20 to 29'
			WHEN packetsNumbers>=30 AND packetsNumbers<40 THEN '30 to 39'
			WHEN packetsNumbers>=40 AND packetsNumbers<50 THEN '40 to 49'
			END AS PacketCategory
	FROM @tmp
	) AS T
GROUP BY userid, PacketCategory
ORDER BY userid

结果：

Result:

206	201	10 to 19
206	205	20 to 29
208	104	10 to 19
209	211	30 to 39

这篇关于sql查询查找两个值之间的计数数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！