如果你的数据中不存在buy=0的记录的话,可以这样 select [name] from 表A where [name] not in (select [name] from 表A where [time] Between '2008-01-05' and'2008-03-06' )
表A id name buy time 1 x1 10 2008-01-01 2 x2 20 2008-01-02 3 x3 10 2008-01-03 4 x4 10 2008-01-04 5 x5 10 2008-01-05 6 x4 10 2008-01-05 7 x1 10 2008-01-06 我想查询2008-01-05 到2008-01-06之间的购买量(buy)为0的用户 结果应该为x2,x3 (Select RTRIM(count(id)) as 购气次数 From A where time Between '2008-01-05' and'2008-03-06' and buy=0 这样好象不行,问题似乎出在这里and buy=0,但不知道怎么表示这段时间没有购买的用户 select name from tb where time between '2008-01-05' and'2008-03-06' group by name having sum(buy) = 0
create table tb(id int, name varchar(10), buy int, time datetime) insert into tb values(1, 'x1', 10 , '2008-01-01') insert into tb values(2, 'x2', 20 , '2008-01-02') insert into tb values(3, 'x3', 10 , '2008-01-03') insert into tb values(4, 'x4', 10 , '2008-01-04') insert into tb values(5, 'x5', 10 , '2008-01-05') insert into tb values(6, 'x4', 10 , '2008-01-05') insert into tb values(7, 'x1', 10 , '2008-01-06') insert into tb values(8, 'x6', 0 , '2008-01-05') --我增加的测试数据 insert into tb values(9, 'x6', 0 , '2008-01-06') --我增加的测试数据 goselect name from tb where time between '2008-01-05' and'2008-03-06' group by name having sum(buy) = 0drop table tb/* name ---------- x6(所影响的行数为 1 行) */
--明白了,你是查所有用户在这段时间内没买东西的用户. create table tb(id int, name varchar(10), buy int, time datetime) insert into tb values(1, 'x1', 10 , '2008-01-01') insert into tb values(2, 'x2', 20 , '2008-01-02') insert into tb values(3, 'x3', 10 , '2008-01-03') insert into tb values(4, 'x4', 10 , '2008-01-04') insert into tb values(5, 'x5', 10 , '2008-01-05') insert into tb values(6, 'x4', 10 , '2008-01-05') insert into tb values(7, 'x1', 10 , '2008-01-06') goselect distinct name from tb where name not in ( select name from tb where time between '2008-01-05' and'2008-03-06' group by name having sum(buy) <> 0 ) drop table tb/* name ---------- x2 x3(所影响的行数为 2 行) */
select [name] from 表A
where [name] not in
(select [name] from 表A where [time] Between '2008-01-05' and'2008-03-06' )
id name buy time
1 x1 10 2008-01-01
2 x2 20 2008-01-02
3 x3 10 2008-01-03
4 x4 10 2008-01-04
5 x5 10 2008-01-05
6 x4 10 2008-01-05
7 x1 10 2008-01-06
我想查询2008-01-05 到2008-01-06之间的购买量(buy)为0的用户
结果应该为x2,x3
(Select RTRIM(count(id)) as 购气次数 From A
where time Between '2008-01-05' and'2008-03-06' and buy=0 这样好象不行,问题似乎出在这里and buy=0,但不知道怎么表示这段时间没有购买的用户 select name from tb where time between '2008-01-05' and'2008-03-06'
group by name having sum(buy) = 0
insert into tb values(1, 'x1', 10 , '2008-01-01')
insert into tb values(2, 'x2', 20 , '2008-01-02')
insert into tb values(3, 'x3', 10 , '2008-01-03')
insert into tb values(4, 'x4', 10 , '2008-01-04')
insert into tb values(5, 'x5', 10 , '2008-01-05')
insert into tb values(6, 'x4', 10 , '2008-01-05')
insert into tb values(7, 'x1', 10 , '2008-01-06')
insert into tb values(8, 'x6', 0 , '2008-01-05') --我增加的测试数据
insert into tb values(9, 'x6', 0 , '2008-01-06') --我增加的测试数据
goselect name from tb where time between '2008-01-05' and'2008-03-06'
group by name having sum(buy) = 0drop table tb/*
name
----------
x6(所影响的行数为 1 行)
*/
create table tb(id int, name varchar(10), buy int, time datetime)
insert into tb values(1, 'x1', 10 , '2008-01-01')
insert into tb values(2, 'x2', 20 , '2008-01-02')
insert into tb values(3, 'x3', 10 , '2008-01-03')
insert into tb values(4, 'x4', 10 , '2008-01-04')
insert into tb values(5, 'x5', 10 , '2008-01-05')
insert into tb values(6, 'x4', 10 , '2008-01-05')
insert into tb values(7, 'x1', 10 , '2008-01-06')
goselect distinct name from tb where name not in
(
select name from tb where time between '2008-01-05' and'2008-03-06' group by name having sum(buy) <> 0
) drop table tb/*
name
----------
x2
x3(所影响的行数为 2 行)
*/