12月4号应该不会查出0的那条数据吧?----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-22 07:51:42
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([日期] datetime,[类型] int,[属性] nvarchar(4))
insert [huang]
select '2013-12-01 01:00:00.000',0,N'上午' union all
select '2013-12-02 12:00:00.000',1,N'上午' union all
select '2013-12-02 03:00:00.000',0,N'上午' union all
select '2013-12-04 12:00:00.000',0,N'上午' union all
select '2013-12-04 11:00:00.000',1,N'上午' union all
select '2013-12-04 15:00:00.000',0,N'下午' union all
select '2013-12-04 07:00:00.000',0,N'上午' union all
select '2013-12-04 11:00:00.000',1,N'上午' union all
select '2013-12-04 09:00:00.000',0,N'上午' union all
select '2013-12-04 06:00:00.000',0,N'上午' union all
select '2013-12-04 08:00:00.000',1,N'上午' union all
select '2013-12-04 10:00:00.000',0,N'上午' union all
select '2013-12-04 04:00:00.000',1,N'上午' union all
select '2013-12-04 03:00:00.000',0,N'上午' union all
select '2013-12-04 02:00:00.000',0,N'上午'
--------------生成数据--------------------------
--找类型为0
SELECT *
FROM huang WHERE convert(varchar(10),日期,121) IN (
SELECT convert(varchar(10),日期,121)
from [huang]
GROUP BY convert(varchar(10),日期,121)
HAVING COUNT(CASE WHEN [类型]=0 THEN 1 ELSE NULL END )=COUNT([类型]))
UNION ALL
SELECT DISTINCT *
FROM huang a
WHERE EXISTS (SELECT 1 FROM huang b WHERE convert(varchar(10),a.日期,121)=convert(varchar(10),b.日期,121) AND b.类型=1 )
AND a.类型=1 ----------------结果----------------------------
/*
日期 类型 属性
----------------------- ----------- ----
2013-12-01 01:00:00.000 0 上午
2013-12-02 12:00:00.000 1 上午
2013-12-04 04:00:00.000 1 上午
2013-12-04 08:00:00.000 1 上午
2013-12-04 11:00:00.000 1 上午
*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-22 07:51:42
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([日期] datetime,[类型] int,[属性] nvarchar(4))
insert [huang]
select '2013-12-01 01:00:00.000',0,N'上午' union all
select '2013-12-02 12:00:00.000',1,N'上午' union all
select '2013-12-02 03:00:00.000',0,N'上午' union all
select '2013-12-04 12:00:00.000',0,N'上午' union all
select '2013-12-04 11:00:00.000',1,N'上午' union all
select '2013-12-04 15:00:00.000',0,N'下午' union all
select '2013-12-04 07:00:00.000',0,N'上午' union all
select '2013-12-04 11:00:00.000',1,N'上午' union all
select '2013-12-04 09:00:00.000',0,N'上午' union all
select '2013-12-04 06:00:00.000',0,N'上午' union all
select '2013-12-04 08:00:00.000',1,N'上午' union all
select '2013-12-04 10:00:00.000',0,N'上午' union all
select '2013-12-04 04:00:00.000',1,N'上午' union all
select '2013-12-04 03:00:00.000',0,N'上午' union all
select '2013-12-04 02:00:00.000',0,N'上午'
--------------生成数据--------------------------
--找类型为0
SELECT *
FROM huang WHERE convert(varchar(10),日期,121) IN (
SELECT convert(varchar(10),日期,121)
from [huang]
GROUP BY convert(varchar(10),日期,121)
HAVING COUNT(CASE WHEN [类型]=0 THEN 1 ELSE NULL END )=COUNT([类型]))
UNION ALL
SELECT DISTINCT *
FROM huang a
WHERE EXISTS (SELECT 1 FROM huang b WHERE convert(varchar(10),a.日期,121)=convert(varchar(10),b.日期,121) AND b.类型=1 )
AND a.类型=1 ----------------结果----------------------------
/*
日期 类型 属性
----------------------- ----------- ----
2013-12-01 01:00:00.000 0 上午
2013-12-02 12:00:00.000 1 上午
2013-12-04 04:00:00.000 1 上午
2013-12-04 08:00:00.000 1 上午
2013-12-04 11:00:00.000 1 上午
*/
truncate table [huang]
insert into huang (列名写上)
select * from #t清空前先备份一下以免出现意外。出门上班,有事10点后再说