子查询解决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.9600.901
cq00000510872007-09-28 16:33:41.807101
cq00000560892007-09-28 16:37:20.413101
0251顾建平2007-09-29 12:28:11.4500.901
0268毛卫红2007-09-29 12:33:06.5130.901
0269王平2007-09-29 13:49:41.1870.901
0295娄波2007-09-29 19:34:42.3900.901
0297丁福东2007-09-29 19:37:02.6400.901
0256李华凤2008-04-02 13:07:34.4670.901
0302张晏2007-10-01 18:08:48.6400.901
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'
