当一列具有重复行时的SQL，然后选择其他列为最小值的行

I have this table

我有这张桌子

mt.id, mt.otherId, mt.name, mt.myChar, mt.type
1      10            stack      U          "question"  
2      10            stack      D  
3      30            stack      U          "question"
4      10            stack      U          "whydownvotes"

And I want only rows with id 2 and 3 returned (without using the id, otherid as parameter) and ensuring name and type are matching against parameters. And when there is a duplicate otherId = then return the row with min myChar value. So far I have this :

我只想要返回id为2和3的行(不使用id,otherid作为参数)并确保名称和类型与参数匹配。当有一个重复的otherId =然后返回具有min myChar值的行。到目前为止我有这个:

select mt.* from myTable mt
where (mt.myChar = 'U' AND (mt.name = 'stack' AND mt.type LIKE '%question%'))
or (mt.myChar = 'D' and mt.name = 'stack')

So where otherID is 10, I want the row with min char value 'D'. I am going to need a subquery or group using min(myChar) ... ?

所以在otherID为10的情况下,我想要min char值为'D'的行。我将需要使用min(myChar)的子查询或组......?

How do i remove the first row from the sql fiddle (without using the id):

如何从sql小提琴中删除第一行(不使用id):

http://sqlfiddle.com/#!9/c579a/1

edit Jeepers, whats with the downvotes, its clear question isn't it ? There is even a sql fiddle.

编辑Jeepers,什么是downvotes,其明确的问题不是吗?甚至有一个sql小提琴。

2 个解决方案

#1

If this is SQL Server, then you can do it in two steps like this:

如果这是SQL Server,那么您可以通过以下两个步骤执行此操作:

WITH filtered AS (
  SELECT
    mt.*,
    minType = MIN(mt.type) OVER (PARTITION BY mt.otherId)
  FROM
    dbo.myTable AS mt
  WHERE (mt.myChar = 'U' AND mt.name = 'stack' AND mt.type LIKE '%question%')
     OR (mt.myChar = 'D' AND mt.name = 'stack')
)
SELECT
  id,
  otherId,
  name,
  myChar,
  type
FROM
  filtered
WHERE
  type = minType
;

The filtered subquery is basically your current query but with an additional column that holds minimum type values per otherId. The main query filters the filtered set further based on the type = minType condition.

过滤后的子查询基本上是您当前的查询,但有一个额外的列,每个otherId保存最小类型值。主查询进一步基于type = minType条件过滤过滤后的集合。

#2

I am assuming you want is a groupwise maximum, one of the most commonly-asked SQL questions You can try as , This query should work on any DBMS. But If you are using the SQL SERVER then you can use the Row_Number() which is very easy to use.Here myTable is your table.

我假设你想要的是groupwise最大值,这是你可以尝试的最常见的SQL问题之一,这个查询应该适用于任何DBMS。但是如果您正在使用SQL SERVER,那么您可以使用非常容易使用的Row_Number()。这里是myTable。

SELECT t0.*
FROM myTable AS t0
LEFT JOIN myTable AS t1 ON t0.otherId = t1.otherId  AND t1.myChar < t0.myChar
WHERE t1.myChar IS NULL;

Here is sql fiddle

这是sql小提琴

#1