假定你给出例子中的ID都是1981,才可以得到结果。
WITH g(C,P,ts)
AS (
SELECT C,P,COUNT(*)
FROM 表A
WHERE ID=1981
AND D>='2014-08-21'
AND D<'2014-08-23'
GROUP BY C,P
),
t(n,D,C,P,zj)
AS (
SELECT ROW_NUMBER() OVER(PARTITION BY C,P ORDER BY D),
D,C,P,zj
FROM 表A
WHERE ID=1981
AND D>='2014-08-21'
AND D<'2014-08-23'
)
SELECT t.D,
g.ts,
g.c,
g.p,
t.zj
FROM g,t
WHERE g.C = t.C
AND g.P = t.P
AND t.n=1
WITH g(C,P,ts)
AS (
SELECT C,P,COUNT(*)
FROM 表A
WHERE ID=1981
AND D>='2014-08-21'
AND D<'2014-08-23'
GROUP BY C,P
),
t(n,D,C,P,zj)
AS (
SELECT ROW_NUMBER() OVER(PARTITION BY C,P ORDER BY D),
D,C,P,zj
FROM 表A
WHERE ID=1981
AND D>='2014-08-21'
AND D<'2014-08-23'
)
SELECT t.D,
g.ts,
g.c,
g.p,
t.zj
FROM g,t
WHERE g.C = t.C
AND g.P = t.P
AND t.n=1
C,P ,SUM(SL*JG) over(partition by D,C,P) from
(SELECT ID,SL,JG,CONVERT(char(8),D,112)as D ,C,P from A
where D>='20140821' and d<'20140823' and id='1981') as p
select CONVERT(varchar(10),D,120) as D,COUNT(*) as ts,c,p,SUM(SL*JG) as Zj
from A where ID='1981' and D>='2014-8-21' and D<='2014-8-23'
group by CONVERT(varchar(10),D,120),c,p
WHERE D!='2014-08-23 10:04:21.000'
GROUP BY convert(varchar(50),D,23)SELECT COUNT((SL*JG))AS ZJ FROM #Tabs t
WHERE D!='2014-08-23 10:04:21.000'
GROUP BY convert(varchar(50),D,23)SELECT D,C,P FROM #Tabs
WHERE D NOT IN ('2014-08-23 10:04:21.000','2014-08-22 10:04:21.000')
GROUP BY D,c,p