如何在sqlite中过滤不一致的记录

发布于 2025-02-04 17:51:12 字数 1110 浏览 3 评论 0原文

假设我有以下记录的SQLite表：

recid	ProduciD	ProductName
1	1	产品A
2	2	产品B
3	2	产品C
4	3	产品D
5	3	产品D

RECID =主键，自动增量。

如果我运行：

SELECT productID, productName 
FROM table 
GROUP BY productID, productName

结果是：

ProductID	ProductName
1	产品A
2	产品B
2	产品C
3	产品D

如您所见，ProductID 2具有不一致的productName：产品B和产品C。如何运行查询只是为了检测不一致的查询？例如，我希望结果是：

productid	productname
2	product b
2	产品c

原文

Say I have SQLite table with the following records:

recID	productID	productName
1	1	Product A
2	2	Product B
3	2	Product C
4	3	Product D
5	3	Product D

recID = primary key, auto increment.

If I run:

SELECT productID, productName 
FROM table 
GROUP BY productID, productName

Result is:

productID	productName
1	Product A
2	Product B
2	Product C
3	Product D

As you can see, productID 2 has inconsistent productName: Product B and Product C. How do I run query just to detect the inconsistent ones? Eg I want the result to be:

productID	productName
2	Product B
2	Product C

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夜未央樱花落 2025-02-11 17:51:12

使用存在，以超过1 product> product> productiD product> productname s：

SELECT t1.productID, t1.productName 
FROM tablename t1
WHERE EXISTS (
  SELECT *
  FROM tablename t2
  WHERE t2.productID = t1.productID AND t2.productName <> t1.productName 
);

或，对于一个小数据集productname s productid 的，中的操作员：

SELECT productID, productName 
FROM tablename
WHERE productID IN (
  SELECT productID
  FROM tablename
  GROUP BY productID
  HAVING COUNT(DISTINCT productName) > 1
);

Use EXISTS to get a productID with more than 1 productNames:

SELECT t1.productID, t1.productName 
FROM tablename t1
WHERE EXISTS (
  SELECT *
  FROM tablename t2
  WHERE t2.productID = t1.productID AND t2.productName <> t1.productName 
);

Or, for a small dataset use aggregation in a subquery which counts the distinct number of productNames of each productID, with the operator IN:

SELECT productID, productName 
FROM tablename
WHERE productID IN (
  SELECT productID
  FROM tablename
  GROUP BY productID
  HAVING COUNT(DISTINCT productName) > 1
);

回复收藏 0 原文

~没有更多了~