分组统计问题，显示所有的时间

[xiequan2]

原始数据
prd_no                                             day1        score       
-------------------------------------------------- ----------- ----------- 
a                                                  1           2
a                                                  1           3
a                                                  2           2
b                                                  2           3
b                                                  2           8
b                                                  3           10

要得到结果如下，a在day1=3的时候没有记录score=0
                b在day1=1的时候没有记录score=0

prd_no                                             day1        score            
-------------------------------------------------- ----------- ----------- 
a                                                  1           5
a                                                  2           2
a                                                  3           0
b                                                  1           0
b                                                  2           11
b                                                  3           10

[--小F--]

score=case when day1=3 then 0
                     when day1=1 then 0

[--小F--]

晕 题目看错 再想想

[ks_reny]

[code=SQL]
select prd_no,day1, (ase when prd_no='a' and day1=3 then 0
when prd_no='b' and day1=1 then 0
else score end) score
from tb
/code]

[SQL77]

SELECT T.PRD_NO,ISNULL(T.DAY1,T1.DAY1)DAY1,ISNULL(SCORES ,0)SCORES  FROM 
(SELECT prd_no,DAY1,SUM(SCORES)SCORES FROM TB WHERE prd_no='A'GROUP BY prd_no,DAY1)AS T
FULL JOIN 
(SELECT prd_no,DAY1,SUM(SCORES)SCORES FROM TB WHERE prd_no='B'GROUP BY prd_no,DAY1)AS T1

ON T.PRD_NO=T1.PRD_NO

没试?

[ks_reny]

[Quote=引用 3 楼 ks_reny 的回复:]

 
select  prd_no,day1, (ase when  prd_no='a' and day1=3 then 0 
                          when  prd_no='b' and day1=1  then 0 
                          else  score end) score  
from  tb 
/code]
[/Quote]
[code=SQL]
select  prd_no,day1, (ase when  prd_no='a' and day1=3 then 0 
                          when  prd_no='b' and day1=1  then 0 
                          else  score end) score  
from  tb 

[xiequan2]

就是将prd_no按不同的day1统计分数，没有的day1显示为0,谢了！！

[missilemy]

先group by 再按楼上的方法，应该可行

[xiequan2]

并且这里的day1不是固定的

[ks_reny]

[Quote=引用 8 楼 xiequan2 的回复:]
并且这里的day1不是固定的
[/Quote]
那你給出原始的數據,和最終的結果,和簡單的說明.

[水族杰纶]

--2000借住臨時表
IF OBJECT_ID('TEMPDB..#A')IS NOT NULL DROP TABLE #A
IF OBJECT_ID('TEMPDB..#B')IS NOT NULL DROP TABLE #B
DECLARE @T TABLE(prd_no VARCHAR(10), day1 INT, score INT)       
-------------------------------------------------- ----------- ----------- 
INSERT @T SELECT 'a'  ,                                                1,           2
INSERT @T SELECT 'a'  ,                                                1 ,          3
INSERT @T SELECT 'a' ,                                                 2 ,          2
INSERT @T SELECT 'b',                                                  2 ,          3
INSERT @T SELECT 'b',                                                  2 ,          8
INSERT @T SELECT 'b',                                                  3  ,         10
SELECT *,ID=IDENTITY(INT) INTO #A FROM @T 
SELECT PRD_NO,DAY1,SUM(SCORE)SCORE ,ID=IDENTITY(INT) INTO #B FROM @T GROUP BY  PRD_NO,DAY1
SELECT M.*,ISNULL(N.SCORE,0) FROM (
SELECT PRD_NO,DAY1=(SELECT COUNT(*) FROM #A WHERE ID<=T.ID AND PRD_NO=T.PRD_NO) FROM #A T)M
LEFT JOIN #B N ON M.DAY1=N.DAY1 AND M.PRD_NO=N.PRD_NO
DROP TABLE #A,#B
/*PRD_NO     DAY1                    
---------- ----------- ----------- 
a          1           5
a          2           2
a          3           0
b          1           0
b          2           11
b          3           10*/

[百年树人]

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([prd_no] varchar(1),[day1] int,[score] int)
insert [tb]
select 'a',1,2 union all
select 'a',1,3 union all
select 'a',2,2 union all
select 'b',2,3 union all
select 'b',2,8 union all
select 'b',3,10
 
---查询---
select
  a.prd_no, 
  a.day1,
  isnull(sum(b.score),0) as score
from
  (select * from (select distinct prd_no from tb) a,(select distinct day1 from tb) b) a
left join
  tb b
on
  a.prd_no=b.prd_no
and 
  a.day1=b.day1
group by
  a.prd_no, 
  a.day1 
order by
  a.prd_no
 
---结果---
prd_no day1        score       
------ ----------- ----------- 
a      1           5
a      2           2
a      3           0
b      1           0
b      2           11
b      3           10

（所影响的行数为 6 行）

[yiyi_fish]

up

[fwacky]

create table xiequan2
(
prd_no varchar(10),
day1 int,
score  int
)


insert into xiequan2
select 'a '                                                 ,1    ,       2 union all
select'a'                                        ,         1      ,     3union all
select'a'                                         ,         2    ,       2union all
select'b'                                             ,     2   ,        3union all
select'b'                                             ,     2      ,     8union all
select'b'                                               ,   3    ,       10


select * from xiequan2


select xiequan2_001.prd_no ,xiequan2_001.day1 ,isnull(sum(xiequan2.score),0) as score from 
(select A.day1,B.prd_no from 
(select day1,max(1)as  cancal from  xiequan2  group by day1 )A ,
(select prd_no,max(1)as cancal from  xiequan2  group by prd_no)B) xiequan2_001
left join xiequan2
on xiequan2_001.day1 =  xiequan2.day1  and xiequan2_001.prd_no =  xiequan2.prd_no
group by  xiequan2_001.day1, xiequan2_001.prd_no 

==========================================
prd_no                                             day1        score            
-------------------------------------------------- ----------- ----------- 
a                                                  1           5
a                                                  2           2
a                                                  3           0
b                                                  1           0
b                                                  2           11
b                                                  3           10

[SQL77]

CREATE TABLE TBTEST(prd_no CHAR(1),day1 INT ,score INT)
INSERT TBTEST    
SELECT 'A',1,2 UNION ALL
SELECT 'A',1,3 UNION ALL
SELECT 'A',2,2 UNION ALL
SELECT 'B',2,3 UNION ALL
SELECT 'B',2,8 UNION ALL
SELECT 'B',3,10 

SELECT * FROM TBTEST


SELECT ISNULL(PRD_NO,'A')PRD_NO,TA.DAY1,ISNULL(score,0)score FROM 
(SELECT DISTINCT DAY1 FROM TBTEST)AS TA FULL JOIN
(SELECT prd_no,DAY1,SUM(score)score FROM TBTEST WHERE prd_no='A'GROUP BY prd_no,DAY1)AS TB
ON  TA.DAY1=TB.DAY1

UNION ALL

SELECT ISNULL(PRD_NO,'B')PRD_NO,TA.DAY1,ISNULL(score,0)score FROM 
(SELECT DISTINCT DAY1 FROM TBTEST)AS TA FULL JOIN
(SELECT prd_no,DAY1,SUM(score)score FROM TBTEST WHERE prd_no='B'GROUP BY prd_no,DAY1)AS TB
ON  TA.DAY1=TB.DAY1

PRD_NO DAY1 score
------ ----------- -----------
A 1 5
A 2 2
A 3 0
B 1 0
B 2 11
B 3 10

（所影响的行数为 6 行）

[xiequan2]

感谢以上的牛人，看有没有简单一点的写法sql2000
就是按不同的day1对prd_no进行汇总score,对每个prd_no,必须显示所有的day1汇总数据据(有可能prd_no在这一天day1没有记录，那么就显示为0)
原始数据
prd_no day1 score
-------------------------------------------------- ----------- -----------
a 1 2
a 1 3
a 2 2
b 2 3
b 2 8
b 3 10

按这种方法求select prd_no,day1,sum(score) from tb group by prd_no,day1 只会得到
a 1 5
a 2 2
b 2 11
b 3 10
但并没有以下这两条记录
a 3 0
b 1 0
day1是不确定的, 要想得到结果
prd_no day1 totalscore
a 1 5
a 2 2
a 3 0
b 1 0
b 2 11
b 3 10

[fwacky]

上面的都可以满足， 曾加，prd_no的数据和day1的数据时，都是正确的！

[fwacky]

TO：xiequan2

都可以满足， 曾加，prd_no的数据和day1的数据时，都是正确的！

create table xiequan2
(
prd_no varchar(10),
day1 int,
score  int
)


insert into xiequan2
select 'a '                                                 ,1    ,       2 union all
select'a'                                        ,         1      ,     3union all
select'a'                                         ,         2    ,       2union all
select'b'                                             ,     2   ,        3union all
select'b'                                             ,     2      ,     8union all
select'b'                                               ,   3    ,       10


select * from xiequan2


select xiequan2_001.prd_no ,xiequan2_001.day1 ,isnull(sum(xiequan2.score),0) as score from 
(select A.day1,B.prd_no from 
(select day1,max(1)as  cancal from  xiequan2  group by day1 )A ,
(select prd_no,max(1)as cancal from  xiequan2  group by prd_no)B) xiequan2_001
left join xiequan2
on xiequan2_001.day1 =  xiequan2.day1  and xiequan2_001.prd_no =  xiequan2.prd_no
group by  xiequan2_001.day1, xiequan2_001.prd_no 

==========================================
prd_no                                             day1        score            
-------------------------------------------------- ----------- ----------- 
a                                                  1           5
a                                                  2           2
a                                                  3           0
b                                                  1           0
b                                                  2           11
b                                                  3           10