表A
XM SL RQ
张三 1 2007-11-01
张三 2 2007-11-02
李四 1 2007-11-01
李四 1 2007-11-02
李四 5 2007-11-03
王五 1 2007-11-01
王五 6 2007-11-02
... .. ..........
现在我想要的结果就是取本月某个人在某天的数量以及他在本月总的数量
例如我想取2007-11-02结果如下
XM SL ZSL
张三 2 3
李四 1 7
王五 6 7
赵六 0 8
... .. ..
注意可能某人某天没有工作量。但是累计数有
这条语句可能对各位高手没难度,对我来说有点难,能不能帮帮忙啊?
XM SL RQ
张三 1 2007-11-01
张三 2 2007-11-02
李四 1 2007-11-01
李四 1 2007-11-02
李四 5 2007-11-03
王五 1 2007-11-01
王五 6 2007-11-02
... .. ..........
现在我想要的结果就是取本月某个人在某天的数量以及他在本月总的数量
例如我想取2007-11-02结果如下
XM SL ZSL
张三 2 3
李四 1 7
王五 6 7
赵六 0 8
... .. ..
注意可能某人某天没有工作量。但是累计数有
这条语句可能对各位高手没难度,对我来说有点难,能不能帮帮忙啊?
DECLARE @T TABLE
(
XM VARCHAR(20),
SL INT,
RQ VARCHAR(20)
)INSERT INTO @t
SELECT '流氓',1,'2007-11-01' UNION ALL
SELECT '流氓',2,'2007-11-02' UNION ALL
SELECT '街皮',1,'2007-11-01' UNION ALL
SELECT '街皮',1,'2007-11-02' UNION ALL
SELECT '街皮',5,'2007-11-03' UNION ALL
SELECT '混混',1,'2007-11-01' UNION ALL
SELECT '混混',6,'2007-11-02' UNION ALL
SELECT '色狼',1,'2007-11-01' UNION ALL
SELECT '色狼',1,'2007-11-20'SELECT DISTINCT XM,
[02日做案次数] = ISNULL(( SELECT SL FROM @T WHERE XM = A.XM AND RQ = '2007-11-02' ),0),
[11月做案次数] = ( SELECT ISNULL(SUM(SL),0) FROM @T WHERE XM = A.XM AND
LEFT(RQ,7) = '2007-11')
FROM @t AXM 02日做案次数 11月做案次数
-------------------- ----------- -----------
混混 6 7
街皮 1 7
流氓 2 3
色狼 0 2(所影响的行数为 4 行)
from
(
select
XM ,
SL
from
表A
where
RQ ='2007-11-02 '
) a
right join
(
select
XM
from
表A
group by XM
) b
on a.XM = b.XM
from
(
select
XM ,
SL
from
表A
where
RQ ='2007-11-02 '
) a
right join
(
select
XM
from
表A
group by XM
) b
on a.XM = b.XM
DECLARE @T TABLE
(
XM VARCHAR(20),
SL INT,
RQ VARCHAR(20)
)INSERT INTO @t
SELECT '流氓',1,'2007-11-01' UNION ALL
SELECT '流氓',2,'2007-11-02' UNION ALL
SELECT '街皮',1,'2007-11-01' UNION ALL
SELECT '街皮',1,'2007-11-02' UNION ALL
SELECT '街皮',5,'2007-11-03' UNION ALL
SELECT '混混',1,'2007-11-01' UNION ALL
SELECT '混混',6,'2007-11-02' UNION ALL
SELECT '色狼',1,'2007-11-01' UNION ALL
SELECT '色狼',1,'2007-11-20'select
t.XM,
[SL]=sum(case when RQ='2007-11-02' then SL else 0 end) ,
t2.ZSL
from
@T t
left join
(select XM, sum(SL) as ZSL from @T group by XM )t2 on t.XM=t2.XM
group by
t.XM,t2.ZSL
(所影响的行数为 9 行)XM SL ZSL
-------------------- ----------- -----------
色狼 0 2
流氓 2 3
混混 6 7
街皮 1 7(所影响的行数为 4 行)