100分求一sql语句

2013-05-20 11:41:01 378 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

几款支持国标GB28181的平台以及视频监控设备接入的配置方法(Web端无插件播放) GB28181解决的一个主要问题就是如何远程、跨网络、跨区域、跨级别的访问网络中的监控设备（包括IPC、NVR等），本文介绍几款G28181平台的接入配置方法，适合有监控视频摄像头的远程接入和预览需求的人群参考使用。阅读详情



表A



ID   time   data

2    11:15   9

3    11:30  11



表B



ID   time   data

1    11:00  6

2    11:15   5



表C



ID   time   data

2    11:15   8



合并成如下



time    A.data    B.data   C.data 

11:00              6 

11:15    9         5         8

11:30    11

最好效率能高一点，因为每个表的数据量比较大。
非常感谢

...全文

FMEA-MSR步骤三：功能分析目的 FMEA-MSR功能分析的主要目标： ● 功能及各功能之间的关系的可视化 ● 功能树/网络或等效功能矩阵参数图(P图) ● 顾客(外部和内部)功能与相关要求的关联 ● 要求或特性与功能的关联 ● 工程团队(系统、安全和组件)之间的协作 ● 失效分析步骤的基础（点击链接进入官网领取软件试用）在监视及系统响应的补充FMEA中，对失效探测和失效响应的监视视为其具有的功能。硬件和软件功能可能包括监视控制状态的功能。故障/失效的监视和探测功能可能包括超出范围的探测、循环冗余校验、真实性检查和序列计数器检查阅读详情

imho888

基础和管理 17,377 成员 95,111 内容

+ 关注 + 加入社区

切换为时间正序

大明湖畔_帅锅 2013-05-21

打赏
举报

3个表会有1个表的time是全的吗能包括其他2个表的time数据有的话就不用union了

andy_linky 2013-05-21

打赏
举报

把时间值用维度表的键代替...

andy_linky 2013-05-21

打赏
举报

例如使用第三个字段表示来源表...

andy_linky 2013-05-21

打赏
举报

能否换个思路解决问题啊。使用UNION效率不会高的啊。

hanks_gao 2013-05-21

打赏
举报

引用 8 楼 andy_linky 的回复:

把时间值用维度表的键代替...

我觉得这个思路可以！


WITH t0 AS(
    SELECT 1 ID, '11:00' TIME FROM dual
    UNION
    SELECT 2 ID, '11:15' TIME FROM dual
    UNION
    SELECT 3 ID, '11:30' TIME FROM dual
    UNION
    SELECT 4 ID, '11:45' TIME FROM dual
    UNION
    SELECT 5 ID, '12:00' TIME FROM dual
    UNION
    SELECT 6 ID, '12:15' TIME FROM dual     
),
t1 AS(
    SELECT 2 ID,'11:15' TIME,'9' date1 FROM dual
    UNION 
    SELECT 3,'11:30','11' FROM dual
),
t2 AS( 
    SELECT 1 ID,'11:00' TIME,'6' date1 FROM dual
    UNION 
    SELECT 2,'11:15','5' FROM dual
),
t3 AS(
    SELECT '2' ID ,'11:15' TIME,'8' date1 FROM dual
)
SELECT t0.TIME,t1.date1,t2.date1,t3.date1
FROM t0
LEFT JOIN t1 on t0.TIME = t1.TIME
LEFT JOIN t2 ON t0.TIME = t2.TIME
LEFT JOIN t3 ON t0.TIME = t3.TIME
ORDER BY t0.TIME;

一十七 2013-05-21

打赏
举报

引用 2 楼 HJ_daxian 的回复:


with t1 as
(
     select 2 c1,'11:15' c2,9 c3 from dual union all
     select 3 c1,'11:30' c2,11 c3 from dual
),t2 as
(
     select 1 c1,'11:00' c2,6 c3 from dual union all
     select 2 c1,'11:15' c2,5 c3 from dual 
),t3 as
(
     select 2 c1,'11:15' c2,8 c3 from dual 
)

select t.c2,t1.c3,t2.c3,t3.c3
from 
(
select t1.c2
from t1 
union 
select t2.c2
from t2
union select t3.c2
from t3
) t left join t1 on t.c2 = t1.c2
    left join t2 on t.c2 = t2.c2
    left join t3 on t.c2 = t3.c2

     c2      c3    c3    c3
---------------------------------------
1	11:00		6	
2	11:15	9	5	8
3	11:30	11

++++++1

rabitsky 2013-05-21

打赏
举报

全连接，只需要在time字段上加个索引就可以了。


with 
t1 as
( select '2' id, '11:15' time,  '9' data from dual union 
  select '3' id, '11:30' time, '11' data from dual),
t2 as
( select '1' id, '11:00' time,  '6' data from dual union
  select '2' id, '11:15' time,  '5' data from dual), 
t3 as
( select '2' id ,'11:15' time,  '8' data from dual) 

select nvl(t1.time,nvl(t2.time,t3.time)) time, 
       t1.data "A.data", 
       t2.data "B.data", 
       t3.data "C.data"
from t1
full join t2 on t1.time = t2.time
full join t3 on t1.time = t3.time

vanjayhsu 2013-05-21

打赏
举报

select time, max(case when c='A' then date else '' end)as A.data, max(case when c='B' then date else '' end)as B.data, max(case when c='C' then date else '' end)as C.data from ( select time,data,'A' as c from A union allselect time,data,'B' from B union allselect time,data,'C' from C) a group by time

这种方式效率可能高些，具体要看执行计划。。。。

大明湖畔_帅锅 2013-05-20

打赏
举报


with t1 as
(
     select 2 c1,'11:15' c2,9 c3 from dual union all
     select 3 c1,'11:30' c2,11 c3 from dual
),t2 as
(
     select 1 c1,'11:00' c2,6 c3 from dual union all
     select 2 c1,'11:15' c2,5 c3 from dual 
),t3 as
(
     select 2 c1,'11:15' c2,8 c3 from dual 
)

select t.c2,t1.c3,t2.c3,t3.c3
from 
(
select t1.c2
from t1 
union 
select t2.c2
from t2
union select t3.c2
from t3
) t left join t1 on t.c2 = t1.c2
    left join t2 on t.c2 = t2.c2
    left join t3 on t.c2 = t3.c2

     c2      c3    c3    c3
---------------------------------------
1	11:00		6	
2	11:15	9	5	8
3	11:30	11

哥眼神纯洁不 2013-05-20

打赏
举报


select time,
max(case when c='A' then date else '' end)as A.data,
max(case when c='B' then date else '' end)as B.data,
max(case when c='C' then date else '' end)as C.data
from (
select time,data,'A' as c from A
union all
select time,data,'B' from B
union all
select time,data,'C' from C) a
group by time

sych888 2013-05-20

打赏
举报

时间列上加索引

小海葵1 2013-05-20

打赏
举报

借楼上数据 SELECT NVL(T1.TIME, T2.TIME, T3.TIME), T1.DATE1, T2.DATE1, T3.DATE1 FROM T1 FULL JOIN T2 ON T1.ID = T2.ID FULL JOIN T3 ON T1.ID = T2.ID ORDER BY NVL(T1.TIME, T2.TIME, T3.TIME)

cyuyanzhiwen 2013-05-20

打赏
举报

with t1 as(
select '2' id,'11:15' time,'9' date1 from dual
union
select '3','11:30','11' from dual)
,t2 as( 
select '1' id,'11:00'time,'6' date1 from dual
union
select '2','11:15','5' from dual)
, t3 as
(select '2' id ,'11:15' time,'8' date1 from dual)
select nvl(t2.time,t1.time),t1.date1,t2.date1,t3.date1
from t2 full join t1 on t2.time=t1.time
full join t3 on t2.time=t3.time
order by t2.time;

100分求一sql语句

相关推荐