select * from wk_user where id not in (select user_id from wk_diary where wk_date=?)
解决方案 »
- 问个小白问题(delete top (10) * from tb order by)
- MS SQL 2008排序规则问题
- 在一个基于网络的联合查询视图中能否判断某台主机与网络通信是否正常
- 请教设计库的问题
- 2个字段,怎么提出这类数据
- MSSQL数据库里的信息在哪,像这个管理员帐号和密码,用户帐号和密码
- mdf文件被破坏,如何处理
- 我想问一下如何写一个存储过程可以插入数据阿??
- sql server 数据字典中有uer_ab_collumn,uer_eqquences吗,sqlplus只在 oracle中有吗,
- 急:记录数目对数据库查询的影响???
- 显示表的问题
- A simple store procedure
declare @thisday datatime
set @thisday='2003-2-13'select [id],[username],[name] from wk_user where id not in (select user_id from wk_diary where
convert(varchar(10),wk_date,121)=convert(varchar(10),@thisday,121) )
现在只是列出某一天没有记录日志的情况,能否使用一条语句查找:1、一个用户在哪天没有记录日志
2、多个用户呢谢谢!ps: 各位推荐本关于sql的好书
convert(char(8),wk_date,112) not in (select convert(char(8),b.wk_date,112) from
wk_user a inner join wk_diary b on a.id=b.user_id where a.id=?)
where not exists (select 1 from wk_diary where [user_id]=tem.id and convert(char(8),wk_date,112)=tem.wk_date)
,wk_date,112) wk_date from wk_diary )b where a.id='2' or rtrim(a.username)
------------------------------------------------^^^^^^^^^^^^^^^^^^^^^这里的条件可以选择多个用户!
='aa' )tem
where not exists (select 1 from wk_diary where [user_id]=tem.id and convert(char(8),wk_date,112)=tem.wk_date)
select distinct wk_date from wk_diary
where wk_date not in
(select wk_date from wk_diary where userid=id1)
多个用户用
where wk_user.user_id in (id1,id2,id3)
(SELECT DISTINCT CONVERT(CHAR(8), wk_d.wk_date, 112) AS wk_date FROM wk_diary wk_d) tmp
CROSS JOIN
(SELECT DISTINCT wk_e.wk_time AS wk_t FROM wk_diary wk_e) tmp1
CROSS JOIN
(SELECT wk_f.[id] AS u_id, wk_f.[name] AS u_name FROM wk_user wk_f) tmp2
LEFT OUTER JOIN
(SELECT CONVERT(CHAR(8),wk_g.wk_date,112) AS wk_date, wk_g.wk_time AS wk_time, wk_g.[user_id] AS u_id FROM wk_diary wk_g) tmp3
ON tmp.wk_date=tmp3.wk_date AND tmp1.wk_t=tmp3.wk_time AND tmp2.u_id=tmp3.u_id
WHERE tmp3.wk_time IS NULL
ORDER BY tmp.wk_date DESC,tmp1.wk_t
(SELECT DISTINCT CONVERT(CHAR(8), [wk_date], 112) AS [wk_date] FROM [wk_diary] [wk_d]) [tmp] -- wk_date
CROSS JOIN
(SELECT DISTINCT [wk_time] AS [wk_time] FROM [wk_diary] [wk_e]) [tmp1] -- wk_time
CROSS JOIN
(SELECT [id] AS [user_id], [grp_id], [dep_id], [name] FROM [wk_user] [wk_f]) [tmp2] -- user_id, [name]
LEFT OUTER JOIN
(SELECT CONVERT(CHAR(8), [wk_date], 112) AS [wk_date], [wk_time], [user_id] FROM [wk_diary] [wk_g]) [tmp3] -- all field
ON [tmp].[wk_date] = [tmp3].[wk_date] AND [tmp1].[wk_time] = [tmp3].[wk_time] AND [tmp2].[user_id] = [tmp3].[user_id]WHERE [tmp3].[wk_time] IS NULL
ORDER BY [tmp].[wk_date] DESC, [tmp1].[wk_time]我又整理了一下!