2表连接,超出我能力了,各位亲帮个忙

qian21h 2013-07-29 10:13:35
现有2个表
SELECT     PNumber, TMoney, GNumber, AddDate,LoginName
FROM dbo.BusTour


SELECT PNumber, TMoney, GNumber, AddDate,LoginName
FROM dbo.CountryTour

2个表中除了LoginName有可能一样,其他字段内容都不一样,比如现在2表中有以下数据:
BusTour表中一部分数据

PNumber TMoney GNumber AddDate LoginName
1 500 35353 2013-08-18 16:02:39.000 admin
1 666 12345 2013-07-20 22:02:15.000 admin
1 444 12345 2013-07-20 14:19:34.000 admin
1 120 1212121 2013-07-18 16:49:38.000 yewu1
1 33 1111111 2013-07-11 11:40:58.000 yewu1
1 445 12345 2013-07-19 17:00:19.000 admin
2 500 1 2013-07-11 14:14:19.000 admin
1 555 12345 2013-07-20 14:31:26.000 admin
1 666 12345 2013-07-20 14:31:48.000 admin
CountryTour表中一部分数据

PNumber TMoney GNumber AddDate LoginName
1 444 22444 2013-07-19 15:51:41.000 admin
2 222 22 2013-07-19 15:51:00.000 admin
34 3434 22444 2013-07-19 16:07:21.000 admin
1 500 201307 2013-07-26 09:01:44.000 yewu1
1 200 22 2013-07-19 16:31:42.000 admin

我现在想得到LoginName='yewu1'的2表中所有数据
PNumber TMoney GNumber AddDate LoginName
1 120 1212121 2013-07-18 16:49:38.000 yewu1
1 33 1111111 2013-07-11 11:40:58.000 yewu1
1 500 201307 2013-07-26 09:01:44.000 yewu1
是否是2表连接后创建新的临时表?求助各位大大
...全文
197 18 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
qian21h 2013-07-29
  • 打赏
  • 举报
回复
非常感谢,问题解决。。。。。 后面我又加了个表上去。。

select * from(SELECT PeopleNumber, TheMoney, BusTour.GroupNumber, AddDate,LoginName,GroupStyle  FROM dbo.BusTour JOIN dbo.GroupLine ON dbo.BusTour.GroupNumber = dbo.GroupLine.GroupNumber 
    union all SELECT PeopleNumber, TheMoney, CountryTour.GroupNumber, AddDate,LoginName,GroupStyle  FROM dbo.CountryTour JOIN dbo.GroupLine ON dbo.CountryTour.GroupNumber = dbo.GroupLine.GroupNumber) t 
    where LoginName='admin' and AddDate between '2013-07-18' and '2013-07-22'
Tosp2012 2013-07-29
  • 打赏
  • 举报
回复

SELECT * 
FROM
(
 SELECT     PNumber, TMoney, GNumber, AddDate,LoginName FROM dbo.BusTour
 UNION       
 SELECT     PNumber, TMoney, GNumber, AddDate,LoginName FROM  dbo.CountryTour
) A
WHERE A.LoginName='yewu1'
唐诗三百首 2013-07-29
  • 打赏
  • 举报
回复
引用 14 楼 qian21h 的回复:
chwnrthd ,首先表示感谢,我执行了下如果单单的以LoginName为条件,可以得到结果,如果我要加时间不是也要俩个表中都加时间条件。。?有没有更好的解决方案
try this,

select * from
(SELECT PNumber, TMoney, GNumber, AddDate,LoginName
 FROM dbo.BusTour
 union all
 SELECT PNumber, TMoney, GNumber, AddDate,LoginName
 FROM dbo.CountryTour) t
where LoginName='yewu1' and AddDate between [开始时间] and [结束时间]
唐诗三百首 2013-07-29
  • 打赏
  • 举报
回复

select * from
(SELECT PNumber, TMoney, GNumber, AddDate,LoginName
 FROM dbo.BusTour where LoginName='yewu1'
 union all
 SELECT PNumber, TMoney, GNumber, AddDate,LoginName
 FROM dbo.CountryTour where LoginName='yewu1') t
qian21h 2013-07-29
  • 打赏
  • 举报
回复
chwnrthd ,首先表示感谢,我执行了下如果单单的以LoginName为条件,可以得到结果,如果我要加时间不是也要俩个表中都加时间条件。。?有没有更好的解决方案
htl258_Tony 2013-07-29
  • 打赏
  • 举报
回复
用UNION ALL两表有重复的不会去重,用UNION会自动过滤掉重复的行
htl258_Tony 2013-07-29
  • 打赏
  • 举报
回复
引用 10 楼 chwnrthd 的回复:
版主就是好 可以编辑
我原来也是把2表看成表2
吖君君 2013-07-29
  • 打赏
  • 举报
回复
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: <BOB> -- Create date: <> -- Description: <从虚表导入实表 研究生资料> -- ============================================= ALTER PROCEDURE [dbo].[InsertFinishedPracticalTeachingWorkloads_FromVitual] @Creator nvarchar(50) AS begin declare @DelID int --需要在虚表中删除的数据ID值 declare @BackID int declare @PracticeCategory int declare @Definition nvarchar(50) declare @StartTime datetime declare @EndTime datetime declare @Professional nvarchar(100) declare @Number int declare @Coeffcient float declare @ScheduleClassHours float declare @StandardClassHours float declare @StaffID int declare @Remark nvarchar(1000)--以上参数是可以由外部传入的参数 declare @StaffWorkloadID int declare @WorkloadType int declare @State int declare @TableName nvarchar(80) declare @WorkTaskID int declare @StaffName nvarchar(100) declare @DepartmentID int set @WorkloadType =113 set @State=1 DECLARE @TranStarted bit SET @TranStarted = 0 IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END ELSE SET @TranStarted = 0 SET ANSI_WARNINGS OFF declare @AllIsRightData int --所有虚表数据是否正确值 declare @IsRightData int --确认导入时执行数据检查 set @AllIsRightData=1 declare CheckData cursor for select IsRight from FinishedPracticalTeachingWorkloads_Virtual where Creator=@Creator open CheckData fetch from CheckData into @IsRightData while(@@fetch_status=0) begin if(@IsRightData=0) BEGIN set @AllIsRightData=0 RAISERROR('还存在未修改完的错误数据,请检查后重新导入!',16,1) Break END fetch next from CheckData into @IsRightData end close CheckData deallocate CheckData declare @LoginStaffName nvarchar(20) select @LoginStaffName=Contact.Name from Contact inner join Staff ON Contact.ContactID=Staff.ContactID WHERE Staff.StaffID=@Creator if(@AllIsRightData=1) BEGIN select @WorkTaskID=ID from WorkTasks where ParentID is null and TaskStatus in (1,2) declare SureInsertAll cursor for select ID,PracticeCategory,Definition,StartTime,EndTime,Professional,Number,Coeffcient,ScheduleClassHours,StandardClassHours,StaffName,DepartmentID,StaffID from FinishedPracticalTeachingWorkloads_Virtual where Creator=@Creator set transaction isolation level serializable begin tran alter table StaffWorkLoads---------------------- disable trigger InsertSumTeachWorkloads---------- alter table FinishedPracticalTeachingWorkloads---------------------- disable trigger SumFinishedPracticalTeachingWorkloadsInsert---------- open SureInsertAll fetch from SureInsertAll into @DelID,@PracticeCategory,@Definition,@StartTime,@EndTime,@Professional,@Number,@Coeffcient,@ScheduleClassHours,@StandardClassHours,@StaffName,@DepartmentID,@StaffID while(@@fetch_status=0) BEGIN select @StaffID=StaffID from staff inner join contact on staff.contactid=contact.contactid where contact.name=@StaffName and contact.departmentid=@DepartmentID if exists( select ID from StaffWorkLoads where WorkTaskID=@WorkTaskID and WorkloadType=@WorkloadType and StaffID=@StaffID ) begin select @StaffWorkloadID=ID from StaffWorkLoads where WorkTaskID=@WorkTaskID and WorkloadType=@WorkloadType and StaffID=@StaffID end else begin insert into StaffWorkLoads(StaffID,WorkTaskID,WorkloadType,Workload,State,UsedFlag,Creator,CreateTime,Editor,EditTime,Remark) values (@StaffID,@WorkTaskID,@WorkloadType,0,1,1,@Creator,getdate(),@Creator,getdate(),null) set @StaffWorkloadID=IDENT_CURRENT('StaffWorkLoads') IF( @@ERROR <> 0 ) GOTO Cleanup end insert into FinishedPracticalTeachingWorkloads ( StaffWorkloadID,WorkloadType,PracticeCategory,Class,StartTime,EndTime,Professional,Number,Coeffcient,ScheduleClassHours,StandardClassHours,State,UsedFlag,Creator,CreateTime,Editor,EditTime, Remark,DataOrigin ) values ( @StaffWorkloadID,@WorkloadType,@PracticeCategory,@Definition,@StartTime,@EndTime,@Professional,@Number,@Coeffcient,@ScheduleClassHours,@StandardClassHours,2,1,@Creator,getdate(),@Creator,getdate(), null,1 ) set @BackID=IDENT_CURRENT('FinishedPracticalTeachingWorkloads') set @TableName='FinishedPracticalTeachingWorkloads' -- 更新工作流数据。 DECLARE @InstanceID uniqueidentifier SET @InstanceID = NEWID() INSERT INTO eIvy_WorkflowInstances (ID, WorkflowName, EntityID, CurrentState) VALUES (@InstanceID, @TableName, @BackID, 2) INSERT INTO eIvy_WorkflowInstanceTracings (TracingID, InstanceID, Transit, Executer, ExecuteTime, Remark) VALUES (NEWID(), @InstanceID, 11,@LoginStaffName, GETDATE(), NULL) IF( @@ERROR <> 0 ) GOTO Cleanup /*insert into WorkloadAssign (Entity,EntityID,StaffID,Workload,SelfRank,State,UsedFlag,Creator,CreateTime,Editor,EditTime,Remark) values (@TableName,@BackID,@StaffID,0,1,1,1,@Creator,getdate(),@Creator,getdate(),@Remark)*/ delete from FinishedPracticalTeachingWorkloads_Virtual where ID=@DelID IF( @@ERROR <> 0 ) GOTO Cleanup IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END fetch next from SureInsertAll into @DelID,@PracticeCategory,@Definition,@StartTime,@EndTime,@Professional,@Number,@Coeffcient,@ScheduleClassHours,@StandardClassHours,@StaffName,@DepartmentID,@StaffID END close SureInsertAll deallocate SureInsertAll alter table FinishedPracticalTeachingWorkloads----- enable trigger SumFinishedPracticalTeachingWorkloadsInsert----- alter table StaffWorkLoads-------------------- enable trigger InsertSumTeachWorkloads---------------- commit tran END Cleanup: IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END RETURN -1 end 这段代码为什么插入到虚表的时候是截断的插入的呀,不是连续插入的,导致不能一次性吧数据全部插入
  • 打赏
  • 举报
回复
版主就是好 可以编辑
搬砖的码农 2013-07-29
  • 打赏
  • 举报
回复
笨方法,不过这两个表的列名要全部相同才能用
select * from dbo.BusTour where LoginName='yewu1' union select * from dbo.CountryTour where LoginName='yewu1'
qian21h 2013-07-29
  • 打赏
  • 举报
回复
2个表的数据啊亲,我用左连,右连,全连貌似都不行啊
  • 打赏
  • 举报
回复
1楼 看错了 以为是表2中 原来是2表啊~ 4楼改正~
qian21h 2013-07-29
  • 打赏
  • 举报
回复
CountryTour表中一部分数据

PNumber TMoney GNumber   AddDate                LoginName  
1	444	22444	2013-07-19 15:51:41.000	admin
2	222	22	2013-07-19 15:51:00.000	admin
34	3434	22444	2013-07-19 16:07:21.000	admin
1	500	201307	2013-07-26 09:01:44.000	yewu1
1	200	22	2013-07-19 16:31:42.000	admin
我现在想得到LoginName='yewu1'的2表中所有数据

PNumber TMoney GNumber   AddDate                LoginName
1	120	1212121	2013-07-18 16:49:38.000	yewu1
1	33	1111111	2013-07-11 11:40:58.000	yewu1
1	500	201307	2013-07-26 09:01:44.000	yewu1

htl258_Tony 2013-07-29
  • 打赏
  • 举报
回复
SELECT PNumber, TMoney, GNumber, AddDate,LoginName
FROM dbo.BusTour
UNION
SELECT PNumber, TMoney, GNumber, AddDate,LoginName
FROM dbo.CountryTour
  • 打赏
  • 举报
回复

SELECT     PNumber, TMoney, GNumber, AddDate,LoginName 
FROM         dbo.BusTour    where LoginName='yewu1'
UNION ALL
SELECT     PNumber, TMoney, GNumber, AddDate,LoginName 
FROM         dbo.CountryTour  where LoginName='yewu1'
qian21h 2013-07-29
  • 打赏
  • 举报
回复
CountryTour表中一部分数据

PNumber TMoney GNumber   AddDate                LoginName 
1	444	22444	2013-07-19 15:51:41.000	admin
2	222	22	2013-07-19 15:51:00.000	admin
34	3434	22444	2013-07-19 16:07:21.000	admin
1	500	2013072502	2013-07-26 09:01:44.000	yewu1
1	200	22	2013-07-19 16:31:42.000	admin

qian21h 2013-07-29
  • 打赏
  • 举报
回复
BusTour表中一部分数据

PNumber TMoney GNumber   AddDate                LoginName
1	500	35353	2013-08-18 16:02:39.000	admin
1	666	12345	2013-07-20 22:02:15.000	admin
1	444	12345	2013-07-20 14:19:34.000	admin
1	120	1212121	2013-07-18 16:49:38.000	yewu1
1	33	1111111	2013-07-11 11:40:58.000	yewu1
1	445	12345	2013-07-19 17:00:19.000	admin
2	500	1	2013-07-11 14:14:19.000	admin
1	555	12345	2013-07-20 14:31:26.000	admin
1	666	12345	2013-07-20 14:31:48.000	admin
  • 打赏
  • 举报
回复
····· select * from dbo.CountryTour where LoginName='yewu1'

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧