您的位置: 首页  >  技术问答  >  FirebirdSQL联盟重复记录

FirebirdSQL联盟重复记录

分类: 技术问答 • 2022-08-31 20:46:05
问题描述：

使用FirebirdSQL 3.0.2。我正在尝试使用固定行来提取报告。意思是，我想每次都有一定数量的行。如果结果集中不存在某行，我想显示缺失的行的值为零。FirebirdSQL联盟重复记录

为此，我有一个虚拟表RAPOR_EK，将所有可能的行组合包含在行中。我在我的主要报告SQL中使用UNION选择该表。

我的问题是关于重复的记录。在我的主要报告SQL中选择了一条记录的情况下，我从虚拟表中选择了一条附加记录。

表结构，虚拟表记录，报表SQL和报表SQL输出包含在下面。您可以在报表SQL输出中的行号1-2,5-6,7-8等中看到重复的记录。

我很感激任何帮助。

原始数据表DDL

CREATE TABLE TABLO_MEDAS (
ID BIGINT NOT NULL, 
T_GERIBILDIRIMNO INTEGER, 
T_ILANTARIHI TIMESTAMP, 
T_KODNO INTEGER, 
T_KADEME SMALLINT, 
T_IL VARCHAR(15), 
T_ILCE VARCHAR(20), 
T_SEBEKEUNSURU VARCHAR(220), 
T_KESINTINEDENIILISKIACIKLAMA VARCHAR(250), 
TKS_KAYNAGAGORE VARCHAR(20), 
TKS_SUREYEGORE VARCHAR(10), 
TKS_SEBEBEGORE VARCHAR(20), 
T_BILDIRIMEGORE VARCHAR(15), 
T_BASLAMATARIHI TIMESTAMP, 
T_SONAERMETARIHI TIMESTAMP, 
T_KESINTISURESI DOUBLE PRECISION, 
TEKS_IMARALANICIOG DOUBLE PRECISION, 
TEKS_IMARALANICIAG DOUBLE PRECISION, 
TEKS_IMARALANDISIOG DOUBLE PRECISION, 
TEKS_IMARALANDISIAG DOUBLE PRECISION, 
TES_IMARALANICIOG DOUBLE PRECISION, 
TES_IMARALANICIAG DOUBLE PRECISION, 
TES_IMARALANDISIOG DOUBLE PRECISION, 
TES_IMARALANDISIAG DOUBLE PRECISION, 
T_ISLETME VARCHAR(50), 
T_SURE VARCHAR(25), 
CONSTRAINT PK_TABLO_MEDAS PRIMARY KEY (ID) 
);

虚表DDL

CREATE TABLE RAPOR_EK (
    KAYNAK VARCHAR(30), 
    SEBEP VARCHAR(30) 
) ;

报告SQL

select 
    '2015-01-01' AS "TARIH", 
    10 AS "RAPOR_TURU", 
    M.TKS_KAYNAGAGORE AS "KAYNAK", 
    M.TKS_SEBEBEGORE AS "SEBEP", 
    (sum(M.TEKS_IMARALANICIOG)/(select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IAIK_OG", 
    (sum(M.TEKS_IMARALANICIAG)/(select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IAIK_AG", 
    ((sum(M.TEKS_IMARALANICIOG) + sum(M.TEKS_IMARALANICIAG))/((select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))) AS "IAIK_TOPLAM", 
    (sum(M.TEKS_IMARALANDISIOG)/(select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IADK_OG", 
    (sum(M.TEKS_IMARALANDISIAG)/(select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IADK_AG", 
    ((sum(M.TEKS_IMARALANDISIOG) + sum(M.TEKS_IMARALANDISIAG))/((select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))) AS "IADK_TOPLAM", 
    ((sum(M.TEKS_IMARALANICIOG) + sum(M.TEKS_IMARALANICIAG) + (sum(M.TEKS_IMARALANDISIOG) + sum(M.TEKS_IMARALANDISIAG)))/((select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))) AS "GNLTOPLAM" 
from TABLO_MEDAS M 
where 
    M.T_ILCE = 'CİHANBEYLİ' 
    and T_BILDIRIMEGORE = 'Bildirimsiz' 
    and (T_BASLAMATARIHI >= '2015-01-01' and T_BASLAMATARIHI <= '2015-01-31 23:59:59') 
group by 
    M.TKS_KAYNAGAGORE, 
    M.TKS_SEBEBEGORE 

UNION 

SELECT 
    '2015-01-01' AS "TARIH", 
    10 AS "RAPOR_TURU", 
    KAYNAK, 
    SEBEP, 
    0, 
    0, 
    0, 
    0, 
    0, 
    0, 
    0 
FROM 
    RAPOR_EK

个虚表记录

KAYNAK SEBEP 
Dağıtım-AG Dışsal 
Dağıtım-AG Şebeke işletmecisi 
Dağıtım-AG Mücbir Sebep 
Dağıtım-AG Güvenlik 
Dağıtım-OG Dışsal 
Dağıtım-OG Şebeke işletmecisi 
Dağıtım-OG Mücbir Sebep 
Dağıtım-OG Güvenlik 
İletim Sistemi Şebeke işletmecisi 
İletim Sistemi Mücbir Sebep 
İletim Sistemi Dışsal 
İletim Sistemi Güvenlik

报告SQL输出

TARIH RAPOR_TURU KAYNAK SEBEP IAIK_OG IAIK_AG IAIK_TOPLAM IADK_OG IADK_AG IADK_TOPLAM GNLTOPLAM 
2015-01-01 10 Dağıtım-AG Dışsal 0 0 0 0 0 0 0 
2015-01-01 10 Dağıtım-AG Dışsal 0 0.00046546808634433004 0.000462962962962963 0 0.003875217981011432 0.0028260562385191467 0.0011495668596296753 
2015-01-01 10 Dağıtım-AG Güvenlik 0 0 0 0 0 0 0 
2015-01-01 10 Dağıtım-AG Mücbir Sebep 0 0 0 0 0 0 0 
2015-01-01 10 Dağıtım-AG Şebeke işletmecisi 0 0 0 0 0 0 0 
2015-01-01 10 Dağıtım-AG Şebeke işletmecisi 0 0.0402048059579915 0.03998842592592593 0 0.09726797132338694 0.07093401158683058 0.04897975941207866 
2015-01-01 10 Dağıtım-OG Dışsal 0 0 0 0 0 0 0 
2015-01-01 10 Dağıtım-OG Dışsal 0 0.052365159713737126 0.052083333333333336 0.042275574112734866 0 0.011445527766002543 0.04027589604631112 
2015-01-01 10 Dağıtım-OG Güvenlik 0 0 0 0 0 0 0 
2015-01-01 10 Dağıtım-OG Güvenlik 0.16129032258064516 0.0005818351079304125 0.0014467592592592592 0 0 0 0.00102639898181221 
2015-01-01 10 Dağıtım-OG Mücbir Sebep 0 0 0 0 0 0 0 
2015-01-01 10 Dağıtım-OG Şebeke işletmecisi 0 0 0 0 0 0 0 
2015-01-01 10 Dağıtım-OG Şebeke işletmecisi 0.6881720430107527 0.8316751032757317 0.8309027777777778 2.8491649269311066 1.3673706645998838 1.768545994065282 1.1033378494888533 
2015-01-01 10 İletim Sistemi Dışsal 0 0 0 0 0 0 0 
2015-01-01 10 İletim Sistemi Güvenlik 0 0 0 0 0 0 0 
2015-01-01 10 İletim Sistemi Mücbir Sebep 0 0 0 0 0 0 0 
2015-01-01 10 İletim Sistemi Şebeke işletmecisi 0 0 0 0 0 0 0

编辑： 使用SQL低于返回行只存在于TABLO_MEDAS。

SELECT 
    '2015-01-01' AS "TARIH", 
    10 AS "RAPOR_TURU", 
    RAPOR_EK.KAYNAK, 
    RAPOR_EK.SEBEP, 
    (sum(M.TEKS_IMARALANICIOG)/(select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IAIK_OG", 
    (sum(M.TEKS_IMARALANICIAG)/(select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IAIK_AG", 
    ((sum(M.TEKS_IMARALANICIOG) + sum(M.TEKS_IMARALANICIAG))/((select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))) AS "IAIK_TOPLAM", 
    (sum(M.TEKS_IMARALANDISIOG)/(select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IADK_OG", 
    (sum(M.TEKS_IMARALANDISIAG)/(select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')) as "IADK_AG", 
    ((sum(M.TEKS_IMARALANDISIOG) + sum(M.TEKS_IMARALANDISIAG))/((select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))) AS "IADK_TOPLAM", 
    ((sum(M.TEKS_IMARALANICIOG) + sum(M.TEKS_IMARALANICIAG) + (sum(M.TEKS_IMARALANDISIOG) + sum(M.TEKS_IMARALANDISIAG)))/((select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))) AS "GNLTOPLAM" 
FROM 
    RAPOR_EK 
LEFT JOIN TABLO_MEDAS AS M ON M.TKS_KAYNAGAGORE = RAPOR_EK.KAYNAK AND M.TKS_SEBEBEGORE = RAPOR_EK.SEBEP 
where 
    M.T_ILCE = 'CİHANBEYLİ' 
    and T_BILDIRIMEGORE = 'Bildirimsiz' 
    and (T_BASLAMATARIHI >= '2015-01-01' and T_BASLAMATARIHI <= '2015-01-31 23:59:59') 
group by 
    KAYNAK, 
    SEBEP

工作SQL如下：

SELECT 
    RAPOR_EK.KAYNAK, 
    RAPOR_EK.SEBEP, 
    COALESCE(DT.IAIK_OG, 0) AS "IAIK_OG", 
    COALESCE(DT.IAIK_AG, 0) AS "IAIK_AG", 
    COALESCE(DT.IAIK_TOPLAM, 0) AS "IAIK_TOPLAM", 
    COALESCE(DT.IADK_OG, 0) AS "IADK_OG", 
    COALESCE(DT.IADK_AG, 0) AS "IADK_AG", 
    COALESCE(DT.IADK_TOPLAM, 0) AS "IADK_TOPLAM", 
    COALESCE(DT.GNLTOPLAM, 0) AS "GNLTOPLAM" 
FROM RAPOR_EK 
LEFT JOIN (
    SELECT 
    M.TKS_KAYNAGAGORE AS "KAYNAK", 
    M.TKS_SEBEBEGORE AS "SEBEP", 
    (sum(M.TES_IMARALANICIOG)/(select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')*60) as "IAIK_OG", 
    (sum(M.TES_IMARALANICIAG)/(select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')*60) as "IAIK_AG", 
    ((sum(M.TES_IMARALANICIOG) + sum(M.TES_IMARALANICIAG))/((select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))*60) AS "IAIK_TOPLAM", 
    (sum(M.TES_IMARALANDISIOG)/(select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')*60) as "IADK_OG", 
    (sum(M.TES_IMARALANDISIAG)/(select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ')*60) as "IADK_AG", 
    ((sum(M.TES_IMARALANDISIOG) + sum(M.TES_IMARALANDISIAG))/((select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))*60) AS "IADK_TOPLAM", 
    ((sum(M.TES_IMARALANICIOG) + sum(M.TES_IMARALANICIAG) + (sum(M.TES_IMARALANDISIOG) + sum(M.TES_IMARALANDISIAG)))/((select sum(I.ILC_IMARALANICI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANICI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_OG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ') + (select sum(I.ILC_IMARALANDISI_AG) from ILCELER_TABLOSU I where I.ILCEADI = 'CİHANBEYLİ'))*60) AS "GNLTOPLAM" 
    FROM TABLO_MEDAS M 
    WHERE 
    M.T_ILCE = 'CİHANBEYLİ' 
    AND M.T_BILDIRIMEGORE = 'Bildirimsiz' 
    AND M.TKS_SUREYEGORE = 'Uzun' 
    AND (M.T_BASLAMATARIHI >= '2015-01-01' AND T_BASLAMATARIHI <= '2015-01-31 23:59:59') 
    GROUP BY 
    M.TKS_KAYNAGAGORE, 
    M.TKS_SEBEBEGORE 
) DT USING (KAYNAK, SEBEP) 
ORDER BY KAYNAK, SEBEP
+0

联接using (report_id_1, report_id_2)我想你想的'RAPOR_EK'和'TABLO_MEDAS'之间的外部联接 –

+0

尝试了像'LEFT OUTER JOIN RAPOR_EK R ON M.TKS_KAYNAGAGORE = R.KAYNAK AND M.TKS_SEBEBEGORE = R.SEBEP'，它只显示出现在TABLO_MEDAS中的行和没有显示来自'RAPOR_EK'的记录。 –

+0

如果'RAPOR_EK'包含所有你想要的**行，你希望'从RAPOR_EK向左加入TABLO_MEDAS ...否则你想要一个'完全外连接' –

答

鉴于您的数据的复杂性，我创建了一个简单的模式来演示解决方案：

create table report_source (
    REPORT_ID_1 VARCHAR(25) NOT NULL, 
    REPORT_ID_2 VARCHAR(25) NOT NULL, 
    CONSTRAINT PK_REPORT_SOURCE PRIMARY KEY (REPORT_ID_1, REPORT_ID_2) 
); 

create table report_data (
    REPORT_ID_1 VARCHAR(25) NOT NULL, 
    REPORT_ID_2 VARCHAR(25) NOT NULL, 
    VALUE_1 INT, 
    VALUE_2 INT 
); 

insert into report_source (report_id_1, report_id_2) values ('a', 'a'); 
insert into report_source (report_id_1, report_id_2) values ('a', 'b'); 
insert into report_source (report_id_1, report_id_2) values ('b', 'a'); 
insert into report_source (report_id_1, report_id_2) values ('b', 'b'); 

insert into report_data (report_id_1, report_id_2, value_1, value_2) values ('a', 'a', 1, 2); 
insert into report_data (report_id_1, report_id_2, value_1, value_2) values ('b', 'b', 3, 4);

凡report_source有与您的rapor_ek和report_data相同的角色tablo_medas。

我们得到你所需要的数据，我们从report_source选择所有的行和左连接到report_data，用默认值替换null值：

select src.report_id_1, src.report_id_2, 
    coalesce(dt.value_1, 0) as value_1, 
    coalesce(dt.value_2, 0) as value_2 
from report_source src 
left join report_data dt using (report_id_1, report_id_2)

其中产量：

a a 1 2 
a b 0 0 
b a 0 0 
b b 3 4

但是，你的代码并不那么简单，因为你的where子句中也有条件将左连接转换为内连接。要获得默认值的报表数据，该查询需要采取的report_data的地方，换句话说：

select src.report_id_1, src.report_id_2, 
    coalesce(dt.value_1, 0) as value_1, 
    coalesce(dt.value_2, 0) as value_2 
from report_source src 
left join (
    select * 
    from report_data 
    where <conditions> 
    <group by, etc> 
) dt using (report_id_1, report_id_2)

您可能还需要更换与on dt.report_id_1 = src.report_id_1 and dt.report_id_1 = src.report_id_2

+0

我是否需要使用'使用（report_id_1，report_id_2）'或者我可以使用它，如果我选择？我的测试表明，我的两个版本都是平等的。 –

+0

@ErtanKüçükoglu不，看看我的结尾段落，'using'只是更短，但只有在两个表中的列具有相同名称时才能使用它。请参阅[Firebird语言参考]（https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-select-joins-qualified）下的“命名连接“ –

相关推荐