mysql - 从 MySQL 到 MS SQL，所有列都无效？

Question

我有以下在 MySQL 中完美执行的查询，但在 MS SQL Server 上执行时却给我带来了麻烦:

SELECT failedlogins.*, siteprofiles.failedLogins AS max,
   COUNT(failedlogins.id) AS total
FROM failedlogins RIGHT JOIN siteprofiles ON failedlogins > 0
WHERE computerName LIKE 'some awesome name' AND timeStamp > 1340752043
GROUP BY computerName

我收到以下错误:

Msg 8120, Level 16, State 1, Line 2
Column 'failedlogins.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'failedlogins.timeStamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'failedlogins.userName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'siteprofiles.failedLogins' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

我认为它与RIGHT JOIN有关，但我不确定如何解决。

正如旁注，如果您不熟悉 MySQL，我将 siteprofiles 中的唯一行正确加入到 failedlogins 表中。通常，您会看到一个 = 符号，表示喜欢多个表中的两行。在 MySQL 中，如果您只选择一个行来联接表，并且不使用等号运算符将行链接在一起，它将将该行联接到返回的每一行。

有人可以帮我调试上面的语句吗？

---

编辑:

这是构造failedlogins 表的 SQL:

CREATE TABLE failedlogins (
  "id" int NOT NULL,
  "timeStamp" int NOT NULL,
  "computerName" NTEXT NOT NULL,
  "userName" NTEXT NOT NULL,
  PRIMARY KEY ("id")
) ;

和 siteprofiles 表:

CREATE TABLE siteprofiles (
  "id" int NOT NULL,
  "siteName" varchar(200) NOT NULL,
  "paddingTop" tinyint NOT NULL,
  "paddingLeft" tinyint NOT NULL,
  "paddingRight" tinyint NOT NULL,
  "paddingBottom" tinyint NOT NULL,
  "width" int NOT NULL,
  "height" int NOT NULL,
  "sideBar" text NOT NULL,
  "auto" text NOT NULL,
  "siteFooter" text NOT NULL,
  "author" varchar(200) NOT NULL,
  "language" varchar(15) NOT NULL,
  "copyright" varchar(200) NOT NULL,
  "description" NTEXT NOT NULL,
  "meta" text NOT NULL,
  "timeZone" varchar(20) NOT NULL,
  "welcome" text NOT NULL,
  "style" varchar(200) NOT NULL,
  "iconType" text NOT NULL,
  "spellCheckerAPI" varchar(50) NOT NULL,
  "saptcha" text NOT NULL,
  "question" NTEXT NOT NULL,
  "answer" NTEXT NOT NULL,
  "failedLogins" int NOT NULL,
  PRIMARY KEY ("siteName")
);

INSERT INTO siteprofiles (id, siteName, paddingTop, paddingLeft, paddingRight, paddingBottom, width, height, sideBar, auto, siteFooter, author, language, copyright, description, meta, timeZone, welcome, style, iconType, spellCheckerAPI, saptcha, question, answer, failedLogins) VALUES
(1, 'The Bell News Magazine', 0, 0, 0, 0, 260, 180, 'Right', '', '<p>&copy; 2011 The Bell News Magazine</p>', 'The Bell News Magazine', 'en-US', '© 2011 The Bell News Magazine', 'The collaborative, innovative Bell News Magazine', 'The Bell News Magazine, The PAVCS Bell News Magazine, The Pennsylvania Virtual Charter School Bell News Magazine, Pennsylvania Virtual Charter School Bell News Magazine, Bell News Magazine, Bell News, Bell Magazine, The Bell Magazine, The Bell News', 'America/New_York', 'Ads', 'onlineUniversity.css', 'gif', 'jmyppg6c5k5ajtqcra7u4eql4l864mps48auuqliy3cccqrb6b', 'auto', '', '', 5);

感谢您的宝贵时间。

Answer 1

自从提供第一个答案后，您似乎已经编辑了问题。错误的调子

Column 'failedlogins.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

... 是因为尽管 MySQL(在其默认配置中)对 GROUP BY 的内容非常宽松，但其他 RDBMS 却不是。为了从 failedlogins.* 中提取所有其他列，您需要针对仅对其相关组列进行计数的子查询JOIN。

SELECT
  failedlogins.*, 
  siteprofiles.failedlogins AS max,
  logincount
FROM 
  failedlogins RIGHT JOIN siteprofiles ON failedlogins > 0
  /* Join matches remaining columns to the counts of computerName */
  INNER JOIN (
    /* Subquery gets computerName and count to join against */
    SELECT computerName, COUNT(*) logincount FROM failedlogins GROUP BY computerName
  ) logincounter ON failedlogins.computerName LIKE logincounter.computerName
WHERE failedlogins.computerName LIKE 'some awesome name' AND timeStamp > 1340752043