查询在SQLite3中正常工作，但在Microsoft SQL Server中出现语法错误

b1uwtaje 于 2022-12-13 发布在 SQLite

关注(0)|答案(1)|浏览(121)

该查询为：

SELECT 100.*COUNT(DISTINCT names_of_numbers.language_name)/variables.value
    AS "Percentage of Indo-European languages in which the words for 2 and 10 start with the same letter."
    FROM (names_of_numbers as numbers1),names_of_numbers,languages,variables
    WHERE variables.variable_name='Number of Indo-European languages' and numbers1.language_name=names_of_numbers.language_name
         and names_of_numbers.language_name=languages.language_name and languages.language_family='Indo-European' and
         substr(numbers1.word,1,1)=substr(names_of_numbers.word,1,1) and numbers1.value=2 and names_of_numbers.value=10;

Microsoft SQL Server告诉我在第三行的)附近有语法错误。这是怎么回事？我该如何在标准SQL中进行查询？

sqlite

来源：https://stackoverflow.com/questions/69423718/query-works-in-sqlite3-but-it-is-a-syntax-error-in-microsoft-sql-server

1条答案

按热度按时间

qco9c6ql1#

You get that error because of the parentheses here:

(names_of_numbers as numbers1)

Remove them.
Also, you must change SUBSTR() to SUBSTRING() which is SQL Server's equivalent function, although for your case LEFT() would also work.
Also, since you are aggregating with COUNT() the column variables.value will not be allowed by SQL Server (SQLite allows it).
Instead use a subquery that returns variables.value .
Finally, use proper joins with aliases for all the tables and ON clauses:

SELECT 100.0 * COUNT(DISTINCT n2.language_name) / 
       (SELECT value FROM variables WHERE variable_name = 'Number of Indo-European languages') 
       AS [Percentage of Indo-European languages in which the words for 2 and 10 start with the same letter]
FROM names_of_numbers n1
INNER JOIN names_of_numbers n2 ON n2.language_name = n1.language_name
INNER JOIN languages l ON l.language_name = n2.language_name
WHERE n1.value = 2 AND n2.value = 10 AND SUBSTRING(n1.word, 1, 1) = SUBSTRING(n2.word, 1, 1) 
  AND l.language_family = 'Indo-European';

赞(0）回复(0）举报 2022-12-13

我来回答

查询在SQLite3中正常工作，但在Microsoft SQL Server中出现语法错误

1条答案

相关问题

热门标签

最新问答