不显示删除回复显示所有回复显示星级回复显示得分回复 如何通过几个字段来判断2条数据是否重复，并只取一条！！

[zhang123wei456]

SELECT CONVERT(varchar(20), a.autonumb) Chg_Cards_His, CONVERT(varchar(20),
a.autonumb) Chg_Invoice, CONVERT(varchar(20), a.autonumb) Chg_Advic,
b.medi_code Chg_HIS_Code, '01' Chg_Pat_Style, b.itemamou chg_Price,
c.opercode Chg_Doctor_Code, c.opername Chg_Doctor_Name,
a.creadate Chg_Exec_Time, '0' Chg_Exec_Rate, space(50) Chg_Operater, getdate()
Chg_Operate_Time, '0' Chg_State, a.patiname Chg_Pat_Name, CONVERT(varchar(2),
a.sex) Chg_Pat_Sex, CONVERT(varchar(10), a.age) Chg_Pat_Age, ' ' Chg_Pat_bed,
space(50) Chg_Pat_Dgs, d .officecode Chg_Pat_Dept_Code,
d .officename Chg_Pat_Dept_Name, space(50) Chg_Pat_Corp, space(50)
Chg_Ar_Code, '0' Chg_sys_state
FROM nclininvoinfo a INNER JOIN
nclinreciinfo b ON a.autonumb = b.autonumb AND b.charge_id IN (8, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 145, 147) LEFT OUTER JOIN
dictoper c ON a.doctid = c.operid LEFT OUTER JOIN
dictoffice d ON a.offiid = d .officeid

如上查出来的结果为：（只列举了前6个字段内容，所有字段就是chg_Price不同，其他相同）
Chg_Cards_His Chg_Invoice Chg_Advic Chg_HIS_Code Chg_Pat_Style chg_Price
130069534 130069534 130069534 JYJMCX 01 4.0
130069534 130069534 130069534 JYJMCX 01 16.0
我想通过Chg_Cards_His和Chg_HIS_Code 如果这2个字段相同的话 只随机查找一条出来，另外一条不要，请问如何实现 谢谢大家！！上面的SQL语句如果修改！！！！

[叶子]

;with maco as 
(
  SELECT CONVERT(varchar(20), a.autonumb) Chg_Cards_His, CONVERT(varchar(20),  
  a.autonumb) Chg_Invoice, CONVERT(varchar(20), a.autonumb) Chg_Advic,  
  b.medi_code Chg_HIS_Code, '01' Chg_Pat_Style, b.itemamou chg_Price,  
  c.opercode Chg_Doctor_Code, c.opername Chg_Doctor_Name,  
  a.creadate Chg_Exec_Time, '0' Chg_Exec_Rate, space(50) Chg_Operater, getdate()  
  Chg_Operate_Time, '0' Chg_State, a.patiname Chg_Pat_Name, CONVERT(varchar(2),  
  a.sex) Chg_Pat_Sex, CONVERT(varchar(10), a.age) Chg_Pat_Age, ' ' Chg_Pat_bed,  
  space(50) Chg_Pat_Dgs, d .officecode Chg_Pat_Dept_Code,  
  d .officename Chg_Pat_Dept_Name, space(50) Chg_Pat_Corp, space(50)  
  Chg_Ar_Code, '0' Chg_sys_state
  FROM nclininvoinfo a INNER JOIN
  nclinreciinfo b ON a.autonumb = b.autonumb AND b.charge_id IN (8, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 145, 147) LEFT OUTER JOIN
  dictoper c ON a.doctid = c.operid LEFT OUTER JOIN
  dictoffice d ON a.offiid = d .officeid
)

select * from maco t where 
chg_Price=(select max(chg_Price) from maco 
where Chg_Cards_His=t.Chg_Cards_His and Chg_HIS_Code =t.Chg_HIS_Code )

[-晴天]

;with cte as(
SELECT CONVERT(varchar(20), a.autonumb) Chg_Cards_His, CONVERT(varchar(20),  
  a.autonumb) Chg_Invoice, CONVERT(varchar(20), a.autonumb) Chg_Advic,  
  b.medi_code Chg_HIS_Code, '01' Chg_Pat_Style, b.itemamou chg_Price,  
  c.opercode Chg_Doctor_Code, c.opername Chg_Doctor_Name,  
  a.creadate Chg_Exec_Time, '0' Chg_Exec_Rate, space(50) Chg_Operater, getdate()  
  Chg_Operate_Time, '0' Chg_State, a.patiname Chg_Pat_Name, CONVERT(varchar(2),  
  a.sex) Chg_Pat_Sex, CONVERT(varchar(10), a.age) Chg_Pat_Age, ' ' Chg_Pat_bed,  
  space(50) Chg_Pat_Dgs, d .officecode Chg_Pat_Dept_Code,  
  d .officename Chg_Pat_Dept_Name, space(50) Chg_Pat_Corp, space(50)  
  Chg_Ar_Code, '0' Chg_sys_state
FROM nclininvoinfo a INNER JOIN
  nclinreciinfo b ON a.autonumb = b.autonumb AND b.charge_id IN (8, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 145, 147) LEFT OUTER JOIN
  dictoper c ON a.doctid = c.operid LEFT OUTER JOIN
  dictoffice d ON a.offiid = d .officeid
)
select * from cte a where not exists(
	select 1 from cte where Chg_Cards_His=a.Chg_Cards_His and Chg_HIS_Code=a.Chg_HIS_Code and chg_Price>a.chg_Price)

[zhang123wei456]

；附近有语法错误，是怎么回事呢？

[-晴天]

注意,那是个半角的分号!

[AcHerat]

SELECT CONVERT(varchar(20), a.autonumb) Chg_Cards_His, CONVERT(varchar(20), 
  a.autonumb) Chg_Invoice, CONVERT(varchar(20), a.autonumb) Chg_Advic, 
  b.medi_code Chg_HIS_Code, '01' Chg_Pat_Style, b.itemamou chg_Price, 
  c.opercode Chg_Doctor_Code, c.opername Chg_Doctor_Name, 
  a.creadate Chg_Exec_Time, '0' Chg_Exec_Rate, space(50) Chg_Operater, getdate() 
  Chg_Operate_Time, '0' Chg_State, a.patiname Chg_Pat_Name, CONVERT(varchar(2), 
  a.sex) Chg_Pat_Sex, CONVERT(varchar(10), a.age) Chg_Pat_Age, ' ' Chg_Pat_bed, 
  space(50) Chg_Pat_Dgs, d .officecode Chg_Pat_Dept_Code, 
  d .officename Chg_Pat_Dept_Name, space(50) Chg_Pat_Corp, space(50) 
  Chg_Ar_Code, '0' Chg_sys_state
  INTO  #temp
FROM nclininvoinfo a INNER JOIN
  nclinreciinfo b ON a.autonumb = b.autonumb AND b.charge_id IN (8, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 145, 147) LEFT OUTER JOIN
  dictoper c ON a.doctid = c.operid LEFT OUTER JOIN
  dictoffice d ON a.offiid = d .officeid

select * from #temp t where not exists(
    select 1 from #temp where Chg_Cards_His=t.Chg_Cards_His and Chg_HIS_Code=t.Chg_HIS_Code and chg_Price>t.chg_Price)

[zhang123wei456]

我是做的一个视图，用你们的方法貌似只能查一次，在查询一次的话 提示“数据库中已存在名为#temp的对象”

[zhang123wei456]

如何解决呢 我是新手！！ 谢谢高手们的指点！

[AcHerat]

create view tp
as
SELECT CONVERT(varchar(20), a.autonumb) Chg_Cards_His, CONVERT(varchar(20), 
  a.autonumb) Chg_Invoice, CONVERT(varchar(20), a.autonumb) Chg_Advic, 
  b.medi_code Chg_HIS_Code, '01' Chg_Pat_Style, b.itemamou chg_Price, 
  c.opercode Chg_Doctor_Code, c.opername Chg_Doctor_Name, 
  a.creadate Chg_Exec_Time, '0' Chg_Exec_Rate, space(50) Chg_Operater, getdate() 
  Chg_Operate_Time, '0' Chg_State, a.patiname Chg_Pat_Name, CONVERT(varchar(2), 
  a.sex) Chg_Pat_Sex, CONVERT(varchar(10), a.age) Chg_Pat_Age, ' ' Chg_Pat_bed, 
  space(50) Chg_Pat_Dgs, d .officecode Chg_Pat_Dept_Code, 
  d .officename Chg_Pat_Dept_Name, space(50) Chg_Pat_Corp, space(50) 
  Chg_Ar_Code, '0' Chg_sys_state
FROM nclininvoinfo a INNER JOIN
  nclinreciinfo b ON a.autonumb = b.autonumb AND b.charge_id IN (8, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 145, 147) LEFT OUTER JOIN
  dictoper c ON a.doctid = c.operid LEFT OUTER JOIN
  dictoffice d ON a.offiid = d .officeid



select * from tp t where not exists(
    select 1 from tp where Chg_Cards_His=t.Chg_Cards_His and Chg_HIS_Code=t.Chg_HIS_Code and chg_Price>t.chg_Price)

[zhang123wei456]

如果字段chg_Price有相同的，也有不同的 那怎么办？

[叶子]

[Quote=引用 9 楼 zhang123wei456 的回复:]
如果字段chg_Price有相同的，也有不同的 那怎么办？
[/Quote]

;with maco as 
(select row_number() over (partition by Chg_Cards_His,Chg_HIS_Code order by Chg_Invoice,Chg_Advic,chg_Price)
  as id,* from (
  SELECT CONVERT(varchar(20), a.autonumb) Chg_Cards_His, CONVERT(varchar(20),  
  a.autonumb) Chg_Invoice, CONVERT(varchar(20), a.autonumb) Chg_Advic,  
  b.medi_code Chg_HIS_Code, '01' Chg_Pat_Style, b.itemamou chg_Price,  
  c.opercode Chg_Doctor_Code, c.opername Chg_Doctor_Name,  
  a.creadate Chg_Exec_Time, '0' Chg_Exec_Rate, space(50) Chg_Operater, getdate()  
  Chg_Operate_Time, '0' Chg_State, a.patiname Chg_Pat_Name, CONVERT(varchar(2),  
  a.sex) Chg_Pat_Sex, CONVERT(varchar(10), a.age) Chg_Pat_Age, ' ' Chg_Pat_bed,  
  space(50) Chg_Pat_Dgs, d .officecode Chg_Pat_Dept_Code,  
  d .officename Chg_Pat_Dept_Name, space(50) Chg_Pat_Corp, space(50)  
  Chg_Ar_Code, '0' Chg_sys_state
  FROM nclininvoinfo a INNER JOIN
  nclinreciinfo b ON a.autonumb = b.autonumb AND b.charge_id IN (8, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 145, 147) LEFT OUTER JOIN
  dictoper c ON a.doctid = c.operid LEFT OUTER JOIN
  dictoffice d ON a.offiid = d .officeid) aa
)

select t.Chg_Cards_His,t.Chg_Invoice,t.Chg_Advic,t.Chg_HIS_Code,t.chg_Price 
from maco t where 
id=(select max(id) from maco 
where Chg_Cards_His=t.Chg_Cards_His and Chg_HIS_Code=t.Chg_HIS_Code)

[叶子]

declare @table table (col1 int,col2 int,col3 int,col4 int,col5 int)
insert into @table
select 1,2,3,2,1 union all
select 1,2,4,2,1 union all
select 1,2,3,2,2 union all
select 1,2,4,2,2 union all
select 2,2,3,3,1 union all
select 2,2,4,3,1 union all
select 2,2,3,3,2 union all
select 2,2,4,3,2

;with maco as(
select row_number() over (partition by col1,col4 order by col2,col3,col5)
as id,* from @table
) 

select t.col1,t.col2,t.col3,t.col4,t.col5 from maco t where 
id=(select max(id) from maco 
where col1=t.col1 and col4=t.col4)
/*
col1        col2        col3        col4        col5
----------- ----------- ----------- ----------- -----------
1           2           4           2           2
2           2           4           3           2
*/

[Shawn]

create view vw_test as
SELECT CONVERT(varchar(20), a.autonumb) Chg_Cards_His, CONVERT(varchar(20), 
  a.autonumb) Chg_Invoice, CONVERT(varchar(20), a.autonumb) Chg_Advic, 
  b.medi_code Chg_HIS_Code, '01' Chg_Pat_Style, b.itemamou chg_Price, 
  c.opercode Chg_Doctor_Code, c.opername Chg_Doctor_Name, 
  a.creadate Chg_Exec_Time, '0' Chg_Exec_Rate, space(50) Chg_Operater, getdate() 
  Chg_Operate_Time, '0' Chg_State, a.patiname Chg_Pat_Name, CONVERT(varchar(2), 
  a.sex) Chg_Pat_Sex, CONVERT(varchar(10), a.age) Chg_Pat_Age, ' ' Chg_Pat_bed, 
  space(50) Chg_Pat_Dgs, d .officecode Chg_Pat_Dept_Code, 
  d .officename Chg_Pat_Dept_Name, space(50) Chg_Pat_Corp, space(50) 
  Chg_Ar_Code, '0' Chg_sys_state
FROM nclininvoinfo a INNER JOIN
  nclinreciinfo b ON a.autonumb = b.autonumb AND b.charge_id IN (8, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 145, 147) LEFT OUTER JOIN
  dictoper c ON a.doctid = c.operid LEFT OUTER JOIN
  dictoffice d ON a.offiid = d .officeid
GO
select b.* from
(select distinct Chg_Cards_His, Chg_HIS_Code from vw_test)  a
cross apply
(select top(1) * from vw_test where Chg_Cards_His = a.Chg_Cards_His and Chg_HIS_Code = a.Chg_HIS_Code order by NEWID()) b
GO