T-SQL，如何通过查询进行分组?

I have a view with this information:

我对这些信息有一个看法:

TableA (IDTableA, IDTableB, IDTableC, Active, date, ...)

表a (IDTableA, IDTableB, IDTableC, Active, date，…)

For each register in TableA and each register in tableC, I want the register of tableB that have the max date and is active.

对于表a中的每个寄存器和tableC中的每个寄存器，我都希望表b的寄存器具有最大日期并处于活动状态。

select IDTableA, IDtableC, IDTableB, Date, Active
from myView
where Active = 1
group by IDTableA, IDTableC
Having Max(Date)
order by IDTableA;

This query works with SQLite, but if I try this query in SQL Server I get an error that say that IDTableB in the select is not contained in the group clause.

这个查询与SQLite一起工作，但是如果我在SQL Server中尝试这个查询，会得到一个错误，说select中的IDTableB不包含在group子句中。

I know that in theory the first query in the SQLite shouldn't work, but do it.

我知道，从理论上讲，SQLite中的第一个查询不应该工作，但应该这样做。

How can I do this query in SQL Server?

如何在SQL Server中执行此查询?

Thanks.

谢谢。

3 个解决方案

#1

Try this,

试试这个,

select * from table1 b
where active = 1
and date = (select max(date) from table1
            where idtablea = b.idtablea
            and idtablec = b.idtablec
            and active = 1);

SQLFIDDLE DEMO

SQLFIDDLE演示

#2

According to SQL 92, if you use GROUP BY clause, then in SELECT output expression list you can only use columns mentioned in GROUP BY list, or any other columns but they must be wrapped in aggregate functions like count(), sum(), avg(), max(), min() and so on.

根据SQL 92，如果您使用GROUP BY子句，那么在SELECT output expression列表中，您只能使用GROUP BY list中提到的列，或者任何其他列，但是它们必须封装在聚合函数中，如count()、sum()、avg()、max()、min()等。

Some servers like MSSQL, Postgres are strict about this rule, but for example MySQL and SQLite are very relaxed and forgiving - and this is why it surprised you.

一些服务器，如MSSQL, Postgres对这条规则是严格的，但是例如MySQL和SQLite是非常放松和宽容的——这就是为什么它会让你感到惊讶。

Long story short - if you want this to work in MSSQL, adhere to SQL92 requirement.

长话短说——如果您想让它在MSSQL中工作，请遵循SQL92需求。

#3

This query in SQLServer

这个查询在SQLServer

select IDTableA, IDtableC, IDTableB, Date, Active
from myView v1
where Active = 1
  AND EXISTS (
              SELECT 1
              FROM myView v2
              group by v2.IDTableA, v2.IDTableC
              Having Max(v2.Date) = v1.Date
              )
order by v1.IDTableA;

或

Also in SQLServer2005+ you can use CTE with ROW_NUMBER

同样在SQLServer2005+中，您可以使用带有ROW_NUMBER的CTE。

;WITH cte AS
 (              
  select IDTableA, IDtableC, IDTableB, [Date], Active,
         ROW_NUMBER() OVER(PARTITION BY IDTableA, IDTableC ORDER BY [Date] DESC) AS rn
  from myView v1
  where Active = 1
  )
  SELECT *
  FROM cte
  WHERE rn = 1
  ORDER BY IDTableA

#1