代码之家 › 专栏 › 技术社区 › JonWay

将列动态分组到范围中

sql-server-2008 tsql sql-server

JonWay · 技术社区 · 6 年前

CREATE TABLE ##TBL (Col INT)
INSERT INTO ##TBL VALUES
(83),(4),(228),(238),(158),(289),(18),(238),(157),(152),(139),(118),(171),(211),(25),(47),(197),(222),
(280),(164),(20),(166),(23),(107),(34),(24),(81),(178),(179),(119),(295),(117),(264),(80),(300),(51),
(128),(124),(296),(213),(150),(85),(92),(285),(281),(47),(115),(36),(267),(274),(75),(176),(57),(71),
(2),(193),(138),(97),(28),(53),(174),(33),(151),(178),(74),(21),(89),(182),(57),(224),(175),(91),
(209),(296),(299),(84),(74),(129),(214),(83),(151),(269),(63),(34),(139),(288),(275),(176),(40),(52),
(199),(95),(167),(38),(46),(179),(190),(292),(174),(127),(191),(241),(177),(83),(289),(98),(50),(289),
(185),(193),(65),(19),(126),(115),(201),(222),(121),(257),(90),(158),(273),(65),(97),(24),(152),(76),
(231),(157),(225),(297),(291),(196),(153),(53),(261),(173),(37),(53),(123),(201),(234),(141),(149),(126),
(26),(22),(121),(252),(126),(4),(210),(196),(176),(102),(202),(114),(228),(3),(23),(25),(22),(129),
(70),(86),(113),(175),(191),(242),(71),(143),(240),(269),(133),(31),(228),(185),(127),(234),(253),(80),
(294),(2),(122),(225),(107),(219),(12),(152),(172),(138),(217),(5),(93),(129),(37),(177),(12),(277),
(195),(93),(93),(93),(238),(134),(60),(3),(245),(48),(40),(205),(288),(82),(190),(174),(143),(1),
(37),(131),(55),(187),(199),(57),(207),(289),(57),(98),(157),(264),(188),(248),(149),(110),(96),(93),
(91),(22),(198),(120),(22),(244),(115),(170),(211),(117),(189),(78),(80),(19),(269),(114),(171),(268),
(258),(59),(27),(190),(208),(5),(52),(88),(262),(106),(235),(77),(108),(35),(174),(245),(107),(21),
(224),(31),(118),(160),(176),(185),(34),(8),(16),(70),(142),(258),(46),(287),(73),(129),(20),(255),
(165),(183),(14),(214),(204),(159),(298),(152),(31),(15),(148),(253),(280),(226),(70),(147),(138),(299),
(229),(189),(98),(54),(262),(94),(115),(195),(261),(126),(67),(100),(186),(243),(236),(250),(148),(172),
(244),(286),(76),(132),(202),(46),(122),(229),(162),(189),(16),(82),(133),(172),(40),(73),(145),(228),
(22),(145),(114),(288),(279),(193),(293),(151),(2),(12),(2),(261),(58),(286),(146),(139),(5),(194),
(104),(248),(70),(211),(186),(276),(88),(188),(35),(75),(236),(188),(63),(246),(7),(7),(229),(275),
(193),(61),(30),(116),(246),(242),(236),(145),(172),(14),(166),(164),(287),(111),(7),(92),(193),(288),
(244),(53),(98),(197),(189),(13),(131),(234),(184),(181),(190),(295),(163),(170),(135),(267),(174),(3),
(134),(224),(71),(252),(170),(36),(198),(63),(280),(152),(225),(10),(273),(263),(89),(273),(69),(5),
(42),(120),(40),(73),(3),(127),(241),(78),(198),(276),(188),(104),(224),(110),(260),(162),(100),(25),
(53),(241),(44),(163),(8),(126),(160),(49),(283),(299),(157),(93),(136),(290),(194),(121),(73),(102)

我有以下问题。

-- group of 50
SELECT
Col,
    CASE WHEN Col<=50 THEN '01-50'
         WHEN Col BETWEEN 51 AND 100 THEN '51-100'
         WHEN Col BETWEEN 101 AND 150 THEN '101-150'
         WHEN Col BETWEEN 151 AND 200 THEN '151-200' --- and so on
    END AS Group_Col
FROM ##TBL
ORDER BY Col

Col Group_Col
8     01-50
90    50-100
111   101-150
601   600-650 etc

如何进行分组 到50 没有案例陈述?值的总列表很大,可能会有所不同。

4 回复 | 直到 6 年前

Squirrel 6 年前

你是说像这样?不逐个输入case语句?

select  Col,
        Group_Col = convert(varchar(10), ((Col - 1) / 50)  * 50 + 1) 
                  + ' - ' 
                  + convert(varchar(10), ((Col - 1) / 50 + 1) * 50)
from    ##TBL
order by Col

Anonymous 6 年前

与前面发布的解决方案类似,但这将确保它们以50为单位分组,并确保一个数字只能落在一个组中。

SELECT distinct
Col,
cast(1+iif(col%50 = 0,col-50,((col/50))*50) as nvarchar(10)) + '-'+
cast(iif(col%50 = 0,col,((col/50) + 1)*50) as nvarchar(10)) 


FROM ##TBL
ORDER BY Col

Juan Carlos Oropeza 6 年前

SELECT
    CASE WHEN Col<=50 THEN '01-50'
         WHEN Col BETWEEN 51 AND 100 THEN '51-100'
         WHEN Col BETWEEN 101 AND 150 THEN '101-150'
         WHEN Col BETWEEN 151 AND 200 THEN '151-200' --- and so on
    END AS Group_Col,
    COUNT (Col) 
FROM ##TBL
GROUP BY 
    CASE WHEN Col<=50 THEN '01-50'
         WHEN Col BETWEEN 51 AND 100 THEN '51-100'
         WHEN Col BETWEEN 101 AND 150 THEN '101-150'
         WHEN Col BETWEEN 151 AND 200 THEN '151-200' --- and so on
    END 
ORDER BY Col

lije 6 年前

SELECT
Col,
CASE WHEN Col<=50 THEN '01-50'
     WHEN Col BETWEEN 51 AND 100 THEN '51-100'
     WHEN Col BETWEEN 101 AND 150 THEN '101-150'
     WHEN Col BETWEEN 151 AND 200 THEN '151-200'
     WHEN Col BETWEEN 201 AND 250 THEN '201-250'
     WHEN Col BETWEEN 251 AND 300 THEN '251-300' --- and so on
END AS Group_Col into #temp
FROM ##TBL 
ORDER BY Col

那么

SELECT count(*) Col, group_col FROM #temp GROUP BY group_col 
ORDER BY group_col