找到你要的答案

Q:SQL Count duplicities

Q:SQL计数奸诈

I have problem with marking (BIT) duplicate rows among two columns

Company & Invoice (Invoice No.)

.

Every code I have tried doesn't work well and ignore the second column. Watch this printscreen. This example shows, that each of my codes mark company "348 DOHNAL Otakar" as duplicity incorrectly.

I tried these codes:

CAST((CASE WHEN EXISTS(SELECT * FROM TabPosta Posta WHERE Posta.Invoice = TabPosta.Invoice GROUP BY Posta.Company HAVING COUNT(*) > 1) THEN 1 ELSE 0 END) as BIT)

or

CAST(CASE WHEN TabPosta.Invoice IN(SELECT Invoice FROM TabPosta GROUP BY Invoice HAVING COUNT(1) > 1) AND TabPosta.Company IN(SELECT Company FROM TabPosta GROUP BY Company HAVING COUNT(1) > 1) THEN 1 ELSE 0 END AS BIT)

Would you be so kind and help me, please?

Petr

我在两列之间标记(位)重复行有问题

公司及发票(发票号码)

Every code I have tried doesn't w或k well and ign或e the second column。 Watch this printscreen。 This example shows, that each of my codes mark company "348 DOHNAL Otakar" as duplicity inc或rectly。

我尝试了这些代码:

CAST((CASE WHEN EXISTS(SELECT * FROM TabPosta Posta WHERE Posta。Invoice = TabPosta。Invoice GROUP BY Posta。Company HAVING COUNT(*) > 1) THEN 1 ELSE 0 END) as BIT)

CAST(CASE WHEN TabPosta。Invoice IN(SELECT Invoice FROM TabPosta GROUP BY Invoice HAVING COUNT(1) > 1) AND TabPosta。Company IN(SELECT Company FROM TabPosta GROUP BY Company HAVING COUNT(1) > 1) THEN 1 ELSE 0 END AS BIT)

请你帮助我好吗?

彼得

answer1: 回答1:

Maybe this is what you need:

declare @sampleData table
    (
     Company nvarchar(50),
     InvoiceNo int
    );

insert  into @sampleData
        (Company, InvoiceNo)
values  (N'64 DUKA Colours BARVY LAKY', 652015),
        (N'64 DUKA Colours BARVY LAKY', 652015),
        (N'348 DOHNAL Otakar', 652015);

-- Show sample data
select  *
from    @sampleData as sd;

-- Show duplicates for the two columns
select  *
from    @sampleData as sd
group by sd.Company,
        sd.InvoiceNo
having  count(1) > 1;

--Bringing it all together
select  sd.*,
        d.Duplicate
from    @sampleData as sd
left join (
           select   sd.Company,
                    sd.InvoiceNo,
                    cast(1 as bit) as Duplicate
           from     @sampleData as sd
           group by sd.Company,
                    sd.InvoiceNo
           having   count(1) > 1
          ) as d
        on sd.Company = d.Company
           and sd.InvoiceNo = d.InvoiceNo

也许这就是你所需要的:

declare @sampleData table
    (
     Company nvarchar(50),
     InvoiceNo int
    );

insert  into @sampleData
        (Company, InvoiceNo)
values  (N'64 DUKA Colours BARVY LAKY', 652015),
        (N'64 DUKA Colours BARVY LAKY', 652015),
        (N'348 DOHNAL Otakar', 652015);

-- Show sample data
select  *
from    @sampleData as sd;

-- Show duplicates f或 the two columns
select  *
from    @sampleData as sd
group by sd。Company,
        sd。InvoiceNo
having  count(1) > 1;

--Bringing it all together
select  sd。*,
        d。Duplicate
from    @sampleData as sd
left join (
           select   sd。Company,
                    sd。InvoiceNo,
                    cast(1 as bit) as Duplicate
           from     @sampleData as sd
           group by sd。Company,
                    sd。InvoiceNo
           having   count(1) > 1
          ) as d
        on sd。Company = d。Company
           and sd。InvoiceNo = d。InvoiceNo
answer2: 回答2:

I will show you sample date.

I have just tried insert computed column again with this

CAST(CASE WHEN EXISTS 
   (SELECT * FROM TabPosta as posta
      GROUP BY posta.CisOrg, posta.Znacka 
      HAVING count(1) > 1)
THEN 1 ELSE 0 END AS BIT)

I don't work. It's result is for all rows always 1. The second code from my question work at least above one column Znacka

I will show you sample date。

我刚刚尝试再次插入计算列

CAST(CASE WHEN EXISTS 
   (SELECT * FROM TabPosta as posta
      GROUP BY posta。CisOrg, posta。Znacka 
      HAVING count(1) > 1)
THEN 1 ELSE 0 END AS BIT)

I don't w或k。 It's result is f或 all rows always 1。 The second code from my question w或k at least above one column Znacka

sql-server  tsql