时间戳

首页 > 解决方案 > 需要帮助在 sql server 中编写查询

sql - 需要帮助在 sql server 中编写查询

问题描述

我有一张这样的桌子

Col Col A   Col B
A   1.2 -3
B   0.7 3.1
C   0.75    -0.77
D   13  -6
E   -2.45   1.94
F   -0.2    0.88
A   3.5 2.7
B   -1.5    1.34
C   -2.3    0.06
D   3.2 1.9
A   1.2 -2.3
B   -3  1.1
C   3.3 -1.1

我需要编写一个查询以获得以下输出

列

Tenor, 0, 0.5, 1, 1.5, 2, 2.5, 3, ....... , 10 (0 to 10)

Col A 和 Col B 的计数总和，其中 Col 值 >= 列的绝对值

对于 0，按 Tenor 计算 Col A 和 Col B 组中的所有绝对值，其中 abs(value) > 0

对于 0.5，按 Tenor 计算 Col A 和 Col B 组中的所有绝对值，其中 abs(value) > 0.5

Col 0   0.5 1   1.5 2   2.5 …..
A   6   6   6   4   4   3   …..
B   6   6   5   3   2   2   …..
C   6   5   3   2   2   1   …..
D   4   4   4   4   3   3   …..
E   2   2   2   2   1   0   …..
F   2   1   0   0   0   0   …..

等等。输出应如上表

标签： sqlsql-server

解决方案

这是一种动态添加列的方法。将前 41 的参数更改为您想要的列数，如果您希望更改步长，则将 0.5 的值更改为您想要的步长。

DECLARE @query_string as NVARCHAR(MAX),
        @cols         as NVARCHAR(MAX) 



with data
  as (
      select top 41 (row_number() over(order by (select null))-1)*0.5 as rnk
         from master..spt_values
      )
     ,cols_data
      as(
select ',count(case when abs(a.col_a)>='+cast(rnk as varchar(10)) +' then 1 end) + '
       +'count(case when abs(a.col_b)>='+cast(rnk as varchar(10)) +' then 1 end) as ['+cast(rnk as varchar(10))+'] '+ CHAR(10) as col
  from data
        )
 select @cols = string_agg(col, ' ') 
   from cols_data
        


SET @query_string = 'SELECT a.col ' +CHAR(10)
                   +        @cols 
                   +' from t a '+CHAR(10)
           +'GROUP BY a.col'
           
           
execute(@query_string)  




+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| col | 0.0 | 0.5 | 1.0 | 1.5 | 2.0 | 2.5 | 3.0 | 3.5 | 4.0 | 4.5 | 5.0 | 5.5 | 6.0 | 6.5 | 7.0 | 7.5 | 8.0 | 8.5 | 9.0 | 9.5 | 10.0 | 10.5 | 11.0 | 11.5 | 12.0 | 12.5 | 13.0 | 13.5 | 14.0 | 14.5 | 15.0 | 15.5 | 16.0 | 16.5 | 17.0 | 17.5 | 18.0 | 18.5 | 19.0 | 19.5 | 20.0 |
+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| A   |   6 |   6 |   6 |   4 |   4 |   3 |   2 |   1 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
| B   |   6 |   6 |   5 |   3 |   2 |   2 |   2 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
| C   |   6 |   5 |   3 |   2 |   2 |   1 |   1 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
| D   |   4 |   4 |   4 |   4 |   3 |   3 |   3 |   2 |   2 |   2 |   2 |   2 |   2 |   1 |   1 |   1 |   1 |   1 |   1 |   1 |    1 |    1 |    1 |    1 |    1 |    1 |    1 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
| E   |   2 |   2 |   2 |   2 |   1 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
| F   |   2 |   1 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |
+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+

DB Fiddle 链接 https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e2dc12c878fb75515e9aad2eb503c26a

推荐阅读