2个sql救助.1.
三个表.相同字段,现在合并数据,具体数据如下.
test1(id , code, position, amount)
001 mb1 p1 1
002 mb2 p1 1
003 mb3 p2 1test2(id , code, position, amount)
001 mb1 p1 1
002 mb2 p1 1
003 mb4 p1 1test2(id , code, position, amount)
001 mb1 p1 1
002 mb2 p2 1
003 mb5 p1 1
现在合并它们(code, position, test1_amount,test2_amount, test3_amount),条件是.code和position一样的合在一起结果:
code position test1_amount test2_amount test3_amount
mb1 p1 1 1 1
mb2 p1 1 1
mb2 p2 1
mb3 p2 1
mb4 p1 1
mb5 p1 1
这样的sql如何写
2
一张入库明细单(in_detail),现要找离某一天(Date1)最近又要小于这一天所的记录,具体数据如下
入库明细单(明细单ID, 物料编号, 入库日期,位置,物料级别)
in_detail(ID,m_code,in_date,in_position,m_level)id code in_date in_position m_level
---------------------------------------------------
001 m1 2005-6-5 p1 4
002 m2 2005-6-5 p2 4
003 m3 2005-6-6 p2 3
004 m4 2005-6-7 p1 5
005 m1 2005-6-8 p1 7
006 m4 2005-6-11 p2 3
007 m1 2005-6-12 p1 6
008 m5 2005-6-13 p3 7
009 m1 2005-6-14 p3 19
010 m1 2005-6-15 p2 3我现在想找离2005-6-14日最近发生的记录,结果如下
009 m1 2005-6-14 p3 19
008 m5 2005-6-13 p3 7
006 m4 2005-6-11 p2 3
003 m3 2005-6-6 p2 3
002 m2 2005-6-5 p2 4现就不知道sql语句如何写...
三个表.相同字段,现在合并数据,具体数据如下.
test1(id , code, position, amount)
001 mb1 p1 1
002 mb2 p1 1
003 mb3 p2 1test2(id , code, position, amount)
001 mb1 p1 1
002 mb2 p1 1
003 mb4 p1 1test2(id , code, position, amount)
001 mb1 p1 1
002 mb2 p2 1
003 mb5 p1 1
现在合并它们(code, position, test1_amount,test2_amount, test3_amount),条件是.code和position一样的合在一起结果:
code position test1_amount test2_amount test3_amount
mb1 p1 1 1 1
mb2 p1 1 1
mb2 p2 1
mb3 p2 1
mb4 p1 1
mb5 p1 1
这样的sql如何写
2
一张入库明细单(in_detail),现要找离某一天(Date1)最近又要小于这一天所的记录,具体数据如下
入库明细单(明细单ID, 物料编号, 入库日期,位置,物料级别)
in_detail(ID,m_code,in_date,in_position,m_level)id code in_date in_position m_level
---------------------------------------------------
001 m1 2005-6-5 p1 4
002 m2 2005-6-5 p2 4
003 m3 2005-6-6 p2 3
004 m4 2005-6-7 p1 5
005 m1 2005-6-8 p1 7
006 m4 2005-6-11 p2 3
007 m1 2005-6-12 p1 6
008 m5 2005-6-13 p3 7
009 m1 2005-6-14 p3 19
010 m1 2005-6-15 p2 3我现在想找离2005-6-14日最近发生的记录,结果如下
009 m1 2005-6-14 p3 19
008 m5 2005-6-13 p3 7
006 m4 2005-6-11 p2 3
003 m3 2005-6-6 p2 3
002 m2 2005-6-5 p2 4现就不知道sql语句如何写...
解决方案 »
- NLS_CHARACTERSET 和 NLS_NCHAR_CHARACTERSET 的值不一致可能导致什么后果?
- 字段数据丢失
- 请教一下各位高手,oracle的存储过程怎么返回一个结果集出来??
- 大家快来帮帮我啊。。。。。SQL问题 在线等
- Oracle9i中如何写正则表达式,急,见详细说明。谢谢
- 怎么插入时间数据?
- 在oracle中不能修改列名
- 这句SQL语句如何优化,现在执行很慢很慢......
- Insert into tablex Select...不能运行阿
- win8.1专业版Database Upgrade Assistant的问题。
- 定时启动??
- 访问ORACLE73 ODBC数据源出错!急急急!!!
select a.code,a.position ,test1.amount test1_amount,test2.amount test2_amount ,test3.amount test3_amount
from
(
select code,position from test1
union
select code,position from test2
union
select code,position from test3
) a
left join test1 on (a.code=test1.code and a.position =test1.position )
left join test2 on (a.code=test2.code and a.position =test2.position )
left join test3 on (a.code=test3.code and a.position =test3.position )
select * from in_detail
where to_char(in_date,'yyyy-mm-dd' <= '2005-06-14'
order by in_date desc
COALESCE(t1.position,t2.position,t3.position) position,
t1.amount test1_amount,
t2.amount test2_amount,
t3.amount test3_amount
from test1 t1
full outer join test2 t2 on t1.code = t2.code and t1.position = t2.position
full outer join test3 t3 on t3.code = t2.code and t3.position = t2.position
order by code,position
-------------------->
select
a.code,a.position ,a2.con1 test1_amount,a3.con2 test2_amount ,a4.con3 test3_amount
from
(select code,position from test1
union
select code,position from test2
union
select code,position from test3
) a,
(select code,position ,count(*) con1 from test1 group code,position ) a2,
(select code,position ,count(*) con2 from test2 group code,position ) a3,
(select code,position ,count(*) con3 from test3 group code,position ) a4
where
a.code=a2.code(+) and a.position =a2.position (+)
and a.code=a3.code(+) and a.position =a3.position (+)
and a.code=a4.code(+) and a.position =a4.position (+)
select * from
(select t.*,rank() over(partition by code order by in_date desc) rk
from in_detail t where in_date <= date '2005-06-14')
where rk > 1
SQL> create table t_1(id int, c1 varchar(10));表已创建。SQL> create table t_2(id int, c2 varchar(10));表已创建。SQL> create table t_3(id int, c3 varchar(10));表已创建。SQL> insert into t_1 values(1,'1_1');已创建 1 行。SQL> insert into t_1 values(2,'1_2');已创建 1 行。SQL> insert into t_1 values(3,'1_3');已创建 1 行。SQL> insert into t_2 values(1,'2_1');已创建 1 行。SQL> insert into t_2 values(21,'2_21');已创建 1 行。SQL> insert into t_2 values(9,'2_9');已创建 1 行。SQL> insert into t_3 values(1,'3_1');已创建 1 行。SQL> insert into t_3 values(9,'3_9');已创建 1 行。SQL> insert into t_3 values(31,'3_31');已创建 1 行。SQL> insert into t_3 values(3,'3_3');已创建 1 行。SQL> commit;提交完成。SQL> select * from t_1; ID C1
---------- ----------
1 1_1
2 1_2
3 1_3SQL> select * from t_2; ID C2
---------- ----------
1 2_1
21 2_21
9 2_9SQL> select * from t_3; ID C3
---------- ----------
1 3_1
9 3_9
31 3_31
3 3_3SQL> select nvl(a.id,nvl(b.id,c.id)),c1,c2,c3 from t_1 a full join t_2 b on a.id=b.id full join t_3 c on a.id=c.id order by 1;NVL(A.ID,NVL(B.ID,C.ID)) C1 C2 C3
------------------------ ---------- ---------- ----------
1 1_1 2_1 3_1
2 1_2
3 1_3 3_3
9 2_9
9 3_9
21 2_21
31 3_31已选择7行。
select * from
(select t.*,row_number() over(partition by code order by in_date desc) rk
from test t where in_date <= date '2005-06-14')
where rk = 1SQL>
5 /ID CODE IN_DATE IN_POSITION M_LEVEL RK
---------- ---------- ----------- ----------- ---------- ----------
009 m1 2005-6-14 p3 19 1
002 m2 2005-6-5 p2 4 1
003 m3 2005-6-6 p2 3 1
006 m4 2005-6-11 p2 3 1
008 m5 2005-6-13 p3 7 1第1题:
select COALESCE(t1.code,t2.code,t3.code) code,
COALESCE(t1.position,t2.position,t3.position) position,
t1.amount test1_amount,
t2.amount test2_amount,
t3.amount test3_amount
from test1 t1
full outer join test2 t2 on t1.code = t2.code and t1.position = t2.position
full outer join test3 t3 on t3.code = t2.code and t3.position = t2.position
order by code,positionSQL>
10 /CODE POSITION TEST1_AMOUNT TEST2_AMOUNT TEST3_AMOUNT
---------- ---------- ------------ ------------ ------------
mb1 p1 1 1 1
mb2 p1 1 1
mb2 p2 1
mb3 p2 1
mb4 p1 1
mb5 p1 16 rows selected
第二个问题 hevin(没有什么是不可能的) 没有理解楼主的意思,应该是这样的:
SQL> select * from b;B1 B2 B3 B4
---------- ---------- ---------- -----------
1 m1 4 2005-8-22 9
2 m2 3 2005-8-29 9
3 m1 10 2005-8-27 9
4 m3 6 2005-8-30 9
5 m4 7 2005-8-21 9
6 m4 17 2005-8-31 9
7 m5 4 2005-8-27 97 rows selectedExecuted in 0.031 seconds-- 8月29日以前的数据:SQL> select * from b where (b2,b4) in (select b2,max(b4) b4 from b where trunc(b4,'dd') <= trunc((sysdate-3),'dd')group by b2);B1 B2 B3 B4
---------- ---------- ---------- -----------
3 m1 10 2005-8-27 9
2 m2 3 2005-8-29 9
5 m4 7 2005-8-21 9
7 m5 4 2005-8-27 9Executed in 0.015 seconds-- 8月31日以前的数据:SQL> select * from b where (b2,b4) in (select b2,max(b4) b4 from b where trunc(b4,'dd') <= trunc((sysdate-1),'dd')group by b2);B1 B2 B3 B4
---------- ---------- ---------- -----------
3 m1 10 2005-8-27 9
2 m2 3 2005-8-29 9
4 m3 6 2005-8-30 9
6 m4 17 2005-8-31 9
7 m5 4 2005-8-27 9Executed in 0.015 secondsSQL> -- (b1,b2,b3,b4) = (id,code,m_level,in_date)
-- 检索的是codeok ~~
select COALESCE(t1.code,t2.code,t3.code) code,--该函数取出第1个不为空的值
COALESCE(t1.position,t2.position,t3.position) position,
t1.amount test1_amount,
t2.amount test2_amount,
t3.amount test3_amount
from test1 t1
full outer join test2 t2 on t1.code = t2.code and t1.position = t2.position
full outer join test3 t3 on t3.code = t2.code and t3.position = t2.position
order by code,position我这边发生了一个奇怪的现象,有重复项
我这边的数据
test1
---------------------------------------
1 mb1 2256 1
2 mb2 2256 1
3 mb3 2261 1
test2
---------------------------------------
1 mb1 2257 1
2 mb5 2255 1
3 mb4 2261 1 test3
-----------------------------------------
1 mb1 2256 1
2 mb2 2256 1
3 mb7 2261 1结果为
----------------------------------------------------
1 mb1 2256 1 1
2 mb1 2256 1 1
3 mb1 2257 1
4 mb2 2256 1 1
5 mb2 2256 1 1
6 mb3 2261 1 1
7 mb4 2261 1
8 mb5 2255 1 第1,2项发生重复了.
大家可以去看看一.我试了test1.code,test2.code,test3.code给加上.发现一些奇怪的东西 code position 1.code 2.code 3.code 1.amount 2.amount 3.amount
-------------------------------------------------------------------------------------
1 mb1 2256 mb1 mb1 1 1
2 mb1 2256 mb1 mb2 1 1
3 mb1 2257 mb1 1
4 mb2 2256 mb2 mb1 1 1
5 mb2 2256 mb2 mb2 1 1
6 mb3 2261 mb3 mb7 1 1
7 mb4 2261 mb4 1
8 mb5 2255 mb5 1
大家可以看到.第2项,,,竟出现在mb1 mb2同行...
不知道这是什么原因...