当前位置: 代码迷 >> Sql Server >> 子查询解决distinct多列的疑问,该怎么处理
  详细解决方案

子查询解决distinct多列的疑问,该怎么处理

热度:171   发布时间:2016-04-27 14:07:50.0
子查询解决distinct多列的疑问
select distinct vip_card_no,custment_name,bsopt_date,disc_rate,isall,IsMinRate
 from custment 
where bsopt_date >= '2012-2-17'
and bsopt_date < '2012-2-18' and cardtype='1' ;

8000092,王晓凤,2012-02-17 13:29:55.483,0.9,0,1
8000092,王晓凤,2012-02-17 13:29:55.500,0.9,0,1
8000095,王先生,2012-02-17 13:28:34.000,0.9,0,1
8000096,傅少峰,2012-02-17 12:51:12.000,0.9,0,1
8000097,张君,2012-02-17 12:50:19.000,0.9,0,1
8000098,张大刚,2012-02-17 12:24:04.000,0.9,0,1
8000121,鄂燕娜,2012-02-17 17:51:24.000,0.9,0,1
8000617,郭敏,2012-02-17 12:06:31.000,0.9,0,1
8000618,魏蔚,2012-02-17 12:08:29.047,0.9,0,1
8000618,魏蔚,2012-02-17 12:08:29.060,0.9,0,1
8000619,赵真,2012-02-17 11:55:34.000,0.9,0,1
8000620,王德良,2012-02-17 11:45:20.000,0.9,0,0

为避免vip_card_no重复,改写为

select vip_card_no,custment_name,bsopt_date,disc_rate,isall,IsMinRate 
 from custment as a
where not exists(select 1 from custment where bsopt_date >= '2012-2-17'
and bsopt_date < '2012-2-18' and cardtype='1' and vip_card_no=a.vip_card_no and 
no1<a.no1);

0071 刘永萍 2007-09-27 21:04:37.960 0.9 0 1
cq0000051 087 2007-09-28 16:33:41.807 1 0 1
cq0000056 089 2007-09-28 16:37:20.413 1 0 1
0251 顾建平 2007-09-29 12:28:11.450 0.9 0 1
0268 毛卫红 2007-09-29 12:33:06.513 0.9 0 1
0269 王平 2007-09-29 13:49:41.187 0.9 0 1
0295 娄波 2007-09-29 19:34:42.390 0.9 0 1
0297 丁福东 2007-09-29 19:37:02.640 0.9 0 1
0256 李华凤 2008-04-02 13:07:34.467 0.9 0 1
0302 张晏 2007-10-01 18:08:48.640 0.9 0 1

 bsopt_date >= '2012-2-17'
and bsopt_date < '2012-2-18' and cardtype='1' 这个条件没起作用……

请问怎么写才对?

------解决方案--------------------
select vip_card_no,custment_name,bsopt_date,disc_rate,isall,IsMinRate
 from custment as a
where not exists(select 1 from custment where bsopt_date >= '2012-2-17'
and bsopt_date < '2012-2-18' and cardtype='1' and vip_card_no=a.vip_card_no and
no1<a.no1)
and bsopt_date >= '2012-2-17'
and bsopt_date < '2012-2-18' and cardtype='1'
  相关解决方案