MS SQL中的Sql语法 [英] Sql syntax in MS SQL

问题描述

以下基于建议的解决方案的代码不运行：

Code below based on suggested solution does NOT run:

Select IIF(`Conversation Time` > 0,1,0) AS [Answered], B.StartDate, B.EndDate, A.`Begin Time`,
A.`End Time`, A.`Charged Duration`,A.`Call Charges`,A.`Call Expense`,A.`Account ID`,
A.`Agent ID`
FROM CDRecords.csv as A
INNER JOIN SearchCriteria.csv AS B
ON (A.`End Time` BETWEEN B.StartDate and B.EndDate)
LEFT JOIN AreaCodes.csv AS C
ON A.`Area Prefix` = C.AreaPrefix

它给出了语法错误。两个连接都是单独运行的，因此它们中的任何一个都没有语法错误，至少在单独运行时报告没有。我认为它是Excel中SQL版本的限制（？）我知道它们确实不允许外连接中有两个以上的表;也许内心也是如此。



在强大的SQL中有很多好的方法可以做到这一点，所提出的解决方案就是一个很好的例子非常好的常规SQL平台，但运行SQL的MS Query具有非常有限的性能子集。您无法在多个表上运行联接（即涉及3个或更多表）。当然，我所做的并不是非常复杂，而且人们通常会想要这样做。我将尝试更多的东西，也许看起来像ADO，因为我已经在VB中，也许我可以先剪切日期范围内的记录并将其放在一个文件中，然后将名称匹配。与此同时，如果有人想在Excel的MS Query中继续抨击它，我很感谢所有的帮助。



我尝试过：



我尝试了它不起作用的建议解决方案，见上文。

It gives syntax error. Both the joins run ok separately so there is no syntax error in either of them, at least none that is reported when they run separately. I think it is a limitation in the SQL version in Excel(?) I know for sure they don't allow more than two tables in outer joins; perhaps the same is true of inner.

There are a ton of good ways to do it in robust SQL and the proposed solution is a good example of one and probably runs great on a "regular" SQL platform, but the MS Query that is running the SQL has a very limited subset of performance. You can't run joins on multiple tables (i.e. 3 or more tables are involved). Certainly what I am doing is not very complicated and something one would commonly want to do. I'm going to try a few more things and perhaps look as ADO since I'm already in VB maybe I can slice the records that are in the date range first and put it in a file then do the names match. In the meantime if anyone wants to keep hammering away at it in Excel's MS Query I am appreciating all the help.

What I have tried:

I tried the suggested solution it does not work, see above.

推荐答案

您的查询是使用隐式 INNER JOIN ，这就是为什么你没有从 A 获得没有匹配的记录记录在 B 和 C 。



更改您的查询回使用 LEFT JOIN s：

Your query is using an implicit INNER JOIN, which is why you're not getting the records from A which don't have matching records in B and C.

Change your query back to using LEFT JOINs:

SELECT
    A.SomeFields, 
    B.SomeDate,
    C.Name
FROM 
    aaa.csv AS A
    INNER JOIN bbb.csv AS B
    ON A.SomeDate BETWEEN B.Date1 And B.Date2
    LEFT JOIN ccc.csv AS C
    ON A.ID = C.ID
;

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