分段统计问题（动态区间统计）

lengxl 2020-02-25 03:15:01

有3张表
一张表放数据
declare @t table(id int,sj int,num int)
insert into @t select 1, 1，2
insert into @t select 2, 2，150
insert into @t select 3, 3，5000
insert into @t select 4, 1，60
insert into @t select 5, 2，120000
insert into @t select 6, 3，33000
insert into @t select 7, 1，4500
insert into @t select 8, 2，590
insert into @t select 9, 3，89
insert into @t select 10, 1，110

一张表表示数据分段
declare @c table(div int)
insert into @c select 10
insert into @c select 100
insert into @c select 500
insert into @c select 1000

一张表表示时间分段
declare @d table(sj int)
insert into @d select 1
insert into @d select 2
insert into @d select 3

然后我需要分段统计表t每段时间里面每个区间的频数

如下结果

时间区间1(0-10) 区间2(10-100) 区间3(100-500) 区间4(500-1000) 区间5(>1000)
0-1 1 2 1 0 0
1-2 0 1 0 1 1
2-3 0 1 0 0 2

因为表c和d里面的数据是动态的,可以自由调整,也可以增加和减少行

如何用sql语句实现呢?

...全文

193 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

二月十六 2020-02-28

打赏
举报

引用 10 楼 lengxl 的回复:

[quote=引用 9 楼二月十六的回复:] [quote=引用 8 楼 lengxl 的回复:] [quote=引用 7 楼二月十六的回复:] 图片没发上来，我大概觉得是那段，是因为有一个动态执行的语句，如果不把处理数据的语句写进去，就无法使用了

[/quote] 一个是在外边用的，一个是动态语句里边用的[/quote] 你这个语句能在c#里调用执行吗？还是只能在sql server里执行？[/quote] 可以在c#里边执行

lengxl 2020-02-27

打赏
举报

引用 9 楼二月十六的回复:

[quote=引用 8 楼 lengxl 的回复:] [quote=引用 7 楼二月十六的回复:] 图片没发上来，我大概觉得是那段，是因为有一个动态执行的语句，如果不把处理数据的语句写进去，就无法使用了

[/quote] 一个是在外边用的，一个是动态语句里边用的[/quote] 你这个语句能在c#里调用执行吗？还是只能在sql server里执行？

二月十六 2020-02-26

打赏
举报

引用 8 楼 lengxl 的回复:

[quote=引用 7 楼二月十六的回复:] 图片没发上来，我大概觉得是那段，是因为有一个动态执行的语句，如果不把处理数据的语句写进去，就无法使用了

[/quote] 一个是在外边用的，一个是动态语句里边用的

lengxl 2020-02-25

打赏
举报

引用 7 楼二月十六的回复:

图片没发上来，我大概觉得是那段，是因为有一个动态执行的语句，如果不把处理数据的语句写进去，就无法使用了

二月十六 2020-02-25

打赏
举报

图片没发上来，我大概觉得是那段，是因为有一个动态执行的语句，如果不把处理数据的语句写进去，就无法使用了

lengxl 2020-02-25

打赏
举报

引用 5 楼二月十六的回复:

[quote=引用 4 楼 lengxl 的回复:] [quote=引用 3 楼二月十六的回复:] [quote=引用 2 楼 lengxl 的回复:] [quote=引用 1 楼二月十六的回复:] 给c表加一个0列

DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY div) rn FROM dbo.c 
),cteb AS (
SELECT ca.div,CASE WHEN cb.div IS NOT NULL THEN cb.div ELSE 1000000000 END AS bdiv FROM ctea ca LEFT JOIN ctea cb ON ca.rn+1 = cb.rn
),ctec AS (
SELECT t.*,cteb.* FROM d  JOIN t ON t.sj = d.sj  JOIN cteb ON t.num BETWEEN cteb.div AND cteb.bdiv
)
select sj'
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY div) rn FROM dbo.c 
),cteb AS (
SELECT ca.div,CASE WHEN cb.div IS NOT NULL THEN cb.div ELSE 1000000000 END AS bdiv FROM ctea ca LEFT JOIN ctea cb ON ca.rn+1 = cb.rn
),ctec AS (
SELECT t.*,cteb.* FROM d  JOIN t ON t.sj = d.sj  JOIN cteb ON t.num BETWEEN cteb.div AND cteb.bdiv
)
SELECT  @sql = @sql + ',sum(case when num between ' + RTRIM(a.div)
        + ' and ' + RTRIM(a.bdiv)
        + ' then 1 else 0 end)[区间' + RTRIM(a.div)
        + '-' + RTRIM(a.bdiv)
        + ']'
FROM    ( SELECT DISTINCT
                    ctec.div,ctec.bdiv
          FROM      ctec
        ) a
SET @sql = @sql
    + ' from ctec group by sj'
EXEC(@sql)

结果：

你好，可以一条sql select语句出来吗？[/quote] 这个比较困难[/quote] 我对sql server不是很熟悉，你的代码看不懂，直接复制过去执行代码有错误

[/quote] 我建了三个表，没用表变量[/quote] 建表后没问题了，问一下图片中框起来的代码是一样的，为什么要用2次呢？谢谢

二月十六 2020-02-25

打赏
举报

引用 4 楼 lengxl 的回复:

[quote=引用 3 楼二月十六的回复:] [quote=引用 2 楼 lengxl 的回复:] [quote=引用 1 楼二月十六的回复:] 给c表加一个0列

DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY div) rn FROM dbo.c 
),cteb AS (
SELECT ca.div,CASE WHEN cb.div IS NOT NULL THEN cb.div ELSE 1000000000 END AS bdiv FROM ctea ca LEFT JOIN ctea cb ON ca.rn+1 = cb.rn
),ctec AS (
SELECT t.*,cteb.* FROM d  JOIN t ON t.sj = d.sj  JOIN cteb ON t.num BETWEEN cteb.div AND cteb.bdiv
)
select sj'
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY div) rn FROM dbo.c 
),cteb AS (
SELECT ca.div,CASE WHEN cb.div IS NOT NULL THEN cb.div ELSE 1000000000 END AS bdiv FROM ctea ca LEFT JOIN ctea cb ON ca.rn+1 = cb.rn
),ctec AS (
SELECT t.*,cteb.* FROM d  JOIN t ON t.sj = d.sj  JOIN cteb ON t.num BETWEEN cteb.div AND cteb.bdiv
)
SELECT  @sql = @sql + ',sum(case when num between ' + RTRIM(a.div)
        + ' and ' + RTRIM(a.bdiv)
        + ' then 1 else 0 end)[区间' + RTRIM(a.div)
        + '-' + RTRIM(a.bdiv)
        + ']'
FROM    ( SELECT DISTINCT
                    ctec.div,ctec.bdiv
          FROM      ctec
        ) a
SET @sql = @sql
    + ' from ctec group by sj'
EXEC(@sql)

结果：

你好，可以一条sql select语句出来吗？[/quote] 这个比较困难[/quote] 我对sql server不是很熟悉，你的代码看不懂，直接复制过去执行代码有错误

[/quote] 我建了三个表，没用表变量

lengxl 2020-02-25

打赏
举报

引用 3 楼二月十六的回复:

[quote=引用 2 楼 lengxl 的回复:] [quote=引用 1 楼二月十六的回复:] 给c表加一个0列

DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY div) rn FROM dbo.c 
),cteb AS (
SELECT ca.div,CASE WHEN cb.div IS NOT NULL THEN cb.div ELSE 1000000000 END AS bdiv FROM ctea ca LEFT JOIN ctea cb ON ca.rn+1 = cb.rn
),ctec AS (
SELECT t.*,cteb.* FROM d  JOIN t ON t.sj = d.sj  JOIN cteb ON t.num BETWEEN cteb.div AND cteb.bdiv
)
select sj'
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY div) rn FROM dbo.c 
),cteb AS (
SELECT ca.div,CASE WHEN cb.div IS NOT NULL THEN cb.div ELSE 1000000000 END AS bdiv FROM ctea ca LEFT JOIN ctea cb ON ca.rn+1 = cb.rn
),ctec AS (
SELECT t.*,cteb.* FROM d  JOIN t ON t.sj = d.sj  JOIN cteb ON t.num BETWEEN cteb.div AND cteb.bdiv
)
SELECT  @sql = @sql + ',sum(case when num between ' + RTRIM(a.div)
        + ' and ' + RTRIM(a.bdiv)
        + ' then 1 else 0 end)[区间' + RTRIM(a.div)
        + '-' + RTRIM(a.bdiv)
        + ']'
FROM    ( SELECT DISTINCT
                    ctec.div,ctec.bdiv
          FROM      ctec
        ) a
SET @sql = @sql
    + ' from ctec group by sj'
EXEC(@sql)

结果：

你好，可以一条sql select语句出来吗？[/quote] 这个比较困难[/quote] 我对sql server不是很熟悉，你的代码看不懂，直接复制过去执行代码有错误

二月十六 2020-02-25

打赏
举报

引用 2 楼 lengxl 的回复:

[quote=引用 1 楼二月十六的回复:] 给c表加一个0列

DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY div) rn FROM dbo.c 
),cteb AS (
SELECT ca.div,CASE WHEN cb.div IS NOT NULL THEN cb.div ELSE 1000000000 END AS bdiv FROM ctea ca LEFT JOIN ctea cb ON ca.rn+1 = cb.rn
),ctec AS (
SELECT t.*,cteb.* FROM d  JOIN t ON t.sj = d.sj  JOIN cteb ON t.num BETWEEN cteb.div AND cteb.bdiv
)
select sj'
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY div) rn FROM dbo.c 
),cteb AS (
SELECT ca.div,CASE WHEN cb.div IS NOT NULL THEN cb.div ELSE 1000000000 END AS bdiv FROM ctea ca LEFT JOIN ctea cb ON ca.rn+1 = cb.rn
),ctec AS (
SELECT t.*,cteb.* FROM d  JOIN t ON t.sj = d.sj  JOIN cteb ON t.num BETWEEN cteb.div AND cteb.bdiv
)
SELECT  @sql = @sql + ',sum(case when num between ' + RTRIM(a.div)
        + ' and ' + RTRIM(a.bdiv)
        + ' then 1 else 0 end)[区间' + RTRIM(a.div)
        + '-' + RTRIM(a.bdiv)
        + ']'
FROM    ( SELECT DISTINCT
                    ctec.div,ctec.bdiv
          FROM      ctec
        ) a
SET @sql = @sql
    + ' from ctec group by sj'
EXEC(@sql)

结果：

你好，可以一条sql select语句出来吗？[/quote] 这个比较困难

lengxl 2020-02-25

打赏
举报

引用 1 楼二月十六的回复:

给c表加一个0列

DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY div) rn FROM dbo.c 
),cteb AS (
SELECT ca.div,CASE WHEN cb.div IS NOT NULL THEN cb.div ELSE 1000000000 END AS bdiv FROM ctea ca LEFT JOIN ctea cb ON ca.rn+1 = cb.rn
),ctec AS (
SELECT t.*,cteb.* FROM d  JOIN t ON t.sj = d.sj  JOIN cteb ON t.num BETWEEN cteb.div AND cteb.bdiv
)
select sj'
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY div) rn FROM dbo.c 
),cteb AS (
SELECT ca.div,CASE WHEN cb.div IS NOT NULL THEN cb.div ELSE 1000000000 END AS bdiv FROM ctea ca LEFT JOIN ctea cb ON ca.rn+1 = cb.rn
),ctec AS (
SELECT t.*,cteb.* FROM d  JOIN t ON t.sj = d.sj  JOIN cteb ON t.num BETWEEN cteb.div AND cteb.bdiv
)
SELECT  @sql = @sql + ',sum(case when num between ' + RTRIM(a.div)
        + ' and ' + RTRIM(a.bdiv)
        + ' then 1 else 0 end)[区间' + RTRIM(a.div)
        + '-' + RTRIM(a.bdiv)
        + ']'
FROM    ( SELECT DISTINCT
                    ctec.div,ctec.bdiv
          FROM      ctec
        ) a
SET @sql = @sql
    + ' from ctec group by sj'
EXEC(@sql)

结果：

你好，可以一条sql select语句出来吗？

二月十六 2020-02-25

打赏
举报

给c表加一个0列

DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY div) rn FROM dbo.c 
),cteb AS (
SELECT ca.div,CASE WHEN cb.div IS NOT NULL THEN cb.div ELSE 1000000000 END AS bdiv FROM ctea ca LEFT JOIN ctea cb ON ca.rn+1 = cb.rn
),ctec AS (
SELECT t.*,cteb.* FROM d  JOIN t ON t.sj = d.sj  JOIN cteb ON t.num BETWEEN cteb.div AND cteb.bdiv
)
select sj'
;WITH ctea AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY div) rn FROM dbo.c 
),cteb AS (
SELECT ca.div,CASE WHEN cb.div IS NOT NULL THEN cb.div ELSE 1000000000 END AS bdiv FROM ctea ca LEFT JOIN ctea cb ON ca.rn+1 = cb.rn
),ctec AS (
SELECT t.*,cteb.* FROM d  JOIN t ON t.sj = d.sj  JOIN cteb ON t.num BETWEEN cteb.div AND cteb.bdiv
)
SELECT  @sql = @sql + ',sum(case when num between ' + RTRIM(a.div)
        + ' and ' + RTRIM(a.bdiv)
        + ' then 1 else 0 end)[区间' + RTRIM(a.div)
        + '-' + RTRIM(a.bdiv)
        + ']'
FROM    ( SELECT DISTINCT
                    ctec.div,ctec.bdiv
          FROM      ctec
        ) a
SET @sql = @sql
    + ' from ctec group by sj'
EXEC(@sql)

结果：