SQL查询问题 [英] Problem with SQL query

问题描述

您好！你能告诉我在我的查询SQL中有什么错误吗？

我检查了每个表的字段的常用数字并检查语法，但我不明白，如何修复错误。

Hello! Can you tell me what is error there is in my query SQL?
I checked common number of each table's fields and check syntax, but i dont understand, how fix error.

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as ssss FROM typeorder UNION SELECT "estate", idTYPE, TYPENAME_AZ, T' at line 1

SELECT "type", idTYPEORDER AS "id", TYPEORDERNAME_AZ AS "name", TYPEORDERNAME_AZ as name_d, TYPEORDERNAME as name_ru, DEFAULT as ssss FROM typeorder
UNION
SELECT "estate", idTYPE, TYPENAME_AZ, TYPENAME_AZ,TYPENAME,DEFAULT FROM type_estate
UNION
SELECT "flats", idAPARTAMENT_FLATS, APARTAMENT_FLATSNAME_AZ, APARTAMENT_FLATSNAME_AZ, APARTAMENT_FLATSNAME, DEFAULT FROM apartament_flats
UNION
SELECT "currency", idCURRENCY, CURRENCYNAME_AZ, CURRENCYNAME_AZ, CURRENCYNAME, DEFAULT FROM currency
UNION
SELECT "country", idCOUNTRY, COUNTRYNAME_AZ, COUNTRYNAME_AZ, COUNTRYNAME , DEFAULTFROM country
UNION
SELECT "region", idREGION, REGIONNAME_AZ, REGIONNAME_AZ, REGIONNAME, DEFAULT FROM region
UNION
SELECT "city", idCITY, CITYNAME_AZ, CITYNAME_AZ, CITYNAME, DEFAULT FROM city
UNION
SELECT "village", idVILLAGE, VILLAGENAME_AZ,VILLAGENAME_AZ, VILLAGENAME, DEFAULT FROM village
UNION
SELECT "bedroom", idBEDROOM, BEDROOMNAME_AZ, BEDROOMNAME_AZ, BEDROOMNAME, DEFAULT FROM bedroom
UNION
SELECT "position", idPOSITION, POSITIONNAME_AZ, POSITIONNAME_AZ, POSITIONNAME, DEFAULT FROM position
UNION
SELECT "toilet", idTOILET, TOILETNAME_AZ, TOILETNAME_AZ, TOILETNAME, DEFAULT FROM toilet
UNION
SELECT "view", idVIEW, VIEWNAME_AZ, VIEWNAME_AZ, VIEWNAME, DEFAULT FROM view
UNION
SELECT "flat", idAPARTAMENT_FLAT, APARTAMENT_FLATNAME_AZ, APARTAMENT_FLATNAME_AZ, APARTAMENT_FLATNAME, DEFAULT FROM apartament_flat
UNION
SELECT "repair", idREPAIRTYPE, REPAIRTYPENAME_AZ, REPAIRTYPENAME_AZ, REPAIRTYPENAME, DEFAULT FROM repairtype
UNION
SELECT "project", idPROJECT, PROJECTNAME_AZ, PROJECTNAME_AZ, PROJECTNAME, DEFAULT FROM project
UNION
SELECT "rooms", idApartamentrooms, APARTAMENTROOMS_AZ, APARTAMENTROOMS_AZ, APARTAMENTROOMS, DEFAULT FROM apartament_rooms
UNION
SELECT "position_type", idPOSITION, POSITIONNAME_AZ, POSITIONNAME_AZ, POSITIONNAME, DEFAULT FROM position_type
UNION
SELECT "comfort", idCOMFORT, COMFORTNAME_AZ, COMFORTNAME_AZ, COMFORTNAME, DEFAULT FROM comfort
UNION
SELECT "metro", idMETRO, METRONAME_AZ, METRONAME_AZ, METRONAME, DEFAULT FROM metro ORDER BY name

推荐答案

首先......正如Mike Meinz所指出的那样 DEFAULT 是一个关键词。更改数据库中列的名称或（这是SQL Server，但我认为它适用于MySQL）用方括号括起列名...即 [DEFAULT]



其次......您正在使用 UNION 将包含非常不同数据项的查询连接在一起...平面&安培;庄园可能会在一起，但厕所和厕所那里的房间也是?????如果你想优化这个查询，你需要提供一些关于你真正试图用这个查询的结果做什么的提示......目前它似乎毫无意义。



第三，了解空白是你最好的朋友，特别是当你使用的工具说在线错误 6 附近'作为ssss时......这么容易比第一行错误更明确地指出问题（严重的是，你的DBMS真的想说在这篇很长的文本中的某个地方没有人可以阅读）



至少在每个 UNION 周围放置一些回车 - 我个人更喜欢每个子句（ SELECT，FROM，UNION，ORDER BY etc）在另一条线上 - 这些年来我很好地解决了这个问题

First ... as Mike Meinz pointed out DEFAULT is a key word. Change the name of the columns on your database or (this is SQL Server but I think it works with MySQL) surround the column name with square brackets ... i.e. [DEFAULT]

Secondly... you are using UNION to join together queries containing very different data items ... flats & estates might go together, but putting toilets & rooms in there too????? If you want to "optimise" this query you will need to give hints of what you're actually trying to do with the results of this query ... at the moment it appears to be pointless.

Thirdly, learn that whitespace is your best friend, especially when you use tools that say things like "Error on line 6 near 'as ssss" ... so much easier to pinpoint the problem than "Error on line 1" (seriously, your DBMS really wants to say "somewhere in this very long line of text that no-one can read")

At the very least put some carriage returns around each UNION - I personally prefer having each clause (SELECT, FROM, UNION, ORDER BY etc) on a separate line - it's served me very well over the years to get straight to the problem

这篇关于SQL查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！