如何查询出没有记录的数据
一个表为个人信息.如
[userlist]
USERID USERNAME
1 张三
2 李四
3 五五
另一个表为对应的ID购买物品的记录,
[RegisterList]
USERID BuyRegister BUYDATE
1 毛巾 2007-02-02
1 鞋 2007-02 02
现在我想查出某个时间范围内没有购买过物品的人.如何写查询语句?
(也就是查询出在[RegisterList]表中没有记录的人)
[解决办法]
create table userlist
(
userid int not null,
username varchar(50) not null
)
go
create table RegisterList
(
userid int not null,
buyregister varchar(50) not null,
buydate varchar(50) not null
)
go
insert into userlist values( '1 ', '张三 ')
insert into userlist values( '2 ', '李四 ')
insert into userlist values( '3 ', '五五 ')
insert into RegisterList values( '1 ', '毛巾 ', '2007-02-02 ')
insert into RegisterList values( '1 ', '鞋 ', '2007-02-02 ')
select * from userlist where userid not in (select u.userid from userlist u,RegisterList r where u.userid=r.userid and buydate= '2007-02-02 ')
/-----------------------------------------/结果
userid username
2李四
3五五
结帖
[解决办法]
抱歉,更改一下方法2:
declare @userlist table(USERID int, USERNAME varchar(10))
insert @userlist
select 1, '张三 ' union all
select 2, '李四 ' union all
select 3, '五五 '
declare @RegisterList table(USERID int, BuyRegister varchar(10), BUYDATE datetime)
insert @RegisterList
select 1, '毛巾 ', '2007-02-02 ' union all
select 1, '鞋 ', '2007-02-02 '
----方法1:
select * from @userlist as a where not exists(select 1 from @RegisterList where USERID = a.USERID AND BUYDATE = '2007-02-02 ')
----方法2:
select a.* from @userlist as a
left join @RegisterList as b on a.USERID = b.USERID and b.BUYDATE = '2007-02-02 '
where b.USERID is null
/*结果
USERID USERNAME
----------- ----------
2 李四
3 五五
*/
