sql 处理数据问题

qq_33188150 2018-01-07 06:29:39
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
CREATE TABLE #T([dateFrom] datetime,[dateTo] datetime,userid NVARCHAR(100),amount INT)
Insert #T
select '2011/10/1 1:00',null,N'u1',20 union all
select '2011/10/1 2:00',null,N'u1',52 union all
select '2011/10/1 3:00',null,N'u2',14 union all
select '2011/10/1 3:00',null,N'u4',58 union all
select '2011/10/2 4:00',null,N'u1',47 union all
select '2011/10/2 4:00',null,N'u2',95 union all
select '2011/10/2 4:00',null,N'u4',56 union all
select '2011/10/3 5:00',null,N'u1',58 union all
select '2011/10/3 4:00',null,N'u2',69
Go
--测试数据结束

;WITH tempa AS (
SELECT CONVERT(NVARCHAR(100), datefrom, 23) AS datefrom ,
CONVERT(NVARCHAR(100), dateto, 23) AS dateto ,
userid ,
SUM(amount) AS amount
FROM #T
GROUP BY CONVERT(NVARCHAR(100), datefrom, 23) ,
CONVERT(NVARCHAR(100), dateto, 23) ,
userid
),tempb AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY datefrom) AS num FROM tempa
)
SELECT datefrom ,
dateto,
userid ,
amount ,
( SELECT SUM(amount)
FROM tempb b
WHERE b.num <= a.num
) AS 累计
FROM tempb a






如何更新dateto，当数量到达100时，日期就加一，
...全文
333 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
qq_33188150 2018-01-08
  • 打赏
  • 举报
 回复
引用 2 楼 sinat_28984567 的回复:
datefrom不知道怎么变得，先写了个没变的 
--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
 CREATE TABLE #T([dateFrom] datetime,[dateTo] datetime,userid NVARCHAR(100),amount INT)
Insert #T
select '2011/10/1 1:00',null,N'u1',20 union all
select '2011/10/1 2:00',null,N'u1',52 union all
select '2011/10/1 3:00',null,N'u2',14 union all
select '2011/10/1 3:00',null,N'u4',58 union all
select '2011/10/2 4:00',null,N'u1',47 union all
select '2011/10/2 4:00',null,N'u2',95 union all
select '2011/10/2 4:00',null,N'u4',56 union all
select '2011/10/3 5:00',null,N'u1',58 union all
select '2011/10/3 4:00',null,N'u2',69
Go
--测试数据结束
 
;WITH tempa AS (
SELECT  CONVERT(NVARCHAR(100), datefrom, 23) AS datefrom ,
CONVERT(NVARCHAR(100), dateto, 23) AS dateto ,
        userid ,
        SUM(amount) AS amount
FROM    #T
GROUP BY CONVERT(NVARCHAR(100), datefrom, 23) ,
     CONVERT(NVARCHAR(100), dateto, 23) ,
        userid
),tempb AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY datefrom) AS num FROM tempa
),ctempc AS (
SELECT 
a.num,
 datefrom ,
dateto,
        userid ,
        amount ,
        ( SELECT    SUM(amount)
          FROM      tempb b
          WHERE     b.num <= a.num
        )%100 AS 累计
FROM    tempb a )
SELECT  a.datefrom ,
        CASE WHEN a.amount + b.累计 >= 100 THEN DATEADD(DAY, 1, a.datefrom)
             ELSE a.datefrom
        END AS dateto ,
        a.userid ,
        a.amount
FROM    ctempc a
        LEFT JOIN ctempc b ON a.num = b.num + 1;
你好，请问datefrom 根据上一个dateto变化，要如何处理
xiaoxiangqing 2018-01-08
  • 打赏
  • 举报
 回复
2楼的就可以
早起晚睡 2018-01-08
  • 打赏
  • 举报
 回复
帮大神们顶顶
RINK_1 2018-01-08
  • 打赏
  • 举报
 回复 

if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
 CREATE TABLE #T([dateFrom] datetime,[dateTo] datetime,userid NVARCHAR(100),amount INT)
Insert #T
select '2011/10/1 1:00',null,N'u1',20 union all
select '2011/10/1 2:00',null,N'u1',52 union all
select '2011/10/1 3:00',null,N'u2',14 union all
select '2011/10/1 3:00',null,N'u4',58 union all
select '2011/10/2 4:00',null,N'u1',47 union all
select '2011/10/2 4:00',null,N'u2',95 union all
select '2011/10/2 4:00',null,N'u4',56 union all
select '2011/10/3 5:00',null,N'u1',58 union all
select '2011/10/3 4:00',null,N'u2',69
Go

with cte_1
as
(select CONVERT(varchar(10),dateFrom,23) as datefrom,dateTo,userid,SUM(amount) as amount
 from #T 
 group by CONVERT(varchar(10),dateFrom,23),dateTo,userid),
 
cte_2
as
(select *,min(datefrom) over (partition by 1) as min_date,ROW_NUMBER() over (order by datefrom,userid) as seq_1 from cte_1),

cte_3
as
(select * from cte_2 A
outer apply (select SUM(amount) as subtotal from cte_2 where seq_1<=A.seq_1) as B) 

select case when DATEDIFF(DAY,datefrom,dateto_new)<=1 then datefrom else DATEADD(DAY,-1,dateto_new) end as datefrom,
       dateto_new as dateto,
       userid,
       amount,
       subtotal
from
(select *,DATEADD(DAY,qty_time,min_date) as dateto_new
from
(select *,subtotal/100 as qty_time from cte_3) as A) as A
qq_33188150 2018-01-07
  • 打赏
  • 举报
 回复
datefrom到dateto若连续的就改变， 不连续的就不变，和2楼的差不多，2楼大神加上后面就是我想要答案
ChinaITOldMan 2018-01-07
  • 打赏
  • 举报
 回复
不是很明白你意思
二月十六 版主 2018-01-07
  • 打赏
  • 举报
 回复
datefrom不知道怎么变得，先写了个没变的
--测试数据

if not object_id(N'Tempdb..#T') is null

    drop table #T

Go

 CREATE TABLE #T([dateFrom] datetime,[dateTo] datetime,userid NVARCHAR(100),amount INT)

Insert #T

select '2011/10/1 1:00',null,N'u1',20 union all

select '2011/10/1 2:00',null,N'u1',52 union all

select '2011/10/1 3:00',null,N'u2',14 union all

select '2011/10/1 3:00',null,N'u4',58 union all

select '2011/10/2 4:00',null,N'u1',47 union all

select '2011/10/2 4:00',null,N'u2',95 union all

select '2011/10/2 4:00',null,N'u4',56 union all

select '2011/10/3 5:00',null,N'u1',58 union all

select '2011/10/3 4:00',null,N'u2',69

Go

--测试数据结束

 

;WITH tempa AS (

SELECT  CONVERT(NVARCHAR(100), datefrom, 23) AS datefrom ,

CONVERT(NVARCHAR(100), dateto, 23) AS dateto ,

        userid ,

        SUM(amount) AS amount

FROM    #T

GROUP BY CONVERT(NVARCHAR(100), datefrom, 23) ,

     CONVERT(NVARCHAR(100), dateto, 23) ,

        userid

),tempb AS (

SELECT *,ROW_NUMBER()OVER(ORDER BY datefrom) AS num FROM tempa

),ctempc AS (

SELECT 

a.num,

 datefrom ,

dateto,

        userid ,

        amount ,

        ( SELECT    SUM(amount)

          FROM      tempb b

          WHERE     b.num <= a.num

        )%100 AS 累计

FROM    tempb a )

SELECT  a.datefrom ,

        CASE WHEN a.amount + b.累计 >= 100 THEN DATEADD(DAY, 1, a.datefrom)

             ELSE a.datefrom

        END AS dateto ,

        a.userid ,

        a.amount

FROM    ctempc a

        LEFT JOIN ctempc b ON a.num = b.num + 1;



二月十六 版主 2018-01-07
  • 打赏
  • 举报
 回复
datefrom有一个变得？
解决SQLSERVER数据库驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接问题JAR包
用于解决SQLSERVER连接问题驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接问题JAR包。
Android通过webservice连接Sqlserver实例
Android连接SQLServer详细教程（数据库+服务器+客户端） 博客http://blog.csdn.net/zhyl8157121/article/details/8169172中的资源
SQL语句基础教程
SQL语句基础教程
SQL PDF教程（精通SQL）
很好的数据库教程，内含高清PDF文档，内容翔实，学得愉快！
sql server学习资料
sql server学习资源完整，短时间可以迅速增长知识
MS-SQL Server

34,838

社区成员

254,631

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章