我想把表Z1中的Rdt,P,Sid三个字段和表Q0Z0中的Rdt,P,Sid三个字段查询出来,然后存入到表KC_P(Rdt,P,Kid)中。
但是由于表Q0Z0中的Sid和Z1表中的Sid的值存在重复的情况,所以我想在查询的时候给Q0Z0表中的Sid字段加上100,以避免在KC_P表中出现重复的Kid号。下面是我写的句子,运行不正确,请高手给指点一下,或者给出更好的句子,谢谢!
insert KC_P(Rdt,P,Kid)
select *
from
(
select Rdt,P,Sid
from Z1
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) A join (
select Rdt,Pt,Sid+100
from Q0Z0
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) B
on A.Rdt=B.Rdt
但是由于表Q0Z0中的Sid和Z1表中的Sid的值存在重复的情况,所以我想在查询的时候给Q0Z0表中的Sid字段加上100,以避免在KC_P表中出现重复的Kid号。下面是我写的句子,运行不正确,请高手给指点一下,或者给出更好的句子,谢谢!
insert KC_P(Rdt,P,Kid)
select *
from
(
select Rdt,P,Sid
from Z1
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) A join (
select Rdt,Pt,Sid+100
from Q0Z0
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) B
on A.Rdt=B.Rdt
select * --貌似楼主的语法也错了吧,这里有几列?
from
(
select Rdt,P,Sid
from Z1
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) A join (
select Rdt,Pt,Sid+100
from Q0Z0
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) B
on A.Rdt=B.Rdt 表Q0Z0中的Rdt,P,Sid三个字段查询出来,然后存入到表KC_P(Rdt,P,Kid)中怎么个存法????
select *
from
(
select *
from
(
select Rdt,P,Sid
from Z1
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) A
union all
select *
from
(
select Rdt,Pt,Sid+100
from Q0Z0
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) B
) j
select *
from
(
select Rdt,P,Sid
from Z1
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) A
union all
select *
from
(
select Rdt,Pt,Sid+100
from Q0Z0
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) B
这样好了
select RDT,SID
from
(
select Rdt,P,Sid
from Z1
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) A
union all
select RDT,SID
from
(
select Rdt,Pt,Sid+100 AS SID
from Q0Z0
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) B
select *
from
(
select Rdt,P,Sid
from Z1
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) A
union all
select *
from
(
select Rdt,Pt,Sid+100 AS SID
from Q0Z0
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
group by Rdt
) B 这样就行了,不好意思,只看楼主的题目,语句列数看错了,
select *
from
(
select Rdt,P,Sid
from Z1
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
--group by Rdt
) A
union all
select *
from
(
select Rdt,Pt,Sid+100 AS SID
from Q0Z0
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
--group by Rdt
) B 楼主GROUP BY 是要如何?要不然直接DISTINCT
insert KC_P(Rdt,P,Kid)
select *
from
(
select Rdt,P,Sid
from Z1
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
) A
union all
select *
from
(
select Rdt,Pt,Sid+100 as sid
from Q0Z0
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'
) B
能运行成功么?如果能成功 但是慢的话
问题可能出在这里where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'有索引么
select Rdt,P,Sid
from Z1
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'unionselect Rdt,Pt,Sid+100 as sid
from Q0Z0
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'语句还可以简一下
没有包含P吗,?
还有RDT是索引列的第列还是?SQL 是0几的?
大于7.1号的占总数据量的多少,
还有KC-P表里的索引情况,数据情况?
索引是(sid,rdt)顺序的。是不是该把时间字段放前面啊?应该怎么设计这个索引,才能最大效率的执行查询呢?
插入表数据时,2ORDER BY RDT 排好序这样应该差不多了,
看来2000只能这样建立个非聚集索引
CREATE INDEX IDX_RDT_SID_P ON TB(RDT,SID,P)
insert KC_P(Rdt,P,Kid)
SELECT * FROM (
select Rdt,P,Sid
from Z1
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1'unionselect Rdt,Pt,Sid+100 as sid
from Q0Z0
where datepart(mi,Rdt) in (0,30) and Rdt>'2010-7-1') T ORDER BY RDT插入的数据如果占,KC-P的绝大多数,就把聚集索引弄掉再重建吧,禁用非聚集索引