id date val dateDiff
3669 2009-8-4 0 0
4020 2009-8-4 0 0
4022 2009-8-4 930 0
4025 2009-8-4 0 0
4027 2009-8-4 0 0
4043 2009-8-4 0 0
4174 2009-8-5 0 1
4330 2009-8-5 0 0
4798 2009-8-6 0 1
4975 2009-8-6 482 0
5340 2009-8-6 550 0
如上表,原表只有三列,最后一列是我想加的,如何用SQL生成上表(第一行datediff就是0,从第二行开如就是用本用DATE值减增上一行date值),求指点
3669 2009-8-4 0 0
4020 2009-8-4 0 0
4022 2009-8-4 930 0
4025 2009-8-4 0 0
4027 2009-8-4 0 0
4043 2009-8-4 0 0
4174 2009-8-5 0 1
4330 2009-8-5 0 0
4798 2009-8-6 0 1
4975 2009-8-6 482 0
5340 2009-8-6 550 0
如上表,原表只有三列,最后一列是我想加的,如何用SQL生成上表(第一行datediff就是0,从第二行开如就是用本用DATE值减增上一行date值),求指点
go
create table [TB] (id int,date datetime,val int)
insert into [TB]
select 3669,'2009-8-4',0 union all
select 4020,'2009-8-4',0 union all
select 4022,'2009-8-4',930 union all
select 4025,'2009-8-4',0 union all
select 4027,'2009-8-4',0 union all
select 4043,'2009-8-4',0 union all
select 4174,'2009-8-5',0 union all
select 4330,'2009-8-5',0 union all
select 4798,'2009-8-6',0 union all
select 4975,'2009-8-6',482 union all
select 5340,'2009-8-6',550select * from [TB];WITH tt
AS(
SELECT ROW_NUMBER() OVER(ORDER BY id) AS num ,*
FROM dbo.TB )
SELECT A.id,A.date,ISNULL(DATEDIFF(dd,a.date,b.date),0) AS [DATEDIFF]
FROM TT A
LEFT JOIN TT B ON A.num = B.num-1/*
id date DATEDIFF
3669 2009-08-04 00:00:00.000 0
4020 2009-08-04 00:00:00.000 0
4022 2009-08-04 00:00:00.000 0
4025 2009-08-04 00:00:00.000 0
4027 2009-08-04 00:00:00.000 0
4043 2009-08-04 00:00:00.000 1
4174 2009-08-05 00:00:00.000 0
4330 2009-08-05 00:00:00.000 1
4798 2009-08-06 00:00:00.000 0
4975 2009-08-06 00:00:00.000 0
5340 2009-08-06 00:00:00.000 0*/
这个大MS SQL 中能用不?;WITH tt
这个语句是?if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (id int,date datetime,val int)
insert into [TB]
select 3669,'2009-8-4',0 union all
select 4020,'2009-8-4',0 union all
select 4022,'2009-8-4',930 union all
select 4025,'2009-8-4',0 union all
select 4027,'2009-8-4',0 union all
select 4043,'2009-8-4',0 union all
select 4174,'2009-8-5',0 union all
select 4330,'2009-8-5',0 union all
select 4798,'2009-8-6',0 union all
select 4975,'2009-8-6',482 union all
select 5340,'2009-8-6',550select * from [TB];WITH tt
AS(
SELECT ROW_NUMBER() OVER(ORDER BY id) AS num ,*
FROM dbo.TB )
SELECT A.id,A.date,ISNULL(DATEDIFF(dd,a.date,b.date),0) AS [DATEDIFF]
FROM TT A
LEFT JOIN TT B ON A.num = B.num-1/*
id date DATEDIFF
3669 2009-08-04 00:00:00.000 0
4020 2009-08-04 00:00:00.000 0
4022 2009-08-04 00:00:00.000 0
4025 2009-08-04 00:00:00.000 0
4027 2009-08-04 00:00:00.000 0
4043 2009-08-04 00:00:00.000 1
4174 2009-08-05 00:00:00.000 0
4330 2009-08-05 00:00:00.000 1
4798 2009-08-06 00:00:00.000 0
4975 2009-08-06 00:00:00.000 0
5340 2009-08-06 00:00:00.000 0*/
case when count(1)>1
then datediff(df1.date,max(df2.date))
else 0
end as dateDiff
from dfTable df1
left join dfTable df2 on df2.id<df1.id
group by df1.id
把
case when count(1)>1
then datediff(df1.date,max(df2.date))
else 0
end as dateDiff
换成
isnull(datediff(df1.date,max(df2.date)),0) as dateDiffselect df1.id,df1.date,df1.val,
isnull(datediff(df1.date,max(df2.date)),0) as dateDiff
from dfTable df1
left join dfTable df2 on df2.id<df1.id
group by df1.id;
USE AdventureWorks2008R2;
GOWITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
SELECT SalesPersonID, COUNT(*)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID
)SELECT SalesPersonID, NumberOfOrders
FROM Sales_CTE
ORDER BY SalesPersonID;
GO