TableName
Id Rate (米)
1 2
2 10
3 3
4 4
5 5
写一个SQL 语句得出下面结果:
分析Rate 数量
Rate<=1米 0
2>=Rate>1米 1
3>=Rate>2米 1
4>=Rate>3米 1
Rate>=5 2请各位高手帮忙!
Id Rate (米)
1 2
2 10
3 3
4 4
5 5
写一个SQL 语句得出下面结果:
分析Rate 数量
Rate<=1米 0
2>=Rate>1米 1
3>=Rate>2米 1
4>=Rate>3米 1
Rate>=5 2请各位高手帮忙!
FROM(
SELECT 分析Rate = N'Rate<=1米', a = NULL, b=1 UNION ALL
SELECT 分析Rate = N'2>=Rate>1米', a = 2, b=1 UNION ALL
SELECT 分析Rate = N'3>=Rate>2米', a = 3, b=2 UNION ALL
SELECT 分析Rate = N'4>=Rate>3米', a = 4, b=3 UNION ALL
SELECT 分析Rate = N'Rate>=5', a = 5, b = NULL
)A
LEFT JOIN TableName B
ON (A.a IS NULL OR A.a >= B.Rate)
AND (A.b IS NULL OR B.Rate > A.b)
GROUP BY A.分析Rate
when Rate>1 and Rate<=2 then '2>=Rate>1米'
when Rate>2 and Rate<=3 then '3>=Rate>2米'
when Rate>3 and Rate<=4 then '4>=Rate>3米'
when Rate>4 and Rate<=5 then '5>=Rate>4米'
when Rate>5 then 'Rate>=5米'
end as [分析Rate]
,count(*) as 数量
from TableName
group by case when Rate<=1 then 'Rate<=1米'
when Rate>1 and Rate<=2 then '2>=Rate>1米'
when Rate>2 and Rate<=3 then '3>=Rate>2米'
when Rate>3 and Rate<=4 then '4>=Rate>3米'
when Rate>4 and Rate<=5 then '5>=Rate>4米'
when Rate>5 then 'Rate>=5米'
end
from TableName as a inner join
(
Select 0 as sID,1 as eID,'Rate<=1米' as Rate
union
Select 2,3,'2>=Rate>1米'
union
Select 3,4,'3>=Rate>2米'
union
Select 4,5,'4>=Rate>3米'
union
Select 5,2147483647,'Rate>=5 ') as b on a.Rate between b.sID and eID
from TableName as a inner join
(
Select 0 as sID,1 as eID,'Rate<=1米' as Rate
union
Select 2,3,'2>=Rate>1米'
union
Select 3,4,'3>=Rate>2米'
union
Select 4,5,'4>=Rate>3米'
union
Select 5,2147483647,'Rate>=5 ') as b on a.Rate between b.sID and eID
-- 测试数据
CREATE TABLE TableName(Id int, Rate int)
INSERT TableName
SELECT 1, 2 UNION ALL
SELECT 2, 10 UNION ALL
SELECT 3, 3 UNION ALL
SELECT 4, 4 UNION ALL
SELECT 5, 5
GO-- 统计
SELECT A.分析Rate, 数量 = COUNT(B.id)
FROM(
SELECT id = 1, 分析Rate = N'Rate<=1米', a = NULL, b = 1 UNION ALL
SELECT id = 2, 分析Rate = N'2>=Rate>1米', a = 1, b=2 UNION ALL
SELECT id = 3, 分析Rate = N'3>=Rate>2米', a = 2, b=3 UNION ALL
SELECT id = 4, 分析Rate = N'4>=Rate>3米', a = 3, b=4 UNION ALL
SELECT id = 5, 分析Rate = N'Rate>=5', a = 4, b = NULL
)A
LEFT JOIN TableName B
ON (A.a IS NULL OR B.Rate > A.a)
AND (A.b IS NULL OR B.Rate <= A.b)
GROUP BY A.分析Rate, A.id
ORDER BY A.id
GODROP TABLE TableName-- 结果
分析Rate 数量
---------- -----------
Rate<=1米 0
2>=Rate>1米 1
3>=Rate>2米 1
4>=Rate>3米 1
Rate>=5 2