分组BY语句错误以获取唯一记录

 收藏

我是SQL Server的新手,曾经与MYSQL一起工作,并尝试使用Group By从表中获取记录。

表结构如下:

SELECT S1.ID,S1.Template_ID,S1.Assigned_By,S1.Assignees,S1.Active FROM "Schedule" AS S1;

输出:

ID Template_ID  Assigned_By Assignees Active
2   25          1           3         1
3   25          5           6         1
6   26          5           6         1

我需要使用下面的Group By语句获取所有列的值

SELECT Template_ID FROM "Schedule" WHERE "Assignees" IN(6, 3) GROUP BY "Template_ID";

输出:

Template_ID

25
26

我尝试使用下面的代码使用Group By来获取表,但它正在获取所有行。

SELECT S1.ID,S1.Template_ID,S1.Assigned_By,S1.Assignees,S1.Active FROM "Schedule" AS S1 INNER JOIN(SELECT Template_ID FROM "Schedule" WHERE "Assignees" IN(6, 3) GROUP BY "Template_ID") AS S2 ON S2.Template_ID=S1.Template_ID

我的输出应该像

   ID Template_ID  Assigned_By Assignees Active
    2   25          1           3         1
    6   26          5           6         1

我想知道是否也可以获取列的ID?我使用ID来编辑网络中的记录。

回复
  • Out 回复

    除了偶然,查询在MySQL中也无法正常工作。

    Nonaggregated columns in MySQL aren't part of the SQL standard and not even allowed in MySQL 5.7 and later unless the default value of the ONLY_FULL_GROUP_BY mode is changed.

    In earlier versions the result is non-deterministic.

    服务器可以从每个组中自由选择任何值,因此,除非它们相同,否则选择的值是不确定的。此外,通过添加ORDER BY子句不会影响从每个组中选择值。

    这意味着无法知道此查询将返回哪些行:

    SELECT S1.ID,S1.Template_ID,S1.Assigned_By,S1.Assignees,S1.Active 
    FROM "Schedule" AS S1
    GROUP BY Template_ID;
    

    To get deterministic results you'd need a way to rank rows with the ranking functions introduced in MySQL 8, like ROW_NUMBER(). These are already available in SQL Server since SQL Server 2012 at least. The syntax is the same for both databases :

    WITH ranked as AS 
    (
        SELECT 
            ID,Template_ID,Assigned_By,Assignees Active, 
            ROW_NUMBER(PARTITION BY Template_ID Order BY ID)
        FROM Scheduled
        WHERE Assignees IN(6, 3) 
    )
    SELECT ID,Template_ID,Assigned_By,Assignees Active
    FROM ranked
    Where RN=1
    

    PARTITION BY Template_ID splits the result rows based on their Template_ID value into separate partitions. Within that partition, the rows are ordered based on the ORDER BY clause. Finally, ROW_NUMBER calculates a row number for each ordered partition row.