Question

oracle 要查询 多个字段 但是要过滤掉 重复的数据 sql 语句怎么写啊

SELECT sequence_no, channel_id, base_id, product_mode,model_code,rated_voltage, spec, brand_name, show_flag, model_id FROM table_name where user_id=1

主要是想过滤model_id 这个字段的
就是想把 最后一列 model_id 重复的数据去掉 只显示一条就可以了

Answer 1

加distinct，
SELECT distinct sequence_no, channel_id, base_id, product_mode,model_code,rated_voltage, spec, brand_name, show_flag, model_id
FROM table_name
where user_id=1

查出来是没有重复记录的，如果想要model_id 没有重复，还需要做别的条件的限制

Answer 2

SELECT sequence_no, channel_id, base_id, product_mode,model_code,rated_voltage, spec, brand_name, show_flag,
MAX (col) OVER (partition by model_id order by model_id)
FROM table_name
where user_id=1
group by sequence_no, channel_id, base_id, product_mode,model_code,rated_voltage, spec, brand_name, show_flag;

Answer 3

select sequence_no,
channel_id,
base_id,
product_mode,
model_code,
rated_voltage,
spec,
brand_name,
show_flag,
model_id
FROM table_name
where user_id = 1
and
rowid in (select min(rowid) from table_name t1 where t.model_id = t1.model_id)

Answer 4

SELECT distinct sequence_no, channel_id, base_id, product_mode,model_code,rated_voltage, spec, brand_name, show_flag, model_id FROM table_name where user_id=1

追问

distinct 不行 数据库里总共有12条数据数 用distinct 还是查询出12条 用group by 分组 还是 12条 晕 求解 求正确的 sql 语句

追答

那就说明这么多字段没有重复的记录

追问

不是  model_id  这个字段有很多重复的  看结果集  你看 好几个 TM000042

追答

select sequence_no,
channel_id,
base_id,
product_mode,
model_code,
rated_voltage,
spec,
brand_name,
show_flag,
model_id
FROM table_name
where user_id = 1 and rowid in (select max(rowid) from table_name t1 where t.model_id = t1.model_id)