在线等 sql联查 数据重复

[buzhidao945]

SELECT RecordNo,OperationName,StartTime,EndTIme,Memo FROM Hai_Incision
原表显示数据
0000222225 75 01.02 2012-08-29 2012-08-30 1002
0000222239 78 02.31 2012-09-19 2012-09-20 0001

SELECT a.RecordNo,a.OperationName,a.StartTime,a.EndTIme,a.Memo
,i.data_name as ONName
FROM Hai_Incision as a
left outer join operations_dict i on a.OperationName=i.data_code
显示数据
0000222225 75 01.02 2012-08-29 2012-08-30 1002 手术1
0000222239 78 02.31 2012-09-19 2012-09-20 0001 手术2
0000222239 78 02.31 2012-09-19 2012-09-20 0001 手术2

operations_dict 的data_code不是唯一的 02.31 有两条数据
想让他显示的时候 重复数据只显示一条

[gua84607583]

SELECT distinct a.RecordNo,a.OperationName,a.StartTime,a.EndTIme,a.Memo
,i.data_name as ONName
FROM Hai_Incision as a
left outer join operations_dict i on a.OperationName=i.data_code

[buzhidao945]

gua84607583 还是显示3条数据啊 distinct 没起作用

[buzhidao945]

operations_dict
data_code name
01.01 植物手术
02.31 手术1
02.31 生物手术

[caoyang0299]

完全重复才能distinct
left join 就会产生重复数据，想去掉的话，加编码，case when 去掉吧

[以学习为目的]

直接用join试试

[buzhidao945]

caoyang0299 case when怎么加呢，菜鸟，求解释

[Mr_yang_da]

SELECT distinct(RecordNo) as a ,OperationName,StartTime,EndTIme,Memo FROM Hai_Incision order by a

[aitaoer_wkx]

SELECT a.RecordNo,a.OperationName,a.StartTime,a.EndTIme,a.Memo,(select distinct i.data_name from operations_dict as i where i.data_code=a.OperationName)as ONName
FROM Hai_Incision
不知道正不正确 你试试

[IEEE_China]

SELECT a.RecordNo,a.OperationName,a.StartTime,a.EndTIme,a.Memo
,i.data_name as ONName
 FROM Hai_Incision as a  
 left outer join operations_dict i on a.OperationName=i.data_code  
group by a.RecordNo,a.OperationName,a.StartTime,a.EndTIme,a.Memo
,i.data_name 

[小海葵1]

小改一下，希望能帮助LZ
SELECT a.RecordNo,a.OperationName,a.StartTime,a.EndTIme,a.Memo
,i.data_name as ONName
FROM Hai_Incision as a
where exists(select 1 from operations_dict i where i.data_code = a.OperationName)

[汤姆克鲁斯]

[Quote=引用 3 楼 的回复:]

operations_dict
data_code name
01.01 植物手术
02.31 手术1
02.31 生物手术
[/Quote]
这不算是重复数据
关键是 同一个data_code 你要取那个了

[發糞塗牆]

如果你要取那么多列，那不存在重复的问题，如果你只要a.RecordNo,a.OperationName，那才算重复，用个distinct就可以拉

[hanlu0012]

SELECT distinct a.RecordNo,a.OperationName,a.StartTime,a.EndTIme,a.Memo
,i.data_name as ONName
FROM Hai_Incision as a
left outer join operations_dict i on a.OperationName=i.data_code

[buzhidao945]

多谢各位的帮助，本人已经解决了