代码之家 › 专栏 › 技术社区 › Scotch

查询所有子级都有一对/副本的父级

sql-server sql

-1

Scotch · 技术社区 · 8 年前

我在查询中寻找方向以获取ID's where,对于每个事务,返回TransDd's where

TransID      Product   QTY  Price  
1            a         2    1.0
1            a         2    1.0
1            b         3    2.5
2            a         1    1.0
2            a         1    1.0
2            b         2    2.0
2            b         2    2.0
3            a         5    2.0
3            a         4    3.0
4            a         1    2.0
4            a         1    2.0
4            b         2    2.0
4            b         2    2.0
4            c         1    1.0

select transid, product, qty, price
     , row_number() over (partition by transid, product, qty, price order by transID desc) rk 
from x

但我觉得我走错了方向。欣赏任何方向。

6 回复 | 直到 8 年前

Gordon Linoff 8 年前

你可以用 count() row_number() :

select transid
from (select x.*,
             count(*) over (partition by transid, product, qty, price) as cnt 
      from x
     ) x
group by transid
having min(cnt) > 1;

不过,这有点过头了,你也可以用 group by

select transid
from (select transid, product, qty, price, count(*) as cnt
      from x
      group by transid, product, qty, price
     ) x
group by transid
having min(cnt) > 1;

Thom A 8 年前

如果我理解正确,这将为您提供您想要的答案:

CREATE TABLE dbo.SampleData (TransID int, Product char(1), Qty int, Price decimal(2,1));
INSERT INTO dbo.SampleData (TransID,
                            Product,
                            Qty,
                            Price)
VALUES (1,'a',2,1.0),
       (1,'a',2,1.0),
       (1,'a',2,1.0),
       (1,'b',3,2.5),
       (2,'a',1,1.0),
       (2,'a',1,1.0),
       (2,'b',2,2.0),
       (2,'b',2,2.0),
       (3,'a',5,2.0),
       (3,'a',4,3.0);

WITH Counts AS (
    SELECT TransID,Product,Qty,
           COUNT(*) AS Dups
    FROM dbo.SampleData
    GROUP BY TransID, Product, Qty)
SELECT TransID
FROM Counts
GROUP BY TransID
HAVING MIN(Dups) >= 2;

DROP TABLE dbo.SampleData;

Tab Alleman 8 年前

使用NOT EXISTS并检查不存在没有重复行的id。

paparazzo 8 年前

select TransID 
from table
except
select TransID
from table 
group by TransID, Product, QTY, Price 
having count(*) = 1

MichaÅ Turczyn 8 年前

尝试此查询:

select transid,
       product, 
       qty, 
       price
from (
    select transid,
           product, 
           qty, 
           price,
           count(*) over (partition by transid, product) cntproduct,
           count(*) over (partition by transid, qty) cntqty,
           count(*) over (partition by transid, price) cntprice
    from my_table
) a where cntprice > 1 and cntproduct > 1 and cntqty > 1

-2

MichaÅ Turczyn 8 年前

SELECT TransID FROM
(
    SELECT COUNT(*) AS Count, TransID, Product, QTY, Price
    FROM x
    GROUP BY TransID, Product, QTY, Price
    HAVING Count = 2
) AS Table1
NOT IN
SELECT TransID FROM
(
    SELECT COUNT(*) AS Count, TransID, Product, QTY, Price
    FROM x
    GROUP BY TransID, Product, QTY, Price
    HAVING Count = 1
) AS Table2

然后阅读TransID。完成!