Sql 多表查询

多表拼接查询

select t2.ariid, t2.alarmconfigid, t2.alarmtime, t2.alarmdetail, t2.actualduration, t2.isack, t2.acktime, t2.acktype, t2.ackuser, t2.ackdetail, t1.ariid,t1.alarmtypeid, t1.aridesc, t1.ariname from yf_alarmrecord t2 join  yf_alarmconfig t1 on t2.alarmconfigid=t1.ariid

多表关联查询

select t2.aridesc from RM_User t2 join  BM_DataRight t1 on t2.ariid=t1.userid and t1.itemid=5 and t1.enterpriseid=17 and t1.opttype=1 and t1.thetype=8

递归查询

select * from RD_Factory where EnterpriseId = 17 START WITH Pid is null CONNECT BY PRIOR AriId = Pid ORDER siblings BY AriSeq

sql语句从一张表查询一个字段值插入另一个表中

insert into a(name,num,class) values('小米','1001',(select top 1 class from b where name = '小米'));

表格字段连续分段分组查询：

效果

select * from
(
select deviceid, productid,theele, thetime,row_number() over(order by thetime asc) as RN from yf_deviceproductinfo
) a full join
(
select deviceid, productid,theele, thetime,row_number() over(order by thetime asc) as RN from yf_deviceproductinfo
) b on a.RN+1=b.RN
where a.deviceid<>b.Deviceid or a.productid<>b.productId or a.deviceid is null or b.deviceid is null

连续数据分组分段统计实现

CREATE global TEMPORARY TABLE temp  
ON COMMIT PRESERVE ROWS   
AS  
select a.RN,b.RN as RN1,row_number() over(order by  nvl(a.RN,0)) as Num from
(
select deviceid, productid,row_number() over(order by thetime asc) as RN from yf_deviceproductinfo
) a full join
(
select deviceid, productid,row_number() over(order by thetime asc) as RN from yf_deviceproductinfo
) b on a.RN+1=b.RN
where a.deviceid<>b.Deviceid or a.productid<>b.productId or a.deviceid is null or b.deviceid is null;
select s.deviceid,s.productid,sum(theele) as ele,ceil((max(thetime)-min(thetime))*24*60*60) as seconds,count(1) as productnum from
(select deviceid, productid,theele,thetime,row_number() over(order by thetime asc) as RN from yf_deviceproductinfo) s,
(select b.rn1 as F, a.rn as L from temp a inner join temp b on
a.num=b.num+1) n where s.rn>=n.F and s.rn<=n.L
group by s.deviceid,s.productid,n.F order by s.deviceid;
truncate TABLE temp;
drop table temp;

统计所有相同项目的总量：

select deviceid,productid,sum(theele) as ele,ceil((max(thetime)-min(thetime))*24*60*60) as seconds,count(1) as productnum from Yf_deviceProductInfo group by deviceid,productid order by deviceid;

对表格统计后关联多表查询

select A.*, B.AriName as DeviceName, C.AriName as ProductName from (select deviceid,productid,sum(theele) as ele,ceil((max(thetime)-min(thetime))*24*60*60) as seconds,max(thetime)as theStartTime,min(thetime)as theEndTime, count(1) as productnum from Yf_deviceProductInfo group by deviceid,productid order by deviceid) A, YF_Device B, YF_DeviceProduct C where A.DeviceId = B.AriId and A.ProductId = C.AriId