如果找到匹配项，则中止随后的UNION ALL命令[H2] [英] Abort subsequent UNION ALL commands if match found [H2]

问题描述

我该如何调整下面的查询，以便它不会执行不必​​要的UNION ALL 除非上面的SELECT语句找不到匹配项?

How do I adapt the query below so that it doesn't perform unnecessary UNION ALL unless the SELECT statement above it doesn't find a match?

SELECT LATITUDE, LONGITUDE FROM coordinates WHERE address = ? AND community = ?
UNION ALL
SELECT LATITUDE, LONGITUDE FROM coordinates WHERE address::text = ? AND community::text LIKE ? 
UNION ALL
SELECT LATITUDE, LONGITUDE FROM coordinates WHERE address::text LIKE ? AND community::text LIKE ?

我的coordinates表具有列| ID |地址|城市|纬度|经度

My coordinates table has columns | ID | ADDRESS | CITY | LATITUDE | LONGITUDE

我试图以最有效的方式从纬度和经度中提取值，同时也首先执行最强的查询，这意味着那些返回最可靠结果的SELECT语句.如果找到结果，则将其返回并丢弃其余查询.

I'm trying to extract values from latitude and longitude in the most efficient manner, while also performing the strongest queries first, meaning those SELECT statements that will return the most reliable result. If a result is found, return it and discard the remaining queries.

现在，我将所有查询保存在ArrayList中，然后分别循环并执行.如果找到match()，则返回resultset.列表顶部的查询是更严格的查询，使用=而不是LIKE，如果没有找到结果，查询将逐渐变得不太严格.

Right now I have all of my queries in an ArrayList which I then loop and execute individually. If a match() is found then I return the resultset. The queries at the top of the list are those that are stricter, using = instead of LIKE, getting gradually less strict if no results are found.

这显然是低效率的，因为我要分别执行近100个SELECT查询，因此我考虑使用UNION ALL，我的计划是在每个SELECT语句之间使用UNION ALL创建一个巨型查询.但是，我现在很困惑，如果找到匹配项，该如何中止任何后续查询?

This is obviously inefficient since I'm performing nearly 100 SELECT queries separately, so I've looked into using UNION ALL and my plan is to create one giant query with UNION ALL in between each SELECT statement. However I'm now confused how to abort any subsequent queries if a match is found?

当前方法:

            queryList.add("SELECT LATITUDE, LONGITUDE FROM coordinates WHERE address = ? AND community = ?");
            queryList.add("SELECT LATITUDE, LONGITUDE FROM coordinates WHERE address::text = ? AND community::text LIKE ?");
            queryList.add("SELECT LATITUDE, LONGITUDE FROM coordinates WHERE address::text LIKE ? AND community::text LIKE ?")        

            PreparedStatement pStatement = null;
            ResultSet rs = null;
            Connection conn = null;

            for (String currentQuery: queryList) {
                Connection conn = getConnection();
                pStatement = conn.prepareStatement(currentQuery);
                pStatement.setString(1, "742 Evergreen Terrace");
                pStatement.setString(2, "Springfield");
                // execute query
                // if match, break loop and use resultset
            } 

那么对于UNION ALL，如果第一个SELECT找到匹配项，我该如何终止?

So with UNION ALL, how do I abort if the first SELECT finds a match?

我正在尝试查询，但是它返回所有纬度和经度，而不仅仅是返回导致找到匹配项的纬度和经度.我该如何更改以仅返回相关行?

I'm trying this query, but it returns ALL latitutudes and longitudes instead of just the ones which caused the match to be found. How do I alter this to only return the relevant row?

SELECT LATITUDE, LONGITUDE FROM coordinates
WHERE EXISTS(SELECT LATITUDE, LONGITUDE FROM coordinates WHERE 
(address = ? AND community = ?) OR WHERE (address::text = ? AND community::text LIKE ?))

推荐答案

其他方法:

SELECT LATITUDE, LONGITUDE FROM coordinates 
WHERE (address = ? AND community = ?)
or (text = ? AND community::text LIKE ? )
or (text LIKE ? AND community::text LIKE ?) 

相同的净结果.速度差异取决于您正在使用的平台.但这可能比以前更快.

Same net result. Speed differences vary depending on what platform you are working on. But this is likely faster than the previous.

这篇关于如果找到匹配项，则中止随后的UNION ALL命令[H2]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！