如何优化或删除以下查询中的冗余
我有 4 个表
Table1 (employee)
id name
--------------------
1 a
2 b
Table2 (appointment)
id table1id table3id table4id sdate edate typeid
-----------------------------------------------------------------------------------
1 1 1 1 1/1/09 NULL 100
2 2 2 1 1/1/09 NULL 101
Table3 (title)
id name
---------------
1 worker1
2 worker2
3 Assistant
4 Manager
Table4 (Department names)
id name
-------------------
1 Logistics
2 ABC
3 XYZ
Type
id name
----------------
100 w (primary)
101 e (secondary)
102 r (other-primary)
103 t (.....)
104 y (....)
为了避免重复,我正在编写查询,因为
Select id, name, title, dept
FROM table1 a
INNER JOIN table2 b ON a.id = b.table1id
INNER JOIN table3 c ON b.table3id = c.id
INNER JOIN table4 d ON d.id = b.table4id
WHERE typeid =
(
SELECT min(type_id) /* i want primary type appointments */
FROM table2
WHERE sdate < getdate() and (edate > getdate() or edate IS NULL)
AND sdate = (select max(sdate) from table2 where table1id = a.id)
AND typeid in (100, 102)
)
AND b.sdate < getdate() and (b.edate > getdate() or b.edate IS NULL)
AND b.sdate = (select max(sdate) from table2 where table1id = a.id)
/* last two i have to repeat again to remove dupes */
有没有一种方法可以减少使用相同条件两次并仅指定一次或任何其他更好的方法来查询它? AND 输入 (100, 102)
I have 4 tables
Table1 (employee)
id name
--------------------
1 a
2 b
Table2 (appointment)
id table1id table3id table4id sdate edate typeid
-----------------------------------------------------------------------------------
1 1 1 1 1/1/09 NULL 100
2 2 2 1 1/1/09 NULL 101
Table3 (title)
id name
---------------
1 worker1
2 worker2
3 Assistant
4 Manager
Table4 (Department names)
id name
-------------------
1 Logistics
2 ABC
3 XYZ
Type
id name
----------------
100 w (primary)
101 e (secondary)
102 r (other-primary)
103 t (.....)
104 y (....)
To avoid dups I'm writing query as
Select id, name, title, dept
FROM table1 a
INNER JOIN table2 b ON a.id = b.table1id
INNER JOIN table3 c ON b.table3id = c.id
INNER JOIN table4 d ON d.id = b.table4id
WHERE typeid =
(
SELECT min(type_id) /* i want primary type appointments */
FROM table2
WHERE sdate < getdate() and (edate > getdate() or edate IS NULL)
AND sdate = (select max(sdate) from table2 where table1id = a.id)
AND typeid in (100, 102)
)
AND b.sdate < getdate() and (b.edate > getdate() or b.edate IS NULL)
AND b.sdate = (select max(sdate) from table2 where table1id = a.id)
/* last two i have to repeat again to remove dupes */
Is there a way i can reduce using same condition twice and query it specifying only once or any other better way?
AND typeid in (100, 102)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
在连接中使用子查询来获取所需的数字,这样的事情会是一种改进吗?
您也可以尝试查看 GROUP BY 的 HAVING 子句。我认为你可以做类似的事情:
但是上面的代码可能会拉出整个列表的最小值和最大值,而不是每个a.id。我忘记是否可以使用 max 中的分区来指定 max 的范围,或者我是否正在考虑 Oracle。如果没有,您始终可以使用子查询来获取每个 a.id 的最佳条目。
Would something like this be an improvement, using a sub-query in the join to get the numbers you want?
You might also try looking into the HAVING clause of GROUP BY. I think you would be able to do something like:
However the above might pull the min and max for the entire list, not for each a.id. I forget if you can use a partition in the max to specify what to max over, or if I am thinking of Oracle there. If not, you could always use a sub-query that gets the best entry for each a.id.
我使用了相同的查询,它运行得很快,我没有找到任何其他方法来优化它
I used same query and it is working fast, I didn't find any other way to optimize it