一个关联查询

2007-06-11 09:12:32 229 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

微信小程序例子——快速掌握滚动视图（scroll-view）快速掌握微信小程序滚动条（scroll-view）阅读详情

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

...全文

移动跨平台框架ReactNative滚动视图ScrollView【17】，学前端程序开发 =就答题情况而言，第一问100%都可以回答正确，第二问大概只有50%正确率，第三问能回答正确的就不多了，第四问再正确就非常非常少了。其实此题并没有太多刁钻匪夷所思的用法，都是一些可能会遇到的场景，而大多数人但凡有1年到2年的工作经验都应该完全正确才对。只能说有一些人太急躁太轻视了，希望大家通过此文了解js一些特性。==就答题情况而言，第一问100%都可以回答正确，第二问大概只有50%正确率，第三问能回答正确的就不多了，第四问再正确就非常非常少了。阅读详情

boytomato

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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

一个关联查询

相关推荐