救命啊,SQL Server 发神经了.......

Question

表一
StateDateTime Totle
2006-3-11      100
2006-4-23      200表二
StateDateTime Checked
2006-3-15     20
2006-4-14     126表三
StateDateTime CheckError
2006-3-15     123
2006-4-23     26用Left Join　把3个表
join回来得到的StateDateTime Totle Checked CheckError
............   ...   ...     ...为什么CheckError这项总是没有内容,这句SQL应该怎么写?大家救救命啊,12点了,我都还没有吃饭...

Answer 1

CREATE TABLE TB2(STATETIME VARCHAR(15),CHECKED INT)
CREATE TABLE TB3(STATETIME VARCHAR(15),CHECKERROR INT)INSERT INTO TB1 SELECT '2006-3-11',11 
UNION ALL SELECT '2006-4-23',200 INSERT INTO TB2 SELECT '2006-3-15',20 
UNION ALL SELECT '2006-4-14',126 INSERT INTO TB3 SELECT '2006-3-15',123 
UNION ALL SELECT '2006-4-23',26 SELECT A.STATETIME,TB1.TOTAL,TB2.CHECKED,TB3.CHECKERROR FROM 
(SELECT STATETIME FROM TB1
 UNION SELECT STATETIME FROM TB2
 UNION SELECT STATETIME FROM TB3
 ) AS A
FULL OUTER JOIN TB1 ON TB1.STATETIME=A.STATETIME
FULL OUTER JOIN TB2 ON TB2.STATETIME=A.STATETIME
FULL OUTER JOIN TB3 ON TB3.STATETIME=A.STATETIMEDROP TABLE TB1,TB2,TB3
-------------------------------
2006-3-11 11 NULL NULL
2006-3-15  NULL 20 123
2006-4-14  NULL 126 NULL
2006-4-23  200 NULL 26

Answer 2

参照：
http://community.csdn.net/Expert/topic/4782/4782017.xml?temp=.643963

Answer 3

CREATE TABLE TB1(STATETIME datetime,TOTAL INT)
CREATE TABLE TB2(STATETIME datetime,CHECKED INT)
CREATE TABLE TB3(STATETIME datetime,CHECKERROR INT)INSERT INTO TB1 SELECT '2006-3-11',11 
UNION ALL SELECT '2006-4-23',200 INSERT INTO TB2 SELECT '2006-3-15',20 
UNION ALL SELECT '2006-4-14',126 INSERT INTO TB3 SELECT '2006-3-15',123 
UNION ALL SELECT '2006-4-23',26 select statetime=isnull(isnull(a.statetime,b.statetime),c.statetime),
total=isnull(total,0),
checked=isnull(checked,0),
checkerror=isnull(checkerror,0) 
from tb1 a
full join tb2 b on a.statetime=b.statetime
full join tb3 c on a.statetime=c.statetime

Answer 4

select statetime=isnull(isnull(a.statetime,b.statetime),c.statetime),
total=isnull(sum(total),0),
checked=isnull(sum(checked),0),
checkerror=isnull(sum(checkerror),0) 
from tb1 a
full join tb2 b on a.statetime=b.statetime
full join tb3 c on a.statetime=c.statetime
group by isnull(isnull(a.statetime,b.statetime),c.statetime)

Answer 5

select statetime=convert(char(10),isnull(isnull(a.statetime,b.statetime),c.statetime),120),
total=isnull(sum(total),0),
checked=isnull(sum(checked),0),
checkerror=isnull(sum(checkerror),0) 
from tb1 a
full join tb2 b on a.statetime=b.statetime
full join tb3 c on a.statetime=c.statetime
group by convert(char(10),isnull(isnull(a.statetime,b.statetime),c.statetime),120)

Answer 6

为什么我写出来的结果是这个啊,救救我啊.....SQL:
SELECT TmpStatTotle.StatDateTime ,Sum(TmpStatTotle.Totle) as 'Totle' ,Sum(TmpStatChecked.checked) as 'Checked',Sum(TmpStatError.CheckError) as 'CheckError' From TmpStatTotle 
FULL JOIN TmpStatChecked  on TmpStatTotle.StatDateTime = TmpStatChecked.StatDateTime 
FULL JOIN TmpStatError on TmpStatError.StatDateTime =  TmpStatTotle.StatDateTime Group by TmpStatTotle.StatDateTime
结果
---
StatDateTime        Column1             Column2             CheckError          
20060424            1                   1                                       
20060510            2                   1                                       
20060511            1                   1                                       
20060512            1                   1                                       
20060516            8                   6                                       
2006052             1                                                           
20060522            2                   2                                       
20060523            1                   1                                       
20060525            9                   7                                       
20060526            1                   1                                       
2006058             28                  13                                     

Answer 7

期望的效果如下表一
StateDateTime Totle
2006-3-11      100
2006-3-15      30
2006-4-23      200表二
StateDateTime Checked
2006-3-11     126
2006-3-15     20
表三
StateDateTime CheckError
2006-3-15     123
2006-4-23     26
StateDateTime Totle Checked CheckError
2006-3-11      100  126     0
2006-3-15      30   20      123
2006-4-23      200  0       26

Answer 8

问一下：
StateDateTime你这个字段是什么类型？你一开始不是说是
StateDateTime Totle
2006-3-11      100
2006-4-23      200
............
怎么，看你的语句也没有进行类型转换，出来的全是20060424                                                 
20060510                                               
20060511

Answer 9

这个就可以实现啊~select statetime=convert(char(10),isnull(isnull(a.statetime,b.statetime),c.statetime),120),
total=isnull(sum(total),0),
checked=isnull(sum(checked),0),
checkerror=isnull(sum(checkerror),0) 
from tb1 a
full join tb2 b on a.statetime=b.statetime
full join tb3 c on a.statetime=c.statetime
group by convert(char(10),isnull(isnull(a.statetime,b.statetime),c.statetime),120)

Answer 10

还有SQL:
SELECT TmpStatTotle.StatDateTime ,Sum(TmpStatTotle.Totle) as 'Totle' ,Sum(TmpStatChecked.checked) as 'Checked',Sum(TmpStatError.CheckError) as 'CheckError' From TmpStatTotle 
FULL JOIN TmpStatChecked  on TmpStatTotle.StatDateTime = TmpStatChecked.StatDateTime 
FULL JOIN TmpStatError on TmpStatError.StatDateTime =  TmpStatTotle.StatDateTime Group by TmpStatTotle.StatDateTime
结果
---
StatDateTime        Column1             Column2             CheckError          
20060424            1                   1                                       
20060510            2                   1                                       
20060511            1                   1                                       
20060512            1                   1                                       
20060516            8                   6                                       
2006052             1                                                           
20060522            2                   2                                       
20060523            1                   1                                       
20060525            9                   7                                       
20060526            1                   1                                       
2006058             28                  13     你的语句和你的结果也不匹配啊，哪跟哪啊

Answer 11

Select 
IsNull(IsNull(A.StateDateTime,B.StateDateTime),C.StateDateTime) As StateDateTime,
IsNull(A.Totle,0) As Totle,
IsNull(B.Checked,0) As Checked,
IsNull(C.CheckError,0) As CheckError
From 表一 A
Full Join 表二 B
On A.StateDateTime=B.StateDateTime
Full Join 表三 C
On A.StateDateTime=C.StateDateTime

Answer 12

直接执行这个试试：
SELECT convert(char(10),isnull(isnull(a.statetime,b.statetime),c.statetime),120) as statetime,
isnull(sum(total),0) as total,
isnull(sum(checked),0) as checked,
isnull(sum(checkerror),0) as checkerror 
From TmpStatTotle a
FULL JOIN TmpStatChecked  b on a.StatDateTime = b.StatDateTime 
FULL JOIN TmpStatError c on a.StatDateTime =  c.StatDateTime 
Group by convert(char(10),isnull(isnull(a.statetime,b.statetime),c.statetime),120)

Answer 13

上面有一字段不一样~改一下~SELECT convert(char(10),isnull(isnull(a.StatDateTime,b.StatDateTime),c.StatDateTime),120) as StatDateTime,
isnull(sum(total),0) as total,
isnull(sum(checked),0) as checked,
isnull(sum(checkerror),0) as checkerror 
From TmpStatTotle a
FULL JOIN TmpStatChecked  b on a.StatDateTime = b.StatDateTime 
FULL JOIN TmpStatError c on a.StatDateTime =  c.StatDateTime 
Group by convert(char(10),isnull(isnull(a.StatDateTime,b.StatDateTime),c.StatDateTime),120)