sql重复值的筛选，经典题解，看过之后有收获

求助sql重复值的筛选，经典题解，看过之后有收获！
一个房地产开发企业在开具销售不动产发票的时候如下：


日期           姓名     门牌号       面积      单价   开具金额（元）  备注
2013-09-29     王六      1-2-101      92.32    5000    461600         全款
2013-10-29     张三      1-1-101      92.32    5000    138480         首付
2013-12-30     张三      1-1-101      92.32    5000    323120         按揭 
2013-08-29     李四      2-1-101      92.32    5000    138480         首付
2013-08-29     李四      2-1-101      92.32    5000    -138480        冲红（面积差）
2013-09-29     李四      2-1-101      92.50    5000    462500         正确的金额
2013-08-29     赵五      3-1-101      92.32    5000    138480         首付
2013-08-29     赵五      3-1-101      92.32    5000    -138480        冲红（面积差）
2013-08-29     赵五      3-1-101      92.50    5000    138750         首付（面积差）
2013-08-29     赵五      3-1-101      92.50    5000    323750         按揭（面积差）
2013-10-29     孙二      1-1-101      92.32    5000    138480         首付
2013-10-30     孙二      1-1-101      92.32    5000    323120         按揭 
2013-11-29     孙二      1-1-101      92.32    5000    -138480        冲红（面积差）
2013-11-30     孙二      1-1-101      92.32    5000    -323120        冲红 (面积差）
2013-12-29     孙二      1-1-101      92.50    5000    462500         正确的金额


适当解释一下，
第一个人 王六 全款一次付清，金额正确，无面积差调整，开具一张发票
第二个人 张三  先交了首付，然后按揭贷款，无面积差调整，开具两张发票
第三个人 李四  先交了首付，办理产权的时候有面积差，先开一张冲红发票，将之前的冲为0，然后开具一张正确金额的发票，此笔业务开具了三张发票
第四个人 赵五   先交了首付，办理产权有面积调整，先冲红，冲为0，然后分两笔开具全额发票，此笔业务开具了四张发票
第五个人 孙二   先交了首付然后按揭开具两张发票，办理产权发现有面积差，开具两张红冲发票，然后再开具一张正确的全额发票。此笔业务开具了五张发票

同样道理一个房可以开具6张发票或者7张发票，现在的问题是

如何通过sql查询，查询一条正确的结果      显示方式为

门牌号 姓名  面积  单价 总房款（就是这套房子的价款，不管开具几张发票的总和）


恳请高人指点。。。。谢谢了先

[解决办法]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-12-30 08:31:10
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)  
 
--Dec 28 2012 20:23:12 
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([日期] datetime,[姓名] varchar(4),[门牌号] varchar(7),[面积] numeric(4,2),[单价] int,[开具金额(元)] int,[备注] varchar(12),[C8] varchar(8))
insert [huang]
select '2013-09-29','王六','1-2-101',92.32,5000,461600,'全款',null union all
select '2013-10-29','张三','1-1-101',92.32,5000,138480,'首付',null union all
select '2013-12-30','张三','1-1-101',92.32,5000,323120,'按揭',null union all
select '2013-08-29','李四','2-1-101',92.32,5000,138480,'首付',null union all
select '2013-08-29','李四','2-1-101',92.32,5000,-138480,'冲红(面积差)',null union all
select '2013-09-29','李四','2-1-101',92.50,5000,462500,'正确的金额',null union all
select '2013-08-29','赵五','3-1-101',92.32,5000,138480,'首付',null union all
select '2013-08-29','赵五','3-1-101',92.32,5000,-138480,'冲红(面积差)',null union all
select '2013-08-29','赵五','3-1-101',92.50,5000,138750,'首付(面积差)',null union all
select '2013-08-29','赵五','3-1-101',92.50,5000,323750,'按揭(面积差)',null union all
select '2013-10-29','孙二','1-1-101',92.32,5000,138480,'首付',null union all
select '2013-10-30','孙二','1-1-101',92.32,5000,323120,'按揭',null union all
select '2013-11-29','孙二','1-1-101',92.32,5000,-138480,'冲红(面积差)',null union all
select '2013-11-30','孙二','1-1-101',92.32,5000,-323120,'冲红','(面积差)' union all
select '2013-12-29','孙二','1-1-101',92.50,5000,462500,'正确的金额',null
--------------开始查询--------------------------

select 门牌号, 姓名 , MIN(面积)面积,  单价, SUM([开具金额（元）] )总房款
from [huang]
GROUP BY  门牌号, 姓名 ,  单价
----------------结果----------------------------
/* 
门牌号     姓名   面积                                      单价          总房款
------- ---- --------------------------------------- ----------- -----------
1-1-101 孙二   92.32                                   5000        462500
1-1-101 张三   92.32                                   5000        461600
1-2-101 王六   92.32                                   5000        461600
2-1-101 李四   92.32                                   5000        462500
3-1-101 赵五   92.32                                   5000        462500
*/

[解决办法]

select [门牌号], [姓名] ,  cast(SUM([开具金额（元）]) /单价 as numeric(10,2)) as  面积,  单价, cast(SUM([开具金额（元）]) as numeric(10,0)) as 总房款
from [a]
GROUP BY  门牌号, 姓名 ,  单价

门牌号     姓名   面积                                      单价          总房款
------- ---- --------------------------------------- ----------- ---------------------------------------
1-1-101 孙二   92.50                                   5000        462500
1-1-101 张三   92.32                                   5000        461600
1-2-101 王六   92.32                                   5000        461600

2-1-101 李四   92.50                                   5000        462500
3-1-101 赵五   92.50                                   5000        462500

(5 行受影响)


单价和面积和总金额要是没有关系就得一个一个情况去判断了。上面的是单价*面积=总金额
[解决办法]

引用:

Quote: 引用:

if object_id('[tb]') is not null drop table [tb]
go 

create table [tb]([日期] datetime,[姓名] varchar(4),[门牌号] varchar(7),[面积] numeric(4,2),[单价] int,[开具金额(元)] int,[备注] varchar(12),[C8] varchar(8))

insert [tb]
select '2013-09-29','王六','1-2-101',92.32,5000,461600,'全款',null union all
select '2013-10-29','张三','1-1-101',92.32,5000,138480,'首付',null union all
select '2013-12-30','张三','1-1-101',92.32,5000,323120,'按揭',null union all
select '2013-08-29','李四','2-1-101',92.32,5000,138480,'首付',null union all
select '2013-08-29','李四','2-1-101',92.32,5000,-138480,'冲红(面积差)',null union all
select '2013-09-29','李四','2-1-101',92.50,5000,462500,'正确的金额',null union all
select '2013-08-29','赵五','3-1-101',92.32,5000,138480,'首付',null union all
select '2013-08-29','赵五','3-1-101',92.32,5000,-138480,'冲红(面积差)',null union all
select '2013-08-29','赵五','3-1-101',92.50,5000,138750,'首付(面积差)',null union all
select '2013-08-29','赵五','3-1-101',92.50,5000,323750,'按揭(面积差)',null union all
select '2013-10-29','孙二','1-1-101',92.32,5000,138480,'首付',null union all
select '2013-10-30','孙二','1-1-101',92.32,5000,323120,'按揭',null union all
select '2013-11-29','孙二','1-1-101',92.32,5000,-138480,'冲红(面积差)',null union all
select '2013-11-30','孙二','1-1-101',92.32,5000,-323120,'冲红','(面积差)' union all
select '2013-12-29','孙二','1-1-101',92.50,5000,462500,'正确的金额',null
go

select 门牌号, 姓名,  max(面积) as 面积,max(单价) as 单价,SUM([开具金额(元)]) as 总房款
from tb
group by 门牌号, 姓名
/*
门牌号姓名面积单价总房款
2-1-101李四92.505000462500
1-1-101孙二92.505000462500
1-2-101王六92.325000461600
1-1-101张三92.325000461600
3-1-101赵五92.505000462500
*/

您还，非常感谢您的回复，但是有一种可能就是可能最后确认的面积会小于开始开票的面积？您用max是不是就不准确了？比如还有那种面积小于开始面积的，如何解决呢？

修改了一下：

if object_id('[tb]') is not null drop table [tb]
go 

create table [tb]([日期] datetime,[姓名] varchar(4),[门牌号] varchar(7),[面积] numeric(4,2),[单价] int,[开具金额(元)] int,[备注] varchar(12),[C8] varchar(8))

insert [tb]
select '2013-09-29','王六','1-2-101',92.32,5000,461600,'全款',null union all
select '2013-10-29','张三','1-1-101',92.32,5000,138480,'首付',null union all
select '2013-12-30','张三','1-1-101',92.32,5000,323120,'按揭',null union all
select '2013-08-29','李四','2-1-101',92.32,5000,138480,'首付',null union all
select '2013-08-29','李四','2-1-101',92.32,5000,-138480,'冲红(面积差)',null union all
select '2013-09-29','李四','2-1-101',92.50,5000,462500,'正确的金额',null union all
select '2013-08-29','赵五','3-1-101',92.32,5000,138480,'首付',null union all
select '2013-08-29','赵五','3-1-101',92.32,5000,-138480,'冲红(面积差)',null union all
select '2013-08-29','赵五','3-1-101',92.50,5000,138750,'首付(面积差)',null union all
select '2013-08-29','赵五','3-1-101',92.50,5000,323750,'按揭(面积差)',null union all
select '2013-10-29','孙二','1-1-101',92.32,5000,138480,'首付',null union all
select '2013-10-30','孙二','1-1-101',92.32,5000,323120,'按揭',null union all
select '2013-11-29','孙二','1-1-101',92.32,5000,-138480,'冲红(面积差)',null union all
select '2013-11-30','孙二','1-1-101',92.32,5000,-323120,'冲红','(面积差)' union all
select '2013-12-29','孙二','1-1-101',92.50,5000,462500,'正确的金额',null
go

select 门牌号, 姓名, 面积,单价,总房款
from 
(
select *,
       ROW_NUMBER() over(PARTITION by 门牌号,姓名 order by @@servername) rownum,
       SUM([开具金额(元)]) over(PARTITION by 门牌号,姓名) 总房款,
       COUNT(*) over(PARTITION by 门牌号,姓名) as c
from tb
)t
where rownum = c
/*
门牌号姓名面积单价总房款
1-1-101孙二92.505000462500
1-1-101张三92.325000461600
1-2-101王六92.325000461600
2-1-101李四92.505000462500
3-1-101赵五92.505000462500
*/

[解决办法]

--初始数据
if object_id('[T_House]') is not null drop table [T_House]
create table [T_House]([日期] datetime, [姓名] varchar(10), [门牌号] varchar(10),  
 
                       [面积] numeric(4,2), [单价] numeric(10,2), [开票金额] numeric(10,2),
                       [备注] varchar(12))
insert [T_House]
select '2013-09-29','王六','1-2-101',92.32,5000,461600,'全款' union all
select '2013-10-29','张三','1-1-101',92.32,5000,138480,'首付' union all
select '2013-12-30','张三','1-1-101',92.32,5000,323120,'按揭' union all
select '2013-08-29','李四','2-1-101',92.32,5000,138480,'首付' union all
select '2013-08-29','李四','2-1-101',92.32,5000,-138480,'冲红(面积差)' union all
select '2013-09-29','李四','2-1-101',92.50,5000,462500,'正确的金额' union all
select '2013-08-29','赵五','3-1-101',92.32,5000,138480,'首付' union all
select '2013-08-29','赵五','3-1-101',92.32,5000,-138480,'冲红(面积差)' union all
select '2013-08-29','赵五','3-1-101',92.50,5000,138750,'首付(面积差)' union all
select '2013-08-29','赵五','3-1-101',92.50,5000,323750,'按揭(面积差)' union all
select '2013-10-29','孙二','1-1-101',92.32,5000,138480,'首付' union all
select '2013-10-30','孙二','1-1-101',92.32,5000,323120,'按揭' union all
select '2013-11-29','孙二','1-1-101',92.32,5000,-138480,'冲红(面积差)' union all
select '2013-11-30','孙二','1-1-101',92.32,5000,-323120,'冲红(面积差)' union all
select '2013-12-29','孙二','1-1-101',92.50,5000,462500,'正确的金额'
--查询
select 门牌号, 姓名, 面积, 单价, SUM(开票金额) 总房款
from T_House
group by 门牌号, 姓名, 面积, 单价
having SUM(开票金额) > 0

查看更多 下一篇