数据
A B C D
------------------
10 57
10 57
10 58
10 58 56
10 58 07 01
10 59 06
10 59 06 01
11
11 58
------------------
A 不会为空
B,C,D没有数据的字段全部是2个半脚空格.希望取得结果 如下
A B C D
------------------
10 57
10 58
10 59 06
11
------------------
A B C D
------------------
10 57
10 57
10 58
10 58 56
10 58 07 01
10 59 06
10 59 06 01
11
11 58
------------------
A 不会为空
B,C,D没有数据的字段全部是2个半脚空格.希望取得结果 如下
A B C D
------------------
10 57
10 58
10 59 06
11
------------------
数据
A B C D
------------------
10
10 57
10 57
10 58
10 58 56
10 58 07 01
10 59 06
10 59 06 01
11
11 58
------------------
A 不会为空
B,C,D没有数据的字段全部是2个半脚空格. 希望取得结果 如下
A B C D
------------------
10
11
------------------
from 数据
group by a;
[align=center]==== 思想重于技巧 ====
[/align]
如果您问题已经得解决,请您及时结帖给分,以感谢帮助您的朋友。 结帖方法:点击版面右上方或右下方 [管理] ,进入页面后就可以输入密码,分别给分,结帖。
或参考:
http://www.csdn.net/help/over.asp
http://topic.csdn.net/u/20080110/19/7cb462f1-cac6-4c28-848e-0a879f4fd642.html
=============================================================================
问题解决,请及时结贴。
正确结贴方法:
管理帖子-->给分-->输入密码-->结贴
[align=center]==== 思想重于技巧 ====
[/align]
FROM
(SELECT A,B,C,D, CASE WHEN trim(B || C || D) IS NULL THEN 1 ELSE 0 END A,
CASE WHEN trim(B) IS NOT NULL AND trim(C || D) IS NULL THEN 2 ELSE 0 END B,
CASE WHEN trim(B) IS NOT NULL AND trim(C) IS NOT NULL AND trim(D) IS NULL THEN 3 ELSE 0 END C ,
CASE WHEN trim(B) IS NOT NULL AND trim(C) IS NOT NULL AND trim(D) IS NOT NULL THEN 4 ELSE 0 END D
FROM T_TBL) TWHERE T.A = 1
OR ( T.B = 2
AND T.A NOT IN (SELECT A FROM T_TBL WHERE trim(B || C || D) IS NULL)
)OR ( T.C = 3
AND T.A NOT IN (SELECT A FROM T_TBL WHERE trim(B || C || D) IS NULL)
AND T.B NOT IN (SELECT B FROM T_TBL WHERE trim(B) IS NOT NULL AND trim(C || D) IS NULL)
)OR ( T.D = 4
AND T.A NOT IN (SELECT A FROM T_TBL WHERE trim(B || C || D) IS NULL)
AND T.B NOT IN (SELECT B FROM T_TBL WHERE trim(B) IS NOT NULL AND trim(C || D) IS NULL)
)
我做出来了,不过感觉效率低
select min(A||B||C||D) feom table
select '10' a, '57' b, ' ' c, ' ' d from dual
union all
select '10', '57', ' ', ' ' from dual
union all
select '10', '58', ' ', ' ' from dual
union all
select '10', '58', '56', ' ' from dual
union all
select '10', '58', '07', '01' from dual
union all
select '10', '59', '06', ' ' from dual
union all
select '10', '59', '06', '01' from dual
union all
select '11', ' ', ' ', ' ' from dual
union all
select '11', '58', ' ', ' ' from dual
)
select a, b, c, d
from (select a, min(b) over(partition by a) min_b, b, min(c) c, min(d) d
from t
group by a,b)
where NOT (trim(min_b) is null and trim(b) is not null);with t as (
select '10' a, '57' b, ' ' c, ' ' d from dual
union all
select '10', '57', ' ', ' ' from dual
union all
select '10', '58', ' ', ' ' from dual
union all
select '10', '58', '56', ' ' from dual
union all
select '10', '58', '07', '01' from dual
union all
select '10', '59', '06', ' ' from dual
union all
select '10', '59', '06', '01' from dual
union all
select '11', ' ', ' ', ' ' from dual
union all
select '11', '58', ' ', ' ' from dual
union all
select '10', ' ', ' ', ' ' from dual
)
select a, b, c, d
from (select a, min(b) over(partition by a) min_b, b, min(c) c, min(d) d
from t
group by a,b)
where NOT (trim(min_b) is null and trim(b) is not null);
数据
A B C D
------------------
10
10 57
10 57 01
10 58
10 58 02
10 58 07 01
10 59 06
10 59 06 01
11 57
11 57 01
11 57 22 03
11 58 01
11 58 01 01
11 58 01 02
11 59 01 01
11 59 02 01
11 59 03 02
------------------
A 不会为空
B,C,D没有数据的字段全部是2个半脚空格. 希望取得结果 如下
A B C D
------------------
10
11 57
11 58 01
11 59 01 01
11 59 02 01
11 59 03 02
------------------
上个结果错了 11 58 01 这条结果是不要的,请参照下面的这个结果
A B C D
------------------
10
11 57
11 59 01 01
11 59 02 01
11 59 03 02
------------------
我已经做出了,但是感觉效率不好 发出来验证下--------------------------------------SELECT DISTINCT trim(T.店) || trim(T.部) || trim(T.課) || trim(T.係) denno
FROM
(SELECT 店,部,課,係, CASE WHEN trim(部 || 課 || 係) IS NULL THEN 1 ELSE 0 END A,
CASE WHEN trim(部) IS NOT NULL AND trim(課 || 係) IS NULL THEN 2 ELSE 0 END B,
CASE WHEN trim(部) IS NOT NULL AND trim(課) IS NOT NULL AND trim(係) IS NULL THEN 3 ELSE 0 END C ,
CASE WHEN trim(部) IS NOT NULL AND trim(課) IS NOT NULL AND trim(係) IS NOT NULL THEN 4 ELSE 0 END D
FROM wangf_test) TWHERE T.A = 1
OR ( T.B = 2
AND T.店 NOT IN (SELECT 店 FROM wangf_test WHERE trim(部 || 課 || 係) IS NULL)
)OR ( T.C = 3
AND T.店 NOT IN (SELECT 店 FROM wangf_test WHERE trim(部 || 課 || 係) IS NULL)
AND T.部 NOT IN (SELECT 部 FROM wangf_test WHERE trim(部) IS NOT NULL AND trim(課 || 係) IS NULL)
)OR ( T.D = 4
AND T.店 NOT IN (SELECT 店 FROM wangf_test WHERE trim(部 || 課 || 係) IS NULL)
AND T.部 NOT IN (SELECT 部 FROM wangf_test WHERE trim(部) IS NOT NULL AND trim(課 || 係) IS NULL)
)