一个关联查询

[boytomato]

a 表
user_id,user_name,pass
1 aaa aaa
2 bbb dddd
3 ea ddd

b 表

user_id ,user_date,remark
2 2006-06-10 aaa
3 2006-06-11 aaa

做个关联查询获取
某一天
b 表没有,a 表有的数据

是某一天，

比如 2006-06-10 这一天
user_id,user_name,pass
1 aaa aaa
3 ea ddd

006-06-11 这一天

user_id,user_name,pass
1 aaa aaa
2 bbb dddd

[paoluo]

Select A.* From A
Left Join B On A.[user_id] = B.[user_id] And B.user_date = '2006-06-10'
Where B.[user_id] Is Null


Select A.* From A
Left Join B On A.[user_id] = B.[user_id] And B.user_date = '2006-06-11'
Where B.[user_id] Is Null

[paoluo]

Select A.* From A Where [user_id] Not In (Select [user_id] From B Where user_date = '2006-06-10')
Select A.* From A Where [user_id] Not In (Select [user_id] From B Where user_date = '2006-06-11')

[paoluo]

Select A.* From A Where Not Exists (Select [user_id] From B Where user_date = '2006-06-10' And [user_id] = A.[user_id])

Select A.* From A Where Not Exists (Select [user_id] From B Where user_date = '2006-06-11' And [user_id] = A.[user_id])

[paoluo]

--方法一：
Select A.* From A
Left Join B On A.[user_id] = B.[user_id] And B.user_date = '2006-06-10'
Where B.[user_id] Is Null

Select A.* From A
Left Join B On A.[user_id] = B.[user_id] And B.user_date = '2006-06-11'
Where B.[user_id] Is Null


--方法二：
Select A.* From A Where [user_id] Not In (Select [user_id] From B Where user_date = '2006-06-10')

Select A.* From A Where [user_id] Not In (Select [user_id] From B Where user_date = '2006-06-11')

--方法三：
Select A.* From A Where Not Exists (Select [user_id] From B Where user_date = '2006-06-10' And [user_id] = A.[user_id])

Select A.* From A Where Not Exists (Select [user_id] From B Where user_date = '2006-06-11' And [user_id] = A.[user_id])

[paoluo]

Create Table A
(user_id Int,
user_name Varchar(10),
pass Varchar(10))
Insert A Select 1, 'aaa', 'aaa'
Union All Select 2, 'bbb', 'dddd'
Union All Select 3, 'ea', 'ddd'

Create Table B
(user_id Int,
user_date Varchar(10),
remark Varchar(10))
Insert B Select 2, '2006-06-10', 'aaa'
Union All Select 3, '2006-06-11', 'aaa'
GO
--方法一：
Select A.* From A
Left Join B On A.[user_id] = B.[user_id] And B.user_date = '2006-06-10'
Where B.[user_id] Is Null

Select A.* From A
Left Join B On A.[user_id] = B.[user_id] And B.user_date = '2006-06-11'
Where B.[user_id] Is Null

--方法二：
Select A.* From A Where [user_id] Not In (Select [user_id] From B Where user_date = '2006-06-10')

Select A.* From A Where [user_id] Not In (Select [user_id] From B Where user_date = '2006-06-11')

--方法三：
Select A.* From A Where Not Exists (Select [user_id] From B Where user_date = '2006-06-10' And [user_id] = A.[user_id])

Select A.* From A Where Not Exists (Select [user_id] From B Where user_date = '2006-06-11' And [user_id] = A.[user_id])
GO
Drop Table A, B
--Result
/*
user_id user_name pass
1 aaa aaa
3 ea ddd

user_id user_name pass
1 aaa aaa
2 bbb dddd
*/

[boytomato]

if exists (select * from sysobjects where id = OBJECT_ID('[Flow_Info_List]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [Flow_Info_List]

CREATE TABLE [Flow_Info_List] (
[Flow_Id] [int] IDENTITY (1, 1) NOT NULL,
[Flow_Time] [datetime] NULL,
[Flow_Name] [nvarchar] (100) NULL,
[Flow_Bc_Id] [int] NULL,
[Flow_Dz_id] [int] NULL,
[Flow_X] [nvarchar] (100) NULL,
[Flow_Out_Time] [datetime] NULL,
[Flow_In_Out_Flag] [int] NULL DEFAULT (0),
[Flow_In_Time] [datetime] NULL)

ALTER TABLE [Flow_Info_List] WITH NOCHECK ADD CONSTRAINT [PK_Flow_Info_List] PRIMARY KEY NONCLUSTERED ( [Flow_Id] )SET IDENTITY_INSERT [Flow_Info_List] ON

INSERT [Flow_Info_List] ([Flow_Id],[Flow_Time],[Flow_Name],[Flow_Bc_Id],[Flow_Dz_id],[Flow_Out_Time],[Flow_In_Out_Flag]) VALUES ( 5,'2007-6-10 0:03:00','1',1,1,'2007-6-10 0:00:00',1)
INSERT [Flow_Info_List] ([Flow_Id],[Flow_Time],[Flow_Name],[Flow_Bc_Id],[Flow_Dz_id],[Flow_Out_Time],[Flow_In_Out_Flag]) VALUES ( 33,'2007-6-10 0:03:00','1',2,1,'2007-6-10 0:00:00',0)
INSERT [Flow_Info_List] ([Flow_Id],[Flow_Time],[Flow_Name],[Flow_Bc_Id],[Flow_Dz_id],[Flow_In_Out_Flag]) VALUES ( 34,'2007-6-10 0:03:00','5',1,1,0)

SET IDENTITY_INSERT [Flow_Info_List] OFF

if exists (select * from sysobjects where id = OBJECT_ID('[KqUserInfo]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [KqUserInfo]

CREATE TABLE [KqUserInfo] (
[User_ID] [int] IDENTITY (1, 1) NOT NULL,
[User_Bh] [nvarchar] (100) NULL,
[User_Name] [nvarchar] (20) NULL,
[User_Dz_id] [int] NULL,
[User_X] [nvarchar] (100) NULL)

ALTER TABLE [KqUserInfo] WITH NOCHECK ADD CONSTRAINT [PK_KqUserInfo] PRIMARY KEY NONCLUSTERED ( [User_ID] )SET IDENTITY_INSERT [KqUserInfo] ON

INSERT [KqUserInfo] ([User_ID],[User_Name],[User_Dz_id],[User_X]) VALUES ( 1,'张三',1,'张')
INSERT [KqUserInfo] ([User_ID],[User_Name],[User_Dz_id],[User_X]) VALUES ( 2,'张了',1,'张')
INSERT [KqUserInfo] ([User_ID],[User_Name],[User_Dz_id],[User_X]) VALUES ( 3,'包在',1,'包')
INSERT [KqUserInfo] ([User_ID],[User_Name],[User_Dz_id],[User_X]) VALUES ( 4,'令',1,'令')
INSERT [KqUserInfo] ([User_ID],[User_Name],[User_Dz_id],[User_X]) VALUES ( 5,'张了啊',1,'张')
INSERT [KqUserInfo] ([User_ID],[User_Name],[User_Dz_id],[User_X]) VALUES ( 6,'张李四',1,'张')

SET IDENTITY_INSERT [KqUserInfo] OFF




select * from kqUserInfo a Left Join flow_info_List b
on a.[user_id]=b.[flow_name] and convert(nvarchar(10),b.flow_time,126)='2006-06-10'
where b.flow_name is null


本来很简单的个东西到我这变得怪怪的。。
兄弟帮忙看看。。

[paoluo]

select * from kqUserInfo a Left Join flow_info_List b
on a.[user_id]=b.[flow_name] and convert(nvarchar(10),b.flow_time,126)='2006-06-10'
where b.flow_name is null
--------
這麼修改即可


select A.* from kqUserInfo a Left Join flow_info_List b
on a.[user_id]=b.[flow_name] and convert(nvarchar(10),b.flow_time,126)='2007-06-10'
where b.flow_name is null

[boytomato]

太谢谢楼上哥们了，，做天晚上加班犯迷糊了。。。。

[wgzaaa]

是'2007-06-10'吧，语法没问题

[paoluo]

恩，就是'2006-06-10'改為'2007-06-10'，然後將*改為A.*

[symbol441]

学习了...