Question

sql 查询去除重复行

需要去除重复的行并求每行的余额(缴费合计 减去 费用合计)
语句如下：
select
SFM_BH as 住院号,SFM_XM as 姓名,SFM_JZBZ 标志, SFM_JF 缴费合计,SFM_DF 费用合计,SFM_YE 余额 from
(select SFD_BH,SFD_CZY,SFD_RQ,SFD_ZFLB,SFD_ZY from ZY_SFD
where
SFD_JDLB=0
and SFD_BZ=1
and DATEDIFF(Day, SFD_RQ, '2015-09-26 15:18:00') <= 0
and DATEDIFF(Day, SFD_RQ, '2015-10-25 15:18:00') >= 0 )
as acc left
join
ZY_SFM on (SFM_BH=SFD_BH)
Where 1=1
group by
SFM_BH,SFM_XM,SFM_RYRQ,SFM_CW,SFD_RQ,SFM_JZBZ,SFM_JF,SFM_DF,SFM_YE,SFD_ZFLB
Order by
SFM_BH,convert(char(16),SFM_RYRQ,120)DESC,SFD_RQ
请专家帮忙修改下语句，谢谢！

Answer 1

首先，从img表中取数据库，将new_id重复的过滤掉，代码为
select
min(id)
from
img
group
by
new_id
------以new_id字段分组，取最小的ID，这个ID总不会重复了吧
然后将这个查询结果以虚拟表形式，作为过滤条件，取你所要的结果，代码为
select
T.new_id
AS
is,title,d_time,imgurl
from
news,Img
where
news.id
=
img.new_id
and
img.id
in
(select
min(id)
AS
img_id,new_id
from
img
group
by
new_id)

Answer 2

在select 后面加一个distinct应该就可以了，不过不建议这么做，建议找出为什么重复的原因。能否把group by所有的字段的select 结果都给我们看看
select distinct
SFM_BH as 住院号,SFM_XM as 姓名,SFM_JZBZ 标志, SFM_JF 缴费合计,SFM_DF 费用合计,SFM_YE 余额 from
(select SFD_BH,SFD_CZY,SFD_RQ,SFD_ZFLB,SFD_ZY from ZY_SFD
where
SFD_JDLB=0
and SFD_BZ=1
and DATEDIFF(Day, SFD_RQ, '2015-09-26 15:18:00') <= 0
and DATEDIFF(Day, SFD_RQ, '2015-10-25 15:18:00') >= 0 )
as acc left
join
ZY_SFM on (SFM_BH=SFD_BH)
Where 1=1
group by
SFM_BH,SFM_XM,SFM_RYRQ,SFM_CW,SFD_RQ,SFM_JZBZ,SFM_JF,SFM_DF,SFM_YE,SFD_ZFLB
Order by
SFM_BH,convert(char(16),SFM_RYRQ,120)DESC,SFD_RQ

Answer 3

select distinct
SFM_BH as 住院号,SFM_XM as 姓名,SFM_JZBZ 标志, SFM_JF 缴费合计,SFM_DF 费用合计,SFM_YE 余额 from
(select SFD_BH,SFD_CZY,SFD_RQ,SFD_ZFLB,SFD_ZY from ZY_SFD
where
SFD_JDLB=0
and SFD_BZ=1
and DATEDIFF(Day, SFD_RQ, '2015-09-26 15:18:00') <= 0
and DATEDIFF(Day, SFD_RQ, '2015-10-25 15:18:00') >= 0 )
as acc left
join
ZY_SFM on (SFM_BH=SFD_BH)
Where 1=1
group by
SFM_BH,SFM_XM,SFM_RYRQ,SFM_CW,SFD_RQ,SFM_JZBZ,SFM_JF,SFM_DF,SFM_YE,SFD_ZFLB
Order by
SFM_BH,convert(char(16),SFM_RYRQ,120)DESC,SFD_RQ

追问

Answer 4

order by (select 1)与order by 1一样按第一列排序,按照查询的结果集第一列排序