我有一个大查询，该如何调试呢？ [英] I have a large query, how do I debug this?

问题描述

因此，我收到此错误消息：

  EDT错误：436号字符工会处或附近的语法错误b $ b  

该查询是一个大型查询，由12个较小的查询组成，这些查询都与UNION ALL连接在一起，并且每个小查询都有两个内部连接语句。因此，就像这样：

  SELECT table.someid作为id 
，table.lastname作为name 
， table2.groupname作为groupname 
，'Leads'作为类型
来自table3.specificid = table.someid 
内部联接table3在table3.specificid = table2上的表
内部联接。 groupid 
，其中table3.deleted = 0 
和table.someid> 0 
和table2.groupid在（'2'，'3'，'4'）
限制5 
 UNION全部
 query2 .... 
  

请注意，table2和table3在每个查询中都是相同的表，并且table2和table3中的字段也相同，我认为

快速提问（我对这一切还是有点陌生​​）：

类型的 Leads是什么意思？与AS之前的其他语句不同，该语句的编写方式不像table.something。

快速编辑问题：（'2'，'3'，'4'）中的table2.groupid是什么意思？

我逐个检查了每个小查询，每个小查询都能工作并返回结果，尽管由于某些原因结果总是空的（这可能取决于或可能取决于不过，用户已登录，因为一些PHP代码生成了此查询）。

至于结果本身，大多数结果看起来像这样（虽然它们是水平排列的）：

  id（整数）
名称（字符变化（80））
组名（字符变化（100））
类型（未知）
  

结果的区别是双重的：
1）大多数结果包含相同的字段名称，但相当其中一些具有不同的场长。就像有些人说字符变化（80），而另一些人说字符变化（100），如果这实际上不是字段长度，请纠正我。
2）2的查询包含不同的字段，但只有id字段不同，这可能是因为它们没有 as id部分。

我不太确定UNION ALL的要求是什么，但是如果我认为，这意味着只有在所有字段都相同的情况下才能工作，但是如果那个时髦的数字改变了（括号中的那个），那么即使它们具有相同的名称，这些字段也被认为是不同的吗？

此外，奇怪的是有些的查询返回的字段完全相同，字段长度相同，因此我尝试仅对所有查询进行UNION，但没有运气，在UNION上仍然出现语法错误。

<我应该提到的另一件事是数据库曾经是MySQL，但是我们改为了PostGreSQL，因此此错误可能是更改的结果（即，代码可能在MySQL中起作用，而在PostGres中不起作用）。

感谢您的时间。

解决方案

您只能有一个 LIMIT xxx子句。在查询末尾，而不是在UNION之前。

So, I get this error message:

EDT ERROR:  syntax error at or near "union" at character 436

The query in question is a large query that consists of 12 smaller queries all connected together with UNION ALL, and each small query has two inner join statements. So, something like:

SELECT table.someid as id
    ,table.lastname as name
    ,table2.groupname as groupname
    , 'Leads     ' as Type
from table 
inner join table3 on table3.specificid = table.someid
INNER JOIN table2 on table3.specificid=table2.groupid
where table3.deleted=0 
 and table.someid > 0
 and table2.groupid in ('2','3','4')
LIMIT 5 
UNION all 
query2....

Note that table2 and table3 are the same tables in each query, and the fields from table2 and table3 are also the same, I think.

Quick question (I am still kinda new to all this):
What does 'Leads ' as Type mean? Unlike the other statements preceding an AS, this one isn't written like table.something.
Quick edit question: What does table2.groupid in ('2','3','4') mean?

I checked each small query one by one, each one works and returns a result, though the results are always empty for some reason(this may or may not be dependent on the user logged in though, as some PHP code generated this query).

As for the results themselves, most of them look something like this (they are arranged horizontally though):

id(integer)
name (character varying(80))
groupname (character varying(100))
type (unknown)

The difference in the results are twofold: 1)Most of the results contain the same field names but quite a few of them have different field lengths. Like some will say character varying (80), while others will say character varying (100), please correct me if this is actually not field length. 2)2 of the queries contain different fields, but only the id field is different, and it's probably because they don't have the "as id" part.

I am not quite sure of what the requirements of UNION ALL are, but if I think, it is meant to only work if all the fields are the same, but if that funky number changes (the one in the brackets), then are the fields considered to be different even if they have the same name?

Also, what's strange is that some of the queries returned the exact same fields, with the same field length, so I tried to UNION ALL only those queries, but no luck, still got a syntax error at UNION.

Another important thing I should mention is that the DB used to be MySQL, but we changed to PostGreSQL, so this bug might be a result of the change (i.e. code that might work in MySQL but not in PostGres).

Thanks for your time.

解决方案

You can have only one "LIMIT xxx" clause. At the end of the query, and not before the UNION.

这篇关于我有一个大查询，该如何调试呢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！