select * from tb t where not exists (select * from tb where t.task_id=task.id and t.create_date<>null and create_date is null)
--猜一下 select * from Tb as A where (create_date is not null and) or (create_date is null and not exists(select 1 from Tb where create_date is not null and task_id=A.task_id))
--多敲了個andselect * from Tb as A where (create_date is not null) or ( create_date is null and not exists(select 1 from Tb where create_date is not null and task_id=A.task_id) )
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(id int, instance_id int, task_id int, create_date datetime ) go insert into tb select 309 , 499 , 800 , '2009-07-16' union all select 310 , 499 , 800 , null union all select 311 , 499 , 803 , null union all select 312 , 499 , 803 , null go select * from tb t where not exists (select * from tb where t.task_id=task_id and create_date is not null and t.create_date is null) /*------------ (4 行受影响) id instance_id task_id create_date ----------- ----------- ----------- ----------------------- 309 499 800 2009-07-16 00:00:00.000 311 499 803 NULL 312 499 803 NULL(3 行受影响) -------*/
-- ========================================= -- -----------t_mac 小编------------- ---希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(id int, instance_id int, task_id int, create_date datetime ) go insert into tb select 309 , 499 , 800 , '2009-07-16' union all select 310 , 499 , 800 , null union all select 311 , 499 , 803 , null union all select 312 , 499 , 803 , null go select id , instance_id , task_id, create_date=CONVERT(varchar(10),create_date,120) from tb t where not exists (select * from tb where t.task_id=task_id and create_date is not null and t.create_date is null) /*------------ (4 行受影响) id instance_id task_id create_date ----------- ----------- ----------- ----------- 309 499 800 2009-07-16 311 499 803 NULL 312 499 803 NULL(3 行受影响) -------*/
---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-07-17 09:09:14 ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([id] int,[instance_id] int,[task_id] int,[create_date] datetime) insert [tb] select 309,499,800,'2009-07-16' union all select 310,499,800,null union all select 311,499,803,null union all select 312,499,803,null --------------开始查询-------------------------- select * from Tb as A where (create_date is not null ) or (create_date is null and not exists(select 1 from Tb where create_date is not null and task_id=A.task_id)) ----------------结果---------------------------- /*id instance_id task_id create_date ----------- ----------- ----------- ----------------------- 309 499 800 2009-07-16 00:00:00.000 311 499 803 NULL 312 499 803 NULL(3 行受影响)*/
---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-07-17 09:09:14 ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([id] int,[instance_id] int,[task_id] int,[create_date] datetime) insert [tb] select 309,499,800,'2009-07-16' union all select 310,499,800,null union all select 311,499,803,null union all select 312,499,803,null --------------开始查询-------------------------- select id , instance_id , task_id, create_date=CONVERT(varchar(10),create_date,120) from Tb A where (create_date is not null ) or (create_date is null and not exists(select 1 from Tb where create_date is not null and task_id=A.task_id)) ----------------结果---------------------------- /*id instance_id task_id create_date ----------- ----------- ----------- ----------- 309 499 800 2009-07-16 311 499 803 NULL 312 499 803 NULL(3 行受影响) */
declare @table table (id int,instance_id int,task_id int,create_date datetime) insert into @table select 309,499,800,'2009-07-16' union all select 310,499,800,null union all select 311,499,803,null union all select 312,499,803,null select isnull(b.id,a.id) as id,isnull(b.instance_id,a.instance_id) as instance_id, isnull(b.task_id,a.task_id) as task_id,isnull(b.create_date,a.create_date) as create_date from (select * from @table where create_date is null)a left join (select * from @table where create_date is not null) b on a.task_id=b.task_id/* id instance_id task_id create_date ----------- ----------- ----------- ----------------------- 309 499 800 2009-07-16 00:00:00.000 311 499 803 NULL 312 499 803 NULL */
declare @tb table (id int,instance_id int,task_id int,create_date datetime) insert into @tb select 309,499,800,'2009-07-16' union all select 310,499,800,null union all select 311,499,803,null union all select 312,499,803,nullselect id,instance_id,task_id,create_date from @tb a where isnull(create_date,'') in (select top 1 isnull(create_date,'') from @tb where task_id=a.task_id order by create_date desc )/*id instance_id task_id create_date ----------- ----------- ----------- ------------------------------------------------------ 309 499 800 2009-07-16 00:00:00.000 311 499 803 NULL 312 499 803 NULL(所影响的行数为 3 行) */
if object_id('test') is not null drop table test create table test ( id bigint primary key, instance_id int, task_id int, createdate datetime ) insert into test values(309,499,800,getdate()) insert into test values(310,499,800,null) insert into test values(311,499,803,null) insert into test values(312,499,803,null) select * from test as t where createdate is not null or createdate is null and not exists (select top 1 * from test where createdate is not null and task_id=t.task_id) 499 800 2009-07-17 09:08:42.560 311 499 803 NULL 312 499 803 NULL (所影响的行数为 3 行)
if object_id('[tb]') is not null drop table [tb] create table [tb]([id] int,[instance_id] int,[task_id] int,[create_date] datetime) insert [tb] select 309,499,800,'2009-07-16' union all select 310,499,800,null union all select 311,499,803,null union all select 312,499,803,nullselect * from Tb as A WHERE NOT exists(select 1 from Tb where ISNULL(create_date,'')<> ISNULL(A.create_date,'') and task_id=A.task_id AND A.create_date IS NULL )id instance_id task_id create_date ----------- ----------- ----------- ------------------------------------------------------ 309 499 800 2009-07-16 00:00:00.000 311 499 803 NULL 312 499 803 NULL(所影响的行数为 3 行)
not exists (select * from tb where t.task_id=task.id and t.create_date<>null and create_date is null)
--猜一下
select * from Tb as A
where (create_date is not null and)
or (create_date is null and not exists(select 1 from Tb where create_date is not null and task_id=A.task_id))
where (create_date is not null)
or ( create_date is null
and not exists(select 1 from Tb where create_date is not null
and task_id=A.task_id)
)
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int, instance_id int, task_id int, create_date datetime )
go
insert into tb
select
309 , 499 , 800 , '2009-07-16' union all select
310 , 499 , 800 , null union all select
311 , 499 , 803 , null union all select
312 , 499 , 803 , null
go
select * from tb t
where
not exists (select * from tb where t.task_id=task_id and create_date is not null and t.create_date is null)
/*------------
(4 行受影响)
id instance_id task_id create_date
----------- ----------- ----------- -----------------------
309 499 800 2009-07-16 00:00:00.000
311 499 803 NULL
312 499 803 NULL(3 行受影响)
-------*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int, instance_id int, task_id int, create_date datetime )
go
insert into tb
select
309 , 499 , 800 , '2009-07-16' union all select
310 , 499 , 800 , null union all select
311 , 499 , 803 , null union all select
312 , 499 , 803 , null
go
select id ,
instance_id ,
task_id,
create_date=CONVERT(varchar(10),create_date,120)
from tb t
where
not exists (select * from tb where t.task_id=task_id and create_date is not null and t.create_date is null)
/*------------
(4 行受影响)
id instance_id task_id create_date
----------- ----------- ----------- -----------
309 499 800 2009-07-16
311 499 803 NULL
312 499 803 NULL(3 行受影响)
-------*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-17 09:09:14
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[instance_id] int,[task_id] int,[create_date] datetime)
insert [tb]
select 309,499,800,'2009-07-16' union all
select 310,499,800,null union all
select 311,499,803,null union all
select 312,499,803,null
--------------开始查询--------------------------
select * from Tb as A
where (create_date is not null )
or (create_date is null and not exists(select 1 from Tb where create_date is not null and task_id=A.task_id))
----------------结果----------------------------
/*id instance_id task_id create_date
----------- ----------- ----------- -----------------------
309 499 800 2009-07-16 00:00:00.000
311 499 803 NULL
312 499 803 NULL(3 行受影响)*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-17 09:09:14
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([id] int,[instance_id] int,[task_id] int,[create_date] datetime)
insert [tb]
select 309,499,800,'2009-07-16' union all
select 310,499,800,null union all
select 311,499,803,null union all
select 312,499,803,null
--------------开始查询--------------------------
select
id ,
instance_id ,
task_id,
create_date=CONVERT(varchar(10),create_date,120)
from Tb A
where (create_date is not null )
or
(create_date is null and not exists(select 1 from Tb where create_date is not null and task_id=A.task_id))
----------------结果----------------------------
/*id instance_id task_id create_date
----------- ----------- ----------- -----------
309 499 800 2009-07-16
311 499 803 NULL
312 499 803 NULL(3 行受影响)
*/
declare @table table (id int,instance_id int,task_id int,create_date datetime)
insert into @table
select 309,499,800,'2009-07-16' union all
select 310,499,800,null union all
select 311,499,803,null union all
select 312,499,803,null
select isnull(b.id,a.id) as id,isnull(b.instance_id,a.instance_id) as instance_id,
isnull(b.task_id,a.task_id) as task_id,isnull(b.create_date,a.create_date) as create_date
from (select * from @table where create_date is null)a left join
(select * from @table where create_date is not null) b
on a.task_id=b.task_id/*
id instance_id task_id create_date
----------- ----------- ----------- -----------------------
309 499 800 2009-07-16 00:00:00.000
311 499 803 NULL
312 499 803 NULL
*/
declare @tb table (id int,instance_id int,task_id int,create_date datetime)
insert into @tb
select 309,499,800,'2009-07-16' union all
select 310,499,800,null union all
select 311,499,803,null union all
select 312,499,803,nullselect id,instance_id,task_id,create_date from @tb a
where isnull(create_date,'') in (select top 1 isnull(create_date,'') from @tb where task_id=a.task_id order by create_date desc )/*id instance_id task_id create_date
----------- ----------- ----------- ------------------------------------------------------
309 499 800 2009-07-16 00:00:00.000
311 499 803 NULL
312 499 803 NULL(所影响的行数为 3 行)
*/
drop table test
create table test
(
id bigint primary key,
instance_id int,
task_id int,
createdate datetime
)
insert into test values(309,499,800,getdate())
insert into test values(310,499,800,null)
insert into test values(311,499,803,null)
insert into test values(312,499,803,null)
select * from test as t
where
createdate is not null
or
createdate is null
and
not exists (select top 1 * from test where createdate is not null and task_id=t.task_id)
499 800 2009-07-17 09:08:42.560 311
499 803 NULL 312
499 803 NULL
(所影响的行数为 3 行)
create table [tb]([id] int,[instance_id] int,[task_id] int,[create_date] datetime)
insert [tb]
select 309,499,800,'2009-07-16' union all
select 310,499,800,null union all
select 311,499,803,null union all
select 312,499,803,nullselect * from Tb as A WHERE
NOT exists(select 1 from Tb where ISNULL(create_date,'')<> ISNULL(A.create_date,'') and task_id=A.task_id AND A.create_date IS NULL )id instance_id task_id create_date
----------- ----------- ----------- ------------------------------------------------------
309 499 800 2009-07-16 00:00:00.000
311 499 803 NULL
312 499 803 NULL(所影响的行数为 3 行)
feixianxxx:剔除不要的..
fredrickhu:拼凑符合条件的..
sdhdy:选取需要的..NB..值得学习的好贴..顶上..