补充一点,要按用户生成序号(用户id相同,序号相同,否则不同),如:
id date tmpvalue no
001002 20041102 va2 1
001abc 20041101 vc1 1
002003 20041105 va2 2
003lmn 20041114 vc1 3
id date tmpvalue no
001002 20041102 va2 1
001abc 20041101 vc1 1
002003 20041105 va2 2
003lmn 20041114 vc1 3
tmpvalue是a.valuea或b.valuec
no是动态生成的。
2 001002 va2 vb2 20041102
3 002003 va2 vb3 20041105
4 002004 va4 vb2 20041114select * from z2 T;1 001abc vc1 20041101
2 001def vc2 20041102
3 002ghi vc3 20041105
4 002lmn vc4 20041114
5 003lmn vc1 20041114SELECT T.*, DENSE_RANK() OVER(ORDER BY SUBSTR(T.ID, 1, 3)) NO
FROM (SELECT Z1.ID ID, Z1.DATE1 DATE1, Z1.VALUEA TMPVALUE
FROM Z1, Z2
WHERE SUBSTR(Z1.ID, 1, 3) = SUBSTR(Z2.ID, 1, 3)
AND INSTR(Z1.VALUEA, '2') > 0
AND INSTR(Z2.VALUEC, '1') > 0
UNION
SELECT Z2.ID ID, Z2.DATE1 DATE1, Z2.VALUEC TMPVALUE
FROM Z1, Z2
WHERE SUBSTR(Z1.ID, 1, 3) = SUBSTR(Z2.ID, 1, 3)
AND INSTR(Z1.VALUEA, '2') > 0
AND INSTR(Z2.VALUEC, '1') > 0) T1 001002 20041102 va2 1
2 001abc 20041101 vc1 1
SELECT T.*, DENSE_RANK() OVER(ORDER BY SUBSTR(T.ID, 1, 3)) NO
FROM (SELECT Z1.ID ID, Z1.DATE1 DATE1, Z1.VALUEA TMPVALUE
FROM Z1, Z2
WHERE INSTR(Z1.VALUEA, '2') > 0
UNION
SELECT Z2.ID ID, Z2.DATE1 DATE1, Z2.VALUEC TMPVALUE
FROM Z1, Z2
WHERE INSTR(Z2.VALUEC, '1') > 0) T
1 001002 20041102 va2 1
2 001abc 20041101 vc1 1
3 002003 20041105 va2 2
4 003lmn 20041114 vc1 3
那么上面的查询就又不对了:(我的问题在ORACLE也提了。
请参照。
http://community.csdn.net/Expert/topic/3593/3593626.xml?temp=.9952967
“那么上面的查询就又不对了:( ”
没弄懂??
那么就不该有b表的数据。
如果只检索b.valuec="...",那就没有a表数据。只有检索条件涉及到2张表时,才对2张表都检索。
问题一览的上面
select hxltest005.ID,valuea as value ,hxltest005.dat from hxltest005,hxltest006
where valuea like '%2'
and substr(hxltest005.ID,1,3)=substr(hxltest006.ID,1,3)
and valuec like '%1'
union
select hxltest006.ID,valuec as value ,hxltest006.dat from hxltest006,hxltest005
where valuec like '%1'
and substr(hxltest005.ID,1,3)=substr(hxltest006.ID,1,3)
and valuea like '%2'
select hxltest006.ID,valuec as value ,hxltest006.dat from hxltest006,hxltest005
where valuec like '%1'
union
select hxltest005.ID,valuea as value ,hxltest005.dat from hxltest005,hxltest006
where substr(hxltest005.ID,1,3)=substr(hxltest006.ID,1,3)
and valuea like '%2'