连接表的问题

本文介绍了连接表的问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我在使用join从3个表中获取记录时遇到问题： 加入表有两个条件，[项目编号]和[当前月份] - 日期字段。 我指定了获得记录的月份和年份，但结果是实际记录的4倍。 任何帮助都将受到赞赏。 INSERT INTO patempTable SELECT pr。[项目编号]，pr。[员工编号]， pr。[当月]，pr。[净工资]，al。[净额]，pd。[net] FROM [pay_roll] pr，[allowance] al，[per_diem_accomodation] pd& _ WHERE（pr。[project number] ='& ; projcode& 'al al。[项目编号] ='& projcode& 'AND pd。[project number] ='& projcode& 'AND& _ MONTH（pr。[当月]）=& monthnumber& AND MONTH（al。[current month]）=& monthnumber& AND MONTH（pd。[当月]）=& monthnumber& AND & _ YEAR（pr。[current mont] h]）=&期间& AND YEAR（al。[current month]）=&期间& AND YEAR（pd。[当月]）=&期间& ） 解决方案 我在你提供的代码中看到了几个主要问题： 1）你说你试图从三张桌子上得到一张记录，但是你向我们展示的查询是一个INSERT子句，而不是一个SELECT子句。 2）你在谈论使用JOIN，但你实际上从未在查询中加入你的表（WHERE子句并不意味着要处理JOIN）。 3）你应该使用参数化查询而不是像你那样连接字符串值。 你应该先去那里： 加入基础知识 [ ^ ] 然后肯定在那里： 如何创建参数化SQL查询？我为什么要这样做？ [ ^ ] 祝你好运。希望这会有所帮助。 检查此查询： INSERT INTO patempTable （ [项目编号]， [员工编号]， [当前月]， [净工资]， [净额]， [[净] ） SELECT pr。[项目编号]，pr。[员工编号]，pr。[当前 month]，pr。[net pay]，al。[net allowance]，pd。[net] FROM [pay_roll] pr INNER JOIN [allowance] al ON pr。[项目编号] = al。[项目编号] AND MONTH（pr。[当前月]）= MONTH（al。[当前月]） AND 年（ p r。[当前月]）=年（al。[当前月]） INNER JOIN [per_diem_accomodation] pd ON pr。 [项目编号] = pd。[项目编号] 和 MONTH（pr。[当前月]）=月（pd。[当前月]） AND 年（pr。[当前月]）=年（pd。[当前月]） WHERE pr。[项目编号] = ' & ; projcode& ' AND MONTH（pr。[当前月份]）= &月份数和 AND 年份（pr。[当前月份] ）= &期间& GROUP BY pr。[项目编号]， pr。[员工编号]，pr。[当前月]，pr。[净工资]，al。[净额]，pd。[net] 我刚用括号括起了第一个连接语句，因为我正在使用访问数据库。 INSERT INTO [patempTable] SELECT pr。[项目编号]，pr。[员工编号]，pr。[当月]，pr。[净工资] ]，& _ al。[net allowance]，pda。[net ] FROM（[pay_roll] pr INNER JOIN [allowance] al ON pr。[项目编号] = al。[项目编号] AND& _ pr。[员工编号] = al。[员工编号]和月份（pr。[当月]）= MONTH（al。[当月]）和YEAR （公元[当月]） =年（al。[当月]））& _ INNER JOIN [per_diem_accomodation] pda ON pr。[项目编号] = pda。[项目编号]和& _ pr。[员工编号] = pda。[员工编号] AND MONTH（pr。[current月]）=月（pda。[当月]）和年（pr。[当月]）=年（pda。[当月]） I've a problem in getting a record from 3 tables using join:There are two conditions for joining the tables,[project number] and [current month]-a date field.I specify the month and year to get the records but it results in 4 times the actual record.Any help would be appreciated."INSERT INTO patempTable SELECT pr.[project number],pr.[employee number],pr.[current month],pr.[net pay],al.[net allowance],pd.[net] FROM [pay_roll] pr,[allowance] al,[per_diem_accomodation] pd" & _"WHERE (pr.[project number]='" & projcode & "' AND al.[project number]='" & projcode & "' AND pd.[project number]='" & projcode & "' AND " & _"MONTH(pr.[current month])=" & monthnumber & " AND MONTH(al.[current month])=" & monthnumber & " AND MONTH(pd.[current month])=" & monthnumber & " AND " & _"YEAR(pr.[current month])=" & period & " AND YEAR(al.[current month])=" & period & " AND YEAR(pd.[current month])=" & period & ")" 解决方案 I see several major problems in the code you provided:1) You say you're trying to get a record from three tables, but the query you show to us is an INSERT clause, not a SELECT one.2) You are talking about using JOIN, but you never actually join your tables in your query (WHERE clause is not meant to handle the JOIN).3) You should use a parameterized query instead of concatenating string values like you do.You should go there first:Join Fundamentals[^]And then surely there:How do I create a parameterized SQL query? Why Should I?[^]Good luck. Hope this helps.Check this query : INSERT INTO patempTable ([project number],[employee number],[current month],[net pay],[net allowance],[[net])SELECT pr.[project number],pr.[employee number],pr.[current month],pr.[net pay],al.[net allowance],pd.[net] FROM [pay_roll] pr INNER JOIN [allowance] al ON pr.[project number]=al.[project number] AND MONTH(pr.[current month])=MONTH(al.[current month]) AND YEAR(pr.[current month])=YEAR(al.[current month]) INNER JOIN [per_diem_accomodation] pd ON pr.[project number]=pd.[project number] AND MONTH(pr.[current month])=MONTH(pd.[current month]) AND YEAR(pr.[current month])=YEAR(pd.[current month]) WHERE pr.[project number]='" & projcode & "' AND MONTH(pr.[current month])=" & monthnumber & " AND YEAR(pr.[current month])=" & period & " GROUP BY pr.[project number],pr.[employee number],pr.[current month],pr.[net pay],al.[net allowance],pd.[net]I just enclosed the first join statement with a bracket,since I'm working with access database."INSERT INTO [patempTable] SELECT pr.[project number],pr.[employee number],pr.[current month],pr.[net pay]," & _ "al.[net allowance],pda.[net] FROM ([pay_roll] pr INNER JOIN [allowance] al ON pr.[project number]=al.[project number] AND " & _ "pr.[employee number]=al.[employee number] AND MONTH(pr.[current month])=MONTH(al.[current month]) AND YEAR(pr.[current month])=YEAR(al.[current month]))" & _ "INNER JOIN [per_diem_accomodation] pda ON pr.[project number]=pda.[project number] AND " & _ "pr.[employee number]=pda.[employee number] AND MONTH(pr.[current month])= MONTH(pda.[current month]) AND YEAR(pr.[current month])=YEAR(pda.[current month])" 这篇关于连接表的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！