时间戳

首页 > 解决方案 > SQL Server - 操作值以创建字符串

sql - SQL Server - 操作值以创建字符串

问题描述

我正在尝试使用 ID 列中的值填充列 String_to_Use 它从左到右显示在第 5 列的方式，并用“-”显示范围。下面的代码错误地生成了最后一列 string_to_use。 

select 
    t.*,
    (case  
        when Checking_id = -2
           then min(id) over (partition by grp) + '-' + max(id) over (partition by grp)
        else id
     end) as string_to_use
from 
    (select 
         t.*,
         sum(case when Checking_id = -2 then 1 else 0 end) over (partition by id) as grp
     from 
         t) t 
order by 
    id;

输出：

ID  Number  ID  IndexColumn String_To_Use   Checking_id    grp   string_to_use
------------------------------------------------------------------------------
0000    1   0000    1       0000-1130             -2        1    0000-1210 
1000    2   1000    2       0000-1130             -2        1    0000-1210 
1020    3   1020    3       0000-1130             -2        1    0000-1210 
1130    4   1130    4       0000-1130             -2        1    0000-1210 
1198    5   NULL    9999    NULL                NULL        0    NULL
1199    6   1199    5       1199-1210             -2        1    0000-1210 
1210    7   1210    6       1199-1210             -2        1    0000-1210 
1240    8   NULL    9999    NULL                NULL        0    NULL
1250    9   NULL    9999    NULL                NULL        0    NULL
1260    10  1260    7       1260                  7         0    1260 
1261    11  NULL    9999    NULL                NULL        0    NULL
1280    12  NULL    9999    NULL                NULL        0    NULL
1296    13  NULL    9999    NULL                NULL        0    NULL
1298    14  NULL    9999    NULL                NULL        0    NULL
1299    15  1299    8       1299                  8         0    1299 
1501    16  NULL    9999    NULL                NULL        0    NULL

有人可以帮我吗？谢谢！

标签： sqlsql-server

解决方案

看看下面的查询。

我所做的是根据 Number 和 IndexColumn 之间的差异创建组。即我的按块分区是基于组的，直到它遇到 9999 indexcol 记录。

之后，我得到该组的最大 id 和最小 id 值并使用“-”连接

这是一个 db-fiddle 链接 https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=7bd4d3a489600b58740e2f82a478726b

最后查询看起来像这样

create table t(ID varchar(10),Number1 int,ID2 varchar(10),indexcol int,String_To_Use varchar(100))

insert into t
select *
 from (values
 ('0000',1 ,'0000',1   ,'0000-1130')     
,('1000',2 ,'1000',2   ,'0000-1130')     
,('1020',3 ,'1020',3   ,'0000-1130')     
,('1130',4 ,'1130',4   ,'0000-1130')     
,('1198',5 ,NULL  ,9999,NULL      )    
,('1199',6 ,'1199',5   ,'1199-1210')     
,('1210',7 ,'1210',6   ,'1199-1210')     
,('1240',8 ,NULL  ,9999,NULL      )    
,('1250',9 ,NULL  ,9999,NULL      )    
,('1260',10,'1260',7   ,'1260'     )     
,('1261',11,NULL  ,9999,NULL      )    
,('1280',12,NULL  ,9999,NULL      )    
,('1296',13,NULL  ,9999,NULL      )    
,('1298',14,NULL  ,9999,NULL      )    
,('1299',15,'1299',8   ,'1299'     )     
,('1501',16,NULL  ,9999,NULL       )   
)t(id,number1,id2,indexcol,string_to_use)

select *
  ,max(case when indexcol <> 9999 then id end) over(partition by  Number1-indexcol)as max_val
  ,case when max(case when indexcol <> 9999 then id end) over(partition by Number1-indexcol)
           = min(case when indexcol <> 9999 then id end) over(partition by Number1-indexcol)
        then max(case when indexcol <> 9999 then id end) over(partition by Number1-indexcol)
        else min(case when indexcol <> 9999 then id end) over(partition by Number1-indexcol) 
             +'-'+
             max(case when indexcol <> 9999 then id end) over(partition by Number1-indexcol)
    end as computed_string_to_use
from t
order by Number1

+------+---------+------+----------+---------------+---------+------------------------+
|  ID  | Number1 | ID2  | indexcol | String_To_Use | max_val | computed_string_to_use |
+------+---------+------+----------+---------------+---------+------------------------+
| 0000 |       1 | 0000 |        1 | 0000-1130     |    1130 | 0000-1130              |
| 1000 |       2 | 1000 |        2 | 0000-1130     |    1130 | 0000-1130              |
| 1020 |       3 | 1020 |        3 | 0000-1130     |    1130 | 0000-1130              |
| 1130 |       4 | 1130 |        4 | 0000-1130     |    1130 | 0000-1130              |
| 1198 |       5 |      |     9999 |               |         |                        |
| 1199 |       6 | 1199 |        5 | 1199-1210     |    1210 | 1199-1210              |
| 1210 |       7 | 1210 |        6 | 1199-1210     |    1210 | 1199-1210              |
| 1240 |       8 |      |     9999 |               |         |                        |
| 1250 |       9 |      |     9999 |               |         |                        |
| 1260 |      10 | 1260 |        7 | 1260          |    1260 | 1260                   |
| 1261 |      11 |      |     9999 |               |         |                        |
| 1280 |      12 |      |     9999 |               |         |                        |
| 1296 |      13 |      |     9999 |               |         |                        |
| 1298 |      14 |      |     9999 |               |         |                        |
| 1299 |      15 | 1299 |        8 | 1299          |    1299 | 1299                   |
| 1501 |      16 |      |     9999 |               |         |                        |
+------+---------+------+----------+---------------+---------+------------------------+

推荐阅读