单表多重搜索如何避免重复记录发生SQL

[百事洞明]

select rq as 日期,kh as 业主名称,
zy as 款项属性,
(select je from cwsr as b where A.rq=b.rq and A.kh=b.kh and lpxm='现金') as 现金,
(select je from cwsr as b where A.rq=b.rq and A.kh=b.kh and lpxm='刷卡') as 刷卡,
(select je from cwsr as b where A.rq=b.rq and A.kh=b.kh and lpxm='转帐') as 转帐,
(select fh from fy where kh=A.kh) as 房号
from CWSR as A where rq='2017-04-16 00:00:00'

同天刷卡现金两条记录想显示为一条记录，不要两条记录重复，如何修改语句？
望高手解答

见图

[百事洞明]

[二月十六]

SELECT DISTINCT
        rq AS 日期 ,
        kh AS 业主名称 ,
        zy AS 款项属性 ,
        ( SELECT    je
          FROM      cwsr AS b
          WHERE     A.rq = b.rq
                    AND A.kh = b.kh
                    AND lpxm = '现金'
        ) AS 现金 ,
        ( SELECT    je
          FROM      cwsr AS b
          WHERE     A.rq = b.rq
                    AND A.kh = b.kh
                    AND lpxm = '刷卡'
        ) AS 刷卡 ,
        ( SELECT    je
          FROM      cwsr AS b
          WHERE     A.rq = b.rq
                    AND A.kh = b.kh
                    AND lpxm = '转帐'
        ) AS 转帐 ,
        ( SELECT    fh
          FROM      fy
          WHERE     kh = A.kh
        ) AS 房号
FROM    CWSR AS A
WHERE   rq = '2017-04-16 00:00:00';

[道素]

你是想统计同一天，同个业主，同一房号的统计吗?可以试试下面的

SELECT 
        CONVERT(VARCHAR,rq,101) AS 日期 ,
        kh AS 业主名称 ,
        zy AS 款项属性 ,
        
        ,SUM(CASE WHEN lpxm = '现金' THEN je ELSE 0 END ) AS 现金
        ,SUM(CASE WHEN lpxm = '刷卡' THEN je ELSE 0 END ) AS 刷卡
        ,SUM(CASE WHEN lpxm = '转帐' THEN je ELSE 0 END ) AS 转帐
        ,SUM(CASE WHEN lpxm = '转帐' THEN je ELSE 0 END ) AS 现金   
        ,fh AS 房号,
FROM    CWSR AS A
WHERE   DATEDIFF(d,rq,GETDATE())=0 --DATEDIFF(d,rq,'2017-04-16 00:00:00')=0 
GROUP BY CONVERT(VARCHAR,rq,101),kh,zy,fh

如果想统计的不是这么明细，那么将去从group by中去掉

[道素]

更正一下SQL

SELECT 
        CONVERT(VARCHAR,rq,101) AS 日期 ,
        kh AS 业主名称 ,
        zy AS 款项属性
        ,SUM(CASE WHEN lpxm = '现金' THEN je ELSE 0 END ) AS 现金
        ,SUM(CASE WHEN lpxm = '刷卡' THEN je ELSE 0 END ) AS 刷卡
        ,SUM(CASE WHEN lpxm = '转帐' THEN je ELSE 0 END ) AS 转帐
        ,fh AS 房号,
FROM    CWSR AS A
WHERE   DATEDIFF(d,rq,GETDATE())=0 --DATEDIFF(d,rq,'2017-04-16 00:00:00')=0 
GROUP BY CONVERT(VARCHAR,rq,101),kh,zy,fh

[百事洞明]

现在的问题是：上面表记录中第3行与第4行为同一人，能否在一条记录中显示 即 华恩享 房款 100000 10000 合计110000 的效果

[百事洞明]

引用 4 楼 ch21st 的回复:

更正一下SQL

SELECT 
        CONVERT(VARCHAR,rq,101) AS 日期 ,
        kh AS 业主名称 ,
        zy AS 款项属性
        ,SUM(CASE WHEN lpxm = '现金' THEN je ELSE 0 END ) AS 现金
        ,SUM(CASE WHEN lpxm = '刷卡' THEN je ELSE 0 END ) AS 刷卡
        ,SUM(CASE WHEN lpxm = '转帐' THEN je ELSE 0 END ) AS 转帐
        ,fh AS 房号,
FROM    CWSR AS A
WHERE   DATEDIFF(d,rq,GETDATE())=0 --DATEDIFF(d,rq,'2017-04-16 00:00:00')=0 
GROUP BY CONVERT(VARCHAR,rq,101),kh,zy,fh

引用 4 楼 ch21st 的回复:

更正一下SQL

SELECT 
        CONVERT(VARCHAR,rq,101) AS 日期 ,
        kh AS 业主名称 ,
        zy AS 款项属性
        ,SUM(CASE WHEN lpxm = '现金' THEN je ELSE 0 END ) AS 现金
        ,SUM(CASE WHEN lpxm = '刷卡' THEN je ELSE 0 END ) AS 刷卡
        ,SUM(CASE WHEN lpxm = '转帐' THEN je ELSE 0 END ) AS 转帐
        ,fh AS 房号,
FROM    CWSR AS A
WHERE   DATEDIFF(d,rq,GETDATE())=0 --DATEDIFF(d,rq,'2017-04-16 00:00:00')=0 
GROUP BY CONVERT(VARCHAR,rq,101),kh,zy,fh

谢谢你的回复
数据 是对了，但想同姓名的做成一条记录可以吗？
摘要无所谓，金额为一行，可以的话，合计付款金额

[百事洞明]

数据 是对了，但想同姓名的做成一条记录可以吗？
摘要无所谓，金额为一行，可以的话，合计付款金额

[二月十六]

引用 6 楼 zjg528 的回复:

数据 是对了，但想同姓名的做成一条记录可以吗？ 摘要无所谓，金额为一行，可以的话，合计付款金额

摘要无所谓 哪个字段‘无所谓’就把从group by中去掉就可以了

SELECT 
        CONVERT(VARCHAR,rq,101) AS 日期 ,
        kh AS 业主名称 
        ,SUM(CASE WHEN lpxm = '现金' THEN je ELSE 0 END ) AS 现金
        ,SUM(CASE WHEN lpxm = '刷卡' THEN je ELSE 0 END ) AS 刷卡
        ,SUM(CASE WHEN lpxm = '转帐' THEN je ELSE 0 END ) AS 转帐
        ,fh AS 房号
FROM    CWSR AS A
WHERE   DATEDIFF(d,rq,GETDATE())=0 --DATEDIFF(d,rq,'2017-04-16 00:00:00')=0 
GROUP BY CONVERT(VARCHAR,rq,101),kh,fh

[道素]

引用 8 楼 zjg528 的回复:

现在的问题是：上面表记录中第3行与第4行为同一人，能否在一条记录中显示 即 华恩享 房款 100000 10000 合计110000 的效果

你需要把Group By条件变成kh，因为你现在就是想统计每个业主的付款合计

SELECT 
        kh AS 业主名称 ,
        ,SUM(CASE WHEN lpxm = '现金' THEN je ELSE 0 END ) AS 现金
        ,SUM(CASE WHEN lpxm = '刷卡' THEN je ELSE 0 END ) AS 刷卡
        ,SUM(CASE WHEN lpxm = '转帐' THEN je ELSE 0 END ) AS 转帐
        ,SUM( je ) AS 合计款项
FROM    CWSR AS A
WHERE   DATEDIFF(d,rq,GETDATE())=0 --DATEDIFF(d,rq,'2017-04-16 00:00:00')=0 
GROUP BY kh
--如果不同日期的也要合计，将日期也去掉