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

2013-07-29 10:13:35 277 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

MiniMax M2.7：单次推理闭环的AI产线自组装能力解析大模型从‘能说会写’迈向‘自主执行’，核心在于是否具备内生式工具构建与环境闭环推理能力。这涉及Agent系统架构演进、长期记忆的知识图谱化建模，以及多智能体间的分布式认知协同。MiniMax M2.7通过将工具调用深度耦合进单次推理过程，实现无需外部框架即可动态生成、验证、执行工具链；其长期记忆不再依赖向量检索，而是构建带时间戳与置信度的可演化三元组图谱；多Agent协作亦突破角色分饰范式，形成具备状态隔离、仲裁机制与规则自生长能力的分布式认知系统。这些能力共同支撑办公自动化、SRE故障根因定位、跨模态内容阅读详情

现有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表连接后创建新的临时表？求助各位大大

...全文

STM32单片机入门学习（六）-光敏传感器控制LED 光敏传感模块一DO端接B13,GND接GND，VCC接VCC,AO不接。LED负极接B12,正极接VCC。阅读详情

qian21h

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

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 这段代码为什么插入到虚表的时候是截断的插入的呀，不是连续插入的，导致不能一次性吧数据全部插入

昵称90天可改一次 2013-07-29

打赏
举报

版主就是好可以编辑

搬砖的码农 2013-07-29

打赏
举报

笨方法，不过这两个表的列名要全部相同才能用

select * from dbo.BusTour where LoginName='yewu1' union select * from dbo.CountryTour where LoginName='yewu1'

qian21h 2013-07-29

打赏
举报

2个表的数据啊亲，我用左连，右连，全连貌似都不行啊

昵称90天可改一次 2013-07-29

打赏
举报

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

昵称90天可改一次 2013-07-29

打赏
举报


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

昵称90天可改一次 2013-07-29

打赏
举报

····· select * from dbo.CountryTour where LoginName='yewu1'

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

相关推荐