表中数据如下:
a b c d
6013821500001567414 0 0 2
6013821500001567414 0 0 2
6013821500001567414 0 0 2
6013821500001567414 0 0 2
6013821500001567414 0 0 1
6013821500001567414 0 0 1
6013821500001567414 0 0 1要把d=1的记录相同的合并成一条,而不等于1的所有记录都全部保留
要如何使用一条SQL语句取出如上条件的记录呢?希望实践证明语句的可行性谢谢
a b c d
6013821500001567414 0 0 2
6013821500001567414 0 0 2
6013821500001567414 0 0 2
6013821500001567414 0 0 2
6013821500001567414 0 0 1
6013821500001567414 0 0 1
6013821500001567414 0 0 1要把d=1的记录相同的合并成一条,而不等于1的所有记录都全部保留
要如何使用一条SQL语句取出如上条件的记录呢?希望实践证明语句的可行性谢谢
select * from(
select tt.*,row_number()over(partition by d order by rownum)rn
from tt)
where rn=1 or d<>1
union all select '6013821500001567414',0,0,2 from dual
union all select '6013821500001567414',0,0,2 from dual
union all select '6013821500001567414',0,0,2 from dual
union all select '6013821500001567414',0,0,1 from dual
union all select '6013821500001567414',0,0,1 from dual
union all select '6013821500001567414',0,0,1 from dual)
select a,b,c,d from(
select tt.*,row_number()over(partition by d order by rownum)rn
from tt)
where rn=1 or d <>1 A B C D
6013821500001567414 0 0 1
6013821500001567414 0 0 2
6013821500001567414 0 0 2
6013821500001567414 0 0 2
6013821500001567414 0 0 2
from table_name
where d<>1
union all
select distinct a,b,c,d
from table_name
whre d=1
;
union all
select distinct a,b,c,d from table_name where d=1
FROM 表名称 AS TT
WHERE TT.D = '1'
UNION ALL
SELECT TT.A ,TT.B ,TT.C ,TT.D
FROM 表名称 AS TT
WHERE TT.D <> '1'