查询计量主表和计量子表,关联字段是计量单号
查出指定车号为XXX的子表记录,当为2条查询结果时(如果不是2条查询结果就不进行操作),例如,本来显示重量 计量时间 物料名称
A1 A2 A3
B1 B2 B3现在要求显示为重量1 计量时间1 重量2 计量时间2 物料名称 重量差
A1 A2 B1 B2 A3 A1-A2也就是对比两次过车的重量差
上面说的是按某个车号去查询
最后要做到的效果是显示所有满足在子表中有两条记录,并且都以上面的比较形式展示出来。。
SQL语句要怎么写啊?还是要用到存储过程,求大神指点。。先谢谢各位啦
查出指定车号为XXX的子表记录,当为2条查询结果时(如果不是2条查询结果就不进行操作),例如,本来显示重量 计量时间 物料名称
A1 A2 A3
B1 B2 B3现在要求显示为重量1 计量时间1 重量2 计量时间2 物料名称 重量差
A1 A2 B1 B2 A3 A1-A2也就是对比两次过车的重量差
上面说的是按某个车号去查询
最后要做到的效果是显示所有满足在子表中有两条记录,并且都以上面的比较形式展示出来。。
SQL语句要怎么写啊?还是要用到存储过程,求大神指点。。先谢谢各位啦
with test as (
select 'A001' AS CH,'20' AS ZL,'10' AS JLSJ,'A3' AS WLMC FROM DUAL
UNION ALL
select 'A001' AS CH,'30' AS ZL,'20' AS JLSJ,'B3' AS WLMC FROM DUAL
UNION ALL
select 'A002' AS CH,'45' AS ZL,'30' AS JLSJ,'C3' AS WLMC FROM DUAL
UNION ALL
select 'A002' AS CH,'55' AS ZL,'40' AS JLSJ,'D3' AS WLMC FROM DUAL
UNION ALL
select 'A003' AS CH,'60' AS ZL,'50' AS JLSJ,'E3' AS WLMC FROM DUAL
)
SELECT MAX(DECODE(RN, 1, ZL, '')) AS CH1,
MAX(DECODE(RN, 1, JLSJ, '')) AS JLSJ1,
MAX(DECODE(RN, 2, ZL, '')) AS CH2,
MAX(DECODE(RN, 2, JLSJ, '')) AS JLSJ2,
MAX(DECODE(RN, 1, WLMC, '')) AS WLMC,
MAX(DECODE(RN, 1, ZL - JLSJ, '')) AS ZLC
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY CH ORDER BY JLSJ) AS RN,
COUNT(*) OVER(PARTITION BY CH) AS CT,
CH,
ZL,
JLSJ,
WLMC
FROM TEST)
WHERE CT = 2
GROUP BY CH=======================================
1 20 10 30 20 A3 10
2 45 30 55 40 C3 15