num id iddata
1 1 2008-1-10
2 2 2009-1-10
3 1 2009-3-15
4 2 2009-2-20
5 3 2009-10-1
6 1 2009-7-16 请教如何选出iddata只是2009年内的ID。
也就是只要ID为2、3的在线给分
1 1 2008-1-10
2 2 2009-1-10
3 1 2009-3-15
4 2 2009-2-20
5 3 2009-10-1
6 1 2009-7-16 请教如何选出iddata只是2009年内的ID。
也就是只要ID为2、3的在线给分
where not exists(select * from tb
where id = a.id
and iddata > '2009-12-31'
and iddata <= '2009-01-01')
from tb t
where not exists(select 1 from tb where id=t.id and year(iddata)!=2009)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([num] int,[id] int,[iddata] datetime)
insert [tb]
select 1,1,'2008-1-10' union all
select 2,2,'2009-1-10' union all
select 3,1,'2009-3-15' union all
select 4,2,'2009-2-20' union all
select 5,3,'2009-10-1' union all
select 6,1,'2009-7-16'
---查询---
select *
from tb t
where not exists(select 1 from tb where id=t.id and year(iddata)!=2009)---结果---
num id iddata
----------- ----------- ------------------------------------------------------
2 2 2009-01-10 00:00:00.000
4 2 2009-02-20 00:00:00.000
5 3 2009-10-01 00:00:00.000(所影响的行数为 3 行)
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-12-02 18:05:24
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (num INT,id INT,iddata DATETIME)
INSERT INTO @tb
SELECT 1,1,'2008-1-10' UNION ALL
SELECT 2,2,'2009-1-10' UNION ALL
SELECT 3,1,'2009-3-15' UNION ALL
SELECT 4,2,'2009-2-20' UNION ALL
SELECT 5,3,'2009-10-1' UNION ALL
SELECT 6,1,'2009-7-16'--SQL查询如下:select * from @tb as a
where not exists(select * from @tb
where id = a.id
and (iddata > '2009-12-31'
or iddata <= '2009-01-01'))/*
num id iddata
----------- ----------- -----------------------
2 2 2009-01-10 00:00:00.000
4 2 2009-02-20 00:00:00.000
5 3 2009-10-01 00:00:00.000(3 行受影响)*/
---查询---
select distinct ID
from tb t
where not exists(select 1 from tb where id=t.id and year(iddata)!=2009)/**
ID
-----------
2
3(所影响的行数为 2 行)
**/
drop table [tb]
go
create table [tb]([num] int,[id] int,[iddata] datetime)
insert [tb]
select 1,1,'2008-1-10' union all
select 2,2,'2009-1-10' union all
select 3,1,'2009-3-15' union all
select 4,2,'2009-2-20' union all
select 5,3,'2009-10-1' union all
select 6,1,'2009-7-16'
select distinct ID
from tb t
where not exists(select * from tb where id=t.id and datepart(year,iddata)!=2009)
/*
ID
-----------
2
3*/
如何把你的代码并入我的代码里。
我的代码
sql1="select count(a1) as a1 from main id in (select distinct mainid.id from main,mainid where "&dse2&")"&wmu
set rs1=conn.execute(sql)
t3=rs1("a1")
能帮忙改一下嘛。
以上代码des2为时间条件
declare @tb table (num int,id int,iddata datetime)
insert into @tb
select 1,1,'2008-1-10' union all
select 2,2,'2009-1-10' union all
select 3,1,'2009-3-15' union all
select 4,2,'2009-2-20' union all
select 5,3,'2009-10-1' union all
select 6,1,'2009-7-16'select distinct id
from @tb
where id not in
(select id from @tb where year(iddata)!=2009)id
-----------
2
3(2 行受影响)
参考一下这个贴子的提问方式http://forum.csdn.net/BList/OtherDatabase
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试。
create table T1 (num int, id int, iddata datetime);
insert into T1
select 1, 1, '2008-1-10' union all
select 2, 2, '2009-1-10' union all
select 3, 1, '2009-3-15' union all
select 4, 2, '2009-2-20' union all
select 5, 3, '2009-10-1' union all
select 6, 1, '2009-7-16'
--查询select t1.id from T1 as t1 group by t1.id having (sum(year(iddata))/2009) = count(iddata)/*
结果:
id
-----------
2
3(2 行受影响)*/
insert [tb]
select 1,1,'2008-1-10' union all
select 2,2,'2009-1-10' union all
select 3,1,'2009-3-15' union all
select 4,2,'2009-2-20' union all
select 5,3,'2009-10-1' union all
select 6,1,'2009-7-16'select * from tb where id not in (select distinct id from tb where datepart(yy,iddata) <> 2009)
/*
num id iddata
----------- ----------- ------------------------------------------------------
2 2 2009-01-10 00:00:00.000
4 2 2009-02-20 00:00:00.000
5 3 2009-10-01 00:00:00.000(所影响的行数为 3 行)
*/select distinct id from tb where id not in (select distinct id from tb where datepart(yy,iddata) <> 2009)
/*
id
-----------
2
3(所影响的行数为 2 行)
*/drop table tb
*
from
tb t
where
not exists(select * from where id = t.id and datepart(yy,iddata)<> 2009)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-02 23:01:27
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([num] int,[id] int,[iddata] datetime)
insert [tb]
select 1,1,'2008-1-10' union all
select 2,2,'2009-1-10' union all
select 3,1,'2009-3-15' union all
select 4,2,'2009-2-20' union all
select 5,3,'2009-10-1' union all
select 6,1,'2009-7-16'
--------------开始查询--------------------------
select
*
from
tb t
where
not exists(select 1 from tb where id = t.id and datepart(yy,iddata)<> 2009)
----------------结果----------------------------
/*num id iddata
----------- ----------- -----------------------
2 2 2009-01-10 00:00:00.000
4 2 2009-02-20 00:00:00.000
5 3 2009-10-01 00:00:00.000(3 行受影响)
*/
select id from tb group by id having max(year(iddata)) = min(year(iddata)) and max(year(iddata))=2009
select id from tb group by id having count(nullif(2009,year(iddata))=0
from tb t
where exists
(select * from tb where id=t.id and YEAR(iddata)=2009)