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

plupiseo 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(339)

我有一张这样的table

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)

列a和列b的计数之和，其中列值大于等于列的abs值
对于0，按abs（值）>0的期限计算a列和b列组中的所有abs值
对于0.5，按期限计算a列和b列组中的所有abs值，其中abs（值）>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   …..

等等。输出应如上表所示

sql sql-server

来源：https://stackoverflow.com/questions/63069716/need-help-to-write-a-query-in-sql-server

2条答案

按热度按时间

0g0grzrc1#

这里有一个方法

select a.col
       ,count(case when abs(a.col_a)>=0   then 1 end)  + count(case when abs(a.col_b)>=0   then 1 end) as [0]
       ,count(case when abs(a.col_a)>=0.5   then 1 end)  + count(case when abs(a.col_b)>=0.5   then 1 end) as [0.5]
       ,count(case when abs(a.col_a)>=1   then 1 end)  + count(case when abs(a.col_b)>=1   then 1 end) as [1]
       ,count(case when abs(a.col_a)>=1.5   then 1 end)  + count(case when abs(a.col_b)>=1.5   then 1 end) as [1.5]
       ,count(case when abs(a.col_a)>=2   then 1 end)  + count(case when abs(a.col_b)>=2   then 1 end) as [2]
       ,count(case when abs(a.col_a)>=2.5   then 1 end)  + count(case when abs(a.col_b)>=2.5   then 1 end) as [2.5]
       ,count(case when abs(a.col_a)>=3   then 1 end)  + count(case when abs(a.col_b)>=3   then 1 end) as [3]
       ,count(case when abs(a.col_a)>=3.5   then 1 end)  + count(case when abs(a.col_b)>=3.5   then 1 end) as [3.5]
       ,count(case when abs(a.col_a)>=4   then 1 end)  + count(case when abs(a.col_b)>=4   then 1 end) as [4]
       ,count(case when abs(a.col_a)>=4.5   then 1 end)  + count(case when abs(a.col_b)>=4.5   then 1 end) as [4.5]
       ,count(case when abs(a.col_a)>=5   then 1 end)  + count(case when abs(a.col_b)>=5   then 1 end) as [5]
       ,count(case when abs(a.col_a)>=5.5   then 1 end)  + count(case when abs(a.col_b)>=5.5   then 1 end) as [5.5]
       ,count(case when abs(a.col_a)>=6   then 1 end)  + count(case when abs(a.col_b)>=6   then 1 end) as [6]
       ,count(case when abs(a.col_a)>=6.5   then 1 end)  + count(case when abs(a.col_b)>=6.5   then 1 end) as [6.5]
       ,count(case when abs(a.col_a)>=7   then 1 end)  + count(case when abs(a.col_b)>=7   then 1 end) as [7]
       ,count(case when abs(a.col_a)>=7.5   then 1 end)  + count(case when abs(a.col_b)>=7.5   then 1 end) as [7.5]
       ,count(case when abs(a.col_a)>=8   then 1 end)  + count(case when abs(a.col_b)>=8   then 1 end) as [8]
       ,count(case when abs(a.col_a)>=8.5   then 1 end)  + count(case when abs(a.col_b)>=8.5   then 1 end) as [8.5]
       ,count(case when abs(a.col_a)>=9   then 1 end)  + count(case when abs(a.col_b)>=9   then 1 end) as [9]
       ,count(case when abs(a.col_a)>=9.5   then 1 end)  + count(case when abs(a.col_b)>=9.5   then 1 end) as [9.5]
       ,count(case when abs(a.col_a)>=10   then 1 end)  + count(case when abs(a.col_b)>=10   then 1 end) as [10]
  from t a
group by a.col  

+-----+---+-----+---+-----+---+-----+---+-----+---+-----+---+-----+---+-----+---+-----+---+-----+---+-----+----+
| col | 0 | 0.5 | 1 | 1.5 | 2 | 2.5 | 3 | 3.5 | 4 | 4.5 | 5 | 5.5 | 6 | 6.5 | 7 | 7.5 | 8 | 8.5 | 9 | 9.5 | 10 |
+-----+---+-----+---+-----+---+-----+---+-----+---+-----+---+-----+---+-----+---+-----+---+-----+---+-----+----+
| A   | 6 |   6 | 6 |   4 | 4 |   3 | 2 |   1 | 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 |
| C   | 6 |   5 | 3 |   2 | 2 |   1 | 1 |   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 |
| E   | 2 |   2 | 2 |   2 | 1 |   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）举报 2021-07-24

hwamh0ep2#

下面是一种动态添加列的方法。将top 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小提琴链接https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e2dc12c878fb75515e9aad2eb503c26a

赞(0）回复(0）举报 2021-07-24

我来回答

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

2条答案

相关问题

热门标签

最新问答