a 表
user_id,user_name,pass
1 aaa aaa
2 bbb dddd
3 ea dddb 表user_id ,user_date,re
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 ddd006-06-11 这一天user_id,user_name,pass
1 aaa aaa
2 bbb dddd
user_id,user_name,pass
1 aaa aaa
2 bbb dddd
3 ea dddb 表user_id ,user_date,re
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 ddd006-06-11 这一天user_id,user_name,pass
1 aaa aaa
2 bbb dddd
解决方案 »
- 数据类型"varchar"和"nvarchar"的区别?
- 请问sqlserver有没有类似的函数
- 动态行转列问题,请各大虾帮帮忙。
- 在SQL2000中的表,我设40个字段是否会对速度有比较明显的影响呢?
- sql server 和 access 中带短杠(-)的字符串排序异常
- 谁能帮我写出实现下面问题的SQL语句﹖﹖﹖100分送上﹗﹗﹗
- 问了半个月了,来者有分,关于数据库占用硬盘空间问题!!
- 这条SQL语句怎么写??
- 高分相送:请问这个存储过程该如何写?
- 树型数据库的设计--要在VB中进行调用,最理想的方法是那一种?
- 在SQL SERVER中授权语句是否一次只能给一个表(或数据对象)授权?(grant)
- 请教一句update语句?
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-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 NullSelect 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])
(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),
re 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 NullSelect 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 ddduser_id user_name pass
1 aaa aaa
2 bbb dddd
*/
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] ONINSERT [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] OFFif 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] ONINSERT [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
本来很简单的个东西到我这变得怪怪的。。
兄弟帮忙看看。。
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