使用我的SQL查询帮助我(需要始终返回至少一行.) [英] Help Me with my SQL Query (Need it to always return at least one row.)

问题描述

首先，这是我的SQL查询:

First off, here's my SQL query:

SELECT research_cost, tech_name,
    (SELECT research_cost
        FROM technologies
        WHERE research_cost <= USERS_RESEARCH_POINTS_VALUE
        ORDER BY research_cost DESC
        LIMIT 1) as research_prev,
    (SELECT cost
        FROM technology_costs
        WHERE id = 18
        LIMIT 1) as technology_cost
FROM `technologies`
    JOIN technology_costs
    ON id = COUNT_OF_TECHS_USER_LEARNED
WHERE research_cost > USERS_RESEARCH_POINTS_VALUE
ORDER BY research_cost ASC
LIMIT 1

网站链接: http://www.joemajewski.com/fortress/

某些人可能会认为上述查询大小适中，甚至很小；不是我.对我来说，这是我编写过的最大，最复杂的SQL查询之一，因此我将自己推向了极限.

Some people might consider the above query to be moderate-sized, or even small; not me. For me, that's one of the largest and most complex SQL queries I've ever written, so I pushed myself to my limits.

无论如何，查询本身没有语法错误，并且在大多数情况下，它确实可以执行我想要的操作.但是，有一个小问题.

Anyways, the query itself has no syntax errors, and it does exactly what I want it to do, for the most part. There's one minor issue, however.

如果主SELECT子句不返回任何行("SELECT research_cost，tech_name FROM technologies WHERE research_cost>诸如此类")，则整个查询为空集.

If the main SELECT clause doesn't return a row (the "SELECT research_cost, tech_name FROM technologies WHERE research_cost > whatever"), then the entire query is an empty set.

如果有帮助，Fortress是我正在开发的这款游戏.这是一个基于浏览器的MMORPG，用户可以在其中组建一支部队，尝试获得排行榜的头把交椅.技术是一种游戏机制，用户可以购买其统计数据(黄金收入，单位产量等)的升级版.研究是一种游戏机制，可让您通过培训科学家进行研究来学习新技术.这就是查询的全部内容.它掌握了正在研究的当前技术及其研究成本.此外，它还抓住了下一代技术将要花费多少的黄金成本.

If it helps, Fortress is this game that I'm working on. It's a browser-based MMORPG where users build an army to try and get a top stop in the leaderboards. Technologies are a game mechanic where users purchase upgrades to their stats (gold income, unit production, etc). Researching is a game mechanic that allows you to learn new technologies by training scientists to do the researching. That's what this query is all about. It grabs the current technology that is being researched, as well as the research cost of it. It additionally grabs the cost in gold of how much the next technology will cost.

所以...有什么办法我可以强制在结果集中至少返回一个空行，因为嵌套的select语句检索脚本中所需的变量，即使外部查询什么也没有返回.或者有人可以更改它，以便始终返回结果.

So... is there any way that I can force at least a null row to be returned in the result set, as the nested select statements retrieve variables that I need in the script, even if the outer query returns nothing. Or could someone possibly change it around so that a result is always returned.

如果还有其他需要，请告诉我. :)

If anything else is needed, let me know. :)

推荐答案

此查询始终返回至少1行

This query always returns at least 1 row

SELECT * FROM (__your old query__)aaa
UNION 
SELECT NULL as research_cost, NULL as tech_name, NULL as research_prev, 
... etc [FROM DUAL] // DUAL is a  dummy table in mysql

这篇关于使用我的SQL查询帮助我(需要始终返回至少一行.)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！