连接多个表 [英] joining multiple tables

问题描述

有人可以帮忙吗?我正在加入四个表以获得所需的结果.参考下面的第一个脚本，我想获取费用摘要，它是table1的字段之一.SUM值应根据table4的adddate字段按月分组.我如何更改以下脚本以获得预期的结果.

希望任何人都能帮助我. :)

Hi,

Anybody can help? I am joining four tables to get the required result. Referring to the first script below, I want to get the summary of Charges which is one of the fields of table1.SUM value should be grouped by month according to the adddate field from table4. How can i change of below script to get the expected result.

Hope anyone can help me. :)

SELECT * FROM Table1 WHERE Table1.evidencenumber IN 
(SELECT DISTINCT table2.evidencenumber FROM table3 rs, table2
WHERE table3.rep_id IN (SELECT DISTINCT rep_id FROM table4 WHERE adddate>=TO_DATE('010109','ddmmyy') 
AND adddate<=TO_DATE('311209','ddmmyy') AND dept='Engineering')
AND table3.evidencenumber=table2.evidencenumber
AND table2.pareantid IS NULL)

推荐答案

如果我正确理解了目的，则只需要按Table1的值进行汇总，然后可以通过简单地将外部的所有子查询联接来过滤不同的结果"where"子句，并从相应的表中添加行的"rowid"标记(这在Oracle中是可能的)，从而在外部查询中计算结果:

If I correctly understand the purpose you need only summary by the values of Table1, then you can filter distinct results by simply joining all the subqueries in outer "where" clause and adding the "rowid" marker of the row from corresponding table (this is possible in Oracle), and thus calculating the result in the outer query:

select T.adddate, sum(T.Charges) from
(
SELECT distinct Table1.rowid, Table1.Charges, table4.adddate FROM
  Table1,
  table3,
  table2,
  table4
WHERE
 Table1.evidencenumber = table2.evidencenumber 
 AND table3.rep_id = table4.rep_id
 and table4.adddate>=TO_DATE('010109','ddmmyy') 
 AND table4.adddate<=TO_DATE('311209','ddmmyy')
 AND table4.dept='Engineering'
 AND table3.evidencenumber=table2.evidencenumber
 AND table2.pareantid IS NULL
) T 
group by T.adddate

即使您的SQL引擎没有内置的行标记(例如Oracle中的rowid)，您也可以向Table1添加一个唯一字段并将其用作行标记.

Still if your SQL engine doesnt have the built-in row marker (like rowid in Oracle) you can add a unique field to the Table1 and use it as a rowid-marker.

这篇关于连接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！