34,590
社区成员
发帖
与我相关
我的任务
分享
select isnull(ta.Sdt,tb.Sdt),Ua1,Ua2
from (
select convert(varchar(10),Sdt,120) as Sdt,max(Ua1) as Ua1
from A
group by convert(varchar(10),Sdt,120)
) ta
full join (
select convert(varchar(10),Sdt,120),max(Ua2) as Ua2
from B
group by convert(varchar(10),Sdt,120)
) tb
on ta.Sdt=tb.Sdt
create table tb1(sdt datetime ,ua1 int,ub1 int,uc1 int)
create table tb2(sdt datetime ,ua2 int,ub2 int,uc2 int)
insert TB1
select '2009-6-23 10:52:00',25 , 32, 26 union all
select '2009-6-23 10:52:00',35 , 75, 38 union all
select '2009-6-23 10:52:00',58 , 68, 39 union all
select '2009-6-24 10:52:00',28 , 69, 46 union all
select '2009-6-24 10:52:00',25 , 58, 37 union all
select '2009-6-24 10:52:00',25 , 58, 37
insert TB2
select '2009-6-23 10:52:00', 67 ,54 ,45 union all
select '2009-6-23 10:52:00', 38 ,69 ,33 union all
select '2009-6-24 10:52:00', 56 ,84 , 44 union all
select '2009-6-24 10:52:00', 76 ,46 ,46 union all
select '2009-6-24 10:52:00', 64 ,66 ,37 union all
select '2009-6-24 10:52:00', 250 ,47 ,60
go
select t.sdt,maxUa1,maxUb1,maxUc1,maxUa2,maxUb2,maxUc2
from (
select convert(varchar(10),sdt,120) sdt ,max(ua1) as maxUa1,max(ub1) as maxub1,MAX(uc1) as maxuc1
from TB1 group by convert(varchar(10),sdt,120) ) t join
(select convert(varchar(10),sdt,120) sdt ,max(ua2) as maxUa2,max(ub2) as maxub2,MAX(uc2) as maxuc2
from TB2 group by convert(varchar(10),sdt,120) ) k on t.sdt=k.sdt
/*
2009-06-23 58 75 39 67 69 45
2009-06-24 28 69 46 250 84 60
*/
CREATE TABLE tb1
(
Sdt datetime,
Ua1 int,
Ub1 int,
Uc1 int,
)
CREATE TABLE tb2
(
Sdt datetime,
Ua2 int,
Ub2 int,
Uc2 int,
)
INSERT INTO tb1
SELECT '2009-6-23 10:52:00', 25, 32, 26 UNION ALL
SELECT '2009-6-23 10:57:00', 35, 75, 38 UNION ALL
SELECT '2009-6-23 11:02:00', 58, 68, 39 UNION ALL
SELECT '2009-6-24 11:07:00', 28, 69, 46 UNION ALL
SELECT '2009-6-24 11:12:00', 25, 58, 37 UNION ALL
SELECT '2009-6-24 11:12:00', 25, 58, 37
INSERT INTO tb2
SELECT '2009-6-23 10:52:00', 67, 54, 45 UNION ALL
SELECT '2009-6-23 10:57:00', 38, 69, 33 UNION ALL
SELECT '2009-6-23 11:02:00', 56, 84, 39 UNION ALL
SELECT '2009-6-24 11:07:00', 76, 36, 46 UNION ALL
SELECT '2009-6-24 11:12:00', 64, 66, 37 UNION ALL
SELECT '2009-6-24 11:12:00', 250, 47, 60
SELECT
A.Sdt,
A.Ua1, A.Ub1, A.Uc1,
B.Ua2, B.Ub2, B.Uc2
INTO tb3 FROM
(
SELECT
Sdt = CONVERT(varchar, Sdt, 112),
Ua1 = MAX(Ua1),
Ub1 = MAX(Ub1),
Uc1 = MAX(Uc1)
FROM tb1
GROUP BY CONVERT(varchar, Sdt, 112)
) A
LEFT JOIN
(
SELECT
Sdt = CONVERT(varchar, Sdt, 112),
Ua2 = MAX(Ua2),
Ub2 = MAX(Ub2),
Uc2 = MAX(Uc2)
FROM tb2
GROUP BY CONVERT(varchar, Sdt, 112)
) B
ON A.Sdt = B.Sdt
SELECT * FROM tb3
DROP TABLE tb1
DROP TABLE tb2
DROP TABLE tb3
insert into tb3
select convert(varchar(10),sdt,120) sdt,max(Ua1),max(Ub1),max(Uc1),max(Ua2),max(Ub2),max(Uc2) from tb1 join tb2 on tb1.sdt=tb2.sdt
select sdt,maxUa1,maxUb1,maxUc1,maxUa2,maxUb2,maxUc2
from (
select convert(varchar(10),sdt,120) sdt ,max(ua1) as maxUa1,max(ub1) as maxub1,MAX(uc1) as maxuc1
from TB1 group by convert(varchar(10),sdt,120) ) t join
(select convert(varchar(10),sdt,120) sdt ,max(ua2) as maxUa2,max(ub2) as maxub2,MAX(uc2) as maxuc2
from TB2 group by convert(varchar(10),sdt,120) ) k on t.sdt=k.sdt
select isnull(ta.Sdt),Ua1,Ua2
from (
select convert(varchar(10),Sdt,120) as Sdt,max(Ua1) as Ua1
from A
group by convert(varchar(10),Sdt,120)
) ta
full join (
select convert(varchar(10),Sdt,120),max(Ua2) as Ua2
from B
group by convert(varchar(10),Sdt,120)
) tb
on ta.Sdt=tb.Sdt
select sdt,maxUa1,maxUb1,maxUc1,maxUa2,maxUb2,maxUc2
from (
select sdt ,max(ua1) as maxUa1,max(ub1) as maxub1,MAX(uc1) as maxuc1
from TB1 group by sdt ) t join
(select sdt ,max(ua2) as maxUa2,max(ub2) as maxub2,MAX(uc2) as maxuc2
from TB2 group by sdt ) k on t.sdt=k.sdt
select isnull(ta.Sdt),Ua1,Ua2
from (
select convert(varchar(10),Sdt,120) as Sdt,max(Ua1) as Ua1
from A
group by convert(varchar(10),Sdt,120)
) ta
full join (
select convert(varchar(10),Sdt,120),max(Ua1) as Ua1
from B
group by convert(varchar(10),Sdt,120)
) tb
on ta.Sdt=tb.Sdt
insert into tb3
select sDt,maxUa1,maxUb1,maxUc1,maxUa2,maxUb2,maxUc2
from .....
可是两个表,我就写不出来。select sdt,
max(maxua1) as maxua1,
max(maxub1) as maxub1,
max(maxuc1) as maxuc1,
max(maxua2) as maxua2,
max(maxub2) as maxub2,
max(maxuc2) as maxuc2
from (
select dateadd(day,datediff(day,0,std),0) as sdt,
max(ua1) as maxua1,
max(ub1) as maxub1,
max(uc1) as maxuc1,
maxua2 = 0, maxub2 = 0, maxuc2 = 0
from tb1
group by dateadd(day,datediff(day,0,std),0)
union
select dateadd(day,datediff(day,0,std),0) as sdt,
maxua1 = 0, maxub1 = 0, maxuc1 = 0
max(ua2) as maxua2,
max(ub2) as maxub2,
max(uc2) as maxuc2
from tb2
group by dateadd(day,datediff(day,0,std),0)
) as a
group by std
select A.*,B.Ua2,B.Ub2,B.Uc2
from
(
select Sdt=convert(varchar(10),Sdt,120), Ua1=max(Ua1),Ub1=max(Ub1),Uc1=max(Uc1)
from Tb1
group by convert(varchar(10),Sdt,120)
) A
join
(
select Sdt=convert(varchar(10),Sdt,120), Ua2=max(Ua2),Ub2=max(Ub2),Uc2=max(Uc2)
from Tb2
group by convert(varchar(10),Sdt,120)
) B
on A.Sdt=B.Sdt