Firebird从表中选择一个域 [英] Firebird select from table distinct one field

问题描述

我昨天提出的问题简化了，但是我意识到我必须报告整个故事. 我必须从4个不同的表中提取4个的数据到Firebird 2.5数据库中，并且以下查询有效:

The question I asked yesterday was simplified but I realize that I have to report the whole story. I have to extract the data of 4 from 4 different tables into a Firebird 2.5 database and the following query works:

SELECT 
PRODUZIONE_T t.CODPRODUZIONE, 
PRODUZIONE_T.NUMEROCOMMESSA as numeroco,
    ANGCLIENTIFORNITORI.RAGIONESOCIALE1, 
    PRODUZIONE_T.DATACONSEGNA, 
    PRODUZIONE_T.REVISIONE,
    ANGUTENTI.NOMINATIVO, 
    ORDINI.T_DATA,
FROM PRODUZIONE_T
LEFT OUTER JOIN ORDINI_T ON PRODUZIONE_T.CODORDINE=ORDINI_T.CODORDINE 
INNER JOIN ANGCLIENTIFORNITORI ON ANGCLIENTIFORNITORI.CODCLIFOR=ORDINI_T.CODCLIFOR 
LEFT OUTER JOIN ANGUTENTI ON ANGUTENTI.IDUTENTE = PRODUZIONE_T.RESPONSABILEUC
ORDER BY right(numeroco,2) DESC, left(numeroco,3) desc 
rows 1 to 500;

但是由于REVISIONE列，查询返回的值是我的两倍(或更多). 如何仅选择单个NUMEROCOMMESSA中具有最大REVISIONE值的行?

However the query returns me double (or more) due to the REVISIONE column. How do I select only the rows of a single NUMEROCOMMESSA with the maximum REVISIONE value?

推荐答案

在这里- http ://sqlfiddle.com/#！6/ce7cf/4

样本数据(如您在原始问题中设置的那样):

Sample Data (as u set it in your original question):

create table TAB1 (
  cod integer primary key,
  n_order varchar(10) not null,
  s_date date  not null,
  revision integer not null );

alter table tab1 add constraint UQ1 unique (n_order,revision);  

insert into TAB1 values ( 1, '001/18', '2018-02-01', 0 );
insert into TAB1 values ( 2, '002/18', '2018-01-31', 0 );
insert into TAB1 values ( 3, '002/18', '2018-01-30', 1 );

查询:

select * 
from tab1 d
join ( select n_ORDER, MAX(REVISION) as REVISION
       FROM TAB1
       Group By n_ORDER ) m
  on m.n_ORDER = d.n_ORDER and m.REVISION = d.REVISION

建议:

1. 在Google上阅读经典书籍:Martin Gruber的"Understanding SQL"
2. 阅读Firebird SQL参考: https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-zh-CN/html/fblangref25.html

这里是使用Firebird 3中引入的 Windowed Functions 的另一种解决方案-

Here is yet one more solution using Windowed Functions introduced in Firebird 3 - http://sqlfiddle.com/#!6/ce7cf/13

我手边没有Firebird 3，因此实际上无法检查是否不会出现突然的不兼容，请在家进行:-D

I do not have Firebird 3 at hand, so can not actually check if there would not be some sudden incompatibility, do it at home :-D

SELECT * FROM
(
   SELECT
     TAB1.*,
     ROW_NUMBER() OVER (
         PARTITION BY n_order 
         ORDER BY revision DESC
      ) AS rank
   FROM TAB1
) d
WHERE rank = 1

阅读文档

• https://community.modeanalytics.com/sql/tutorial/sql-window-functions/
• https://www .firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-dml-windowfuncs.html

• https://community.modeanalytics.com/sql/tutorial/sql-window-functions/
• https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-dml-windowfuncs.html

这三个解决方案(包括Gordon的解决方案)中哪个更快，取决于特定的数据库-实际数据，现有索引，索引的选择性.

Which of the three (including Gordon's one) solution would be faster depends upon specific database - the real data, the existing indexes, the selectivity of indexes.

尽管窗口函数可以进行无联接查询，但我不确定对真实数据的查询是否会更快，因为它可能只是忽略order+revision cortege上的索引并执行 full-scan ，而不是在应用rank=1条件之前.尽管第一种解决方案很可能会使用索引来获取最大值，而无需实际读取表中的每一行.

While window functions can make the join-less query, I am not sure it would be faster on real data, as it maybe can just ignore indexes on order+revision cortege and do the full-scan instead, before rank=1 condition applied. While the first solution would most probably use indexes to get maximums without actually reading every row in the table.

Firebird支持的邮件列表建议了一种打破循环的方法，仅使用一个查询:技巧是同时使用Windows函数和CTE(公用表表达式):

The Firebird-support mailing list suggested a way to break out of the loop, to only use a single query: The trick is using both windows functions and CTE (common table expression): http://sqlfiddle.com/#!18/ce7cf/2

WITH TMP AS (
  SELECT 
   *,
   MAX(revision) OVER (
         PARTITION BY n_order 
       ) as max_REV
  FROM TAB1
)
SELECT * FROM TMP
WHERE revision = max_REV

这篇关于Firebird从表中选择一个域的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！