--@thisday is the input para declare @thisday datatime set @thisday='2003-2-13'select * 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) )
select * from wk_user a where not exists (select 1 from wk_diary where convert(char(8),b.wk_date,112)=convert(char(8),getdate(),112) and [user_id]=a.[id])
select * from wk_user a where not exists (select 1 from wk_diary where convert(char(8),b.wk_date,112)=convert(char(8),你的时间,112) and [user_id]=a.[id])
SELECT [tmp].[wk_date] AS [wk_date], [tmp1].[wk_time] AS [wk_time], [tmp2].[user_id] AS [user_id], [tmp2].[grp_id] AS [grp_id], [tmp2].[dep_id] AS [dep_id], [tmp2].[name] AS [name] FROM (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]我自己的答案
declare @thisday datatime
set @thisday='2003-2-13'select * 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的好书
(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]我自己的答案