一个关联查询

boytomato 2007-06-11 09:12:32
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
...全文
170 11 打赏 收藏 举报
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
symbol441 2007-06-11
学习了...
  • 打赏
  • 举报
回复
paoluo 2007-06-11
恩,就是'2006-06-10'改為'2007-06-10',然後將*改為A.*
  • 打赏
  • 举报
回复
wgzaaa 2007-06-11
是'2007-06-10'吧,语法没问题
  • 打赏
  • 举报
回复
boytomato 2007-06-11
太谢谢楼上哥们了,,做天晚上加班犯迷糊了。。。。
  • 打赏
  • 举报
回复
paoluo 2007-06-11
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 2007-06-11
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 2007-06-11
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
*/
  • 打赏
  • 举报
回复
paoluo 2007-06-11
--方法一:
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 2007-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 2007-06-11
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 2007-06-11


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
  • 打赏
  • 举报
回复
相关推荐
最新单片机仿真 独立式键盘调时的数码时钟实验 最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的数码时钟实验最新单片机仿真 独立式键盘调时的
发帖
MS-SQL Server
加入

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2007-06-11 09:12
社区公告
暂无公告