我想查询一个表的某个字段，该字段的值不能重复，但是有另一个字段，另一个字段值不相同的时候可以重复。
例如
id id1 num date type
1 1001 100 2010-01-01 00:00:00.000 1
1 1002 100 2010-01-01 00:00:00.000 1
1 1003 NULL 2010-01-01 00:00:00.000 1
2 1001 NULL 2010-01-02 00:00:00.000 1
2 1002 100 2010-01-02 00:00:00.000 1
2 1003 NULL 2010-01-02 00:00:00.000 1
1 1001 100 2010-01-01 00:00:00.000 2
1 1002 100 2010-01-01 00:00:00.000 2
1 1003 100 2010-01-01 00:00:00.000 2
现在我要查询 日期不重复，但type不同的时候可以重复。
------解决方案--------------------
结果呢
------解决方案--------------------
select *
from tb t
where not exists(select 1 from tb where type=t.type and datediff(dd,[date],t.[date])=0 and id1>t.id1)
------解决方案--------------------
頂樓上- -
------解决方案--------------------
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[id1] int,[num] int,[date] datetime,[type] int)
insert [tb]
select 1,1001,100,'2010-01-01 00:00:00.000',1 union all
select 1,1002,100,'2010-01-01 00:00:00.000',1 union all
select 1,1003,null,'2010-01-01 00:00:00.000',1 union all
select 2,1001,null,'2010-01-02 00:00:00.000',1 union all
select 2,1002,100,'2010-01-02 00:00:00.000',1 union all
select 2,1003,null,'2010-01-02 00:00:00.000',1 union all
select 1,1001,100,'2010-01-01 00:00:00.000',2 union all
select 1,1002,100,'2010-01-01 00:00:00.000',2 union all
select 1,1003,100,'2010-01-01 00:00:00.000',2
---查询---
select *
from tb t
where not exists(select 1 from tb where type=t.type and datediff(dd,[date],t.[date])=0 and id1>t.id1)
---结果---
id id1 num date type
----------- ----------- ----------- ------------------------------------------------------ -----------
1 1003 NULL 2010-01-01 00:00:00.000 1
2 1003 NULL 2010-01-02 00:00:00.000 1