求 一个按照从大范围到小范围，级别筛选纪录的sql写法，急~~在线等！！！！

[wangfei0723]

数据
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
------------------

[wangfei0723]

再举例说明:如果数据是这样的

发表于：2008-05-11 18:10:57 楼主
数据
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
------------------

[老紫竹]

连问题都没看懂，不知道你要干什么，特别是第二个的结果，到底怎么拿到的？

[hq19880508]

我有一个看法,不知道对不对.
这个题目的意思是:取属性A中所有不同编号的第一个元组.

[liuyann]

select a,min(b) || min(c) || min(d)
from 数据
group by a;

==== 思想重于技巧 ====

reply via CSDN viewer

[liuyann]

由于您是新用户，所以特此介绍一下结贴的方法


如果您问题已经得解决，请您及时结帖给分，以感谢帮助您的朋友。

结帖方法：点击版面右上方或右下方 [管理] ，进入页面后就可以输入密码,分别给分，结帖。
或参考：
http://www.csdn.net/help/over.asp
http://topic.csdn.net/u/20080110/19/7cb462f1-cac6-4c28-848e-0a879f4fd642.html
=============================================================================
问题解决，请及时结贴。
正确结贴方法：
管理帖子-->给分-->输入密码-->结贴

==== 思想重于技巧 ====

reply via CSDN viewer

[jyj0207]

select min(A||B||C||D) feom table

[wangfei0723]

SELECT DISTINCT trim(T.A) || trim(T.B) || trim(T.C) || trim(T.D) denno
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) T

WHERE 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)
)


我做出来了，不过感觉效率低

[wangfei0723]

再举例说明:如果数据是这样的

发表于：2008-05-11 18:10:57 楼主
数据
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
------------------

[wangfei0723]

上个结果错了 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) T

WHERE 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)
)

[oracle_dba_11]

楼主要是把实际数据贴出来应该好理解点

[huoxudong125]

题好像没说清楚目的吧

[bobfang]

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
)
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);