查询以查找父级在SQL Server中是否有多个项目 [英] Query to find if a parent has multiple items in SQL server

问题描述

我有下表。我怎样才能找到父母是否有多个项目。



例如：父1有项目ABC和BBC，所以查询应该显示'（Multiple）'而不是显示项目名称。父5只有单项，它应该显示项目名称。

I have the following table. How can I find if a Parent has multiple items.

For Eg: Parent 1 has items ABC and BBC, So the query should display '(Multiple)' instead of showing Item names. Parent 5 has only single item, It should show the Item name.

Price      |    Item    |  Parent
-----------|------------|-------------
20         |   ABC      |   1
10         |   BBC      |   1
5          |   CBC      |   5
21         |   DDB      |   6
17         |   BDD      |   6
31         |   DBB      |   6

我尝试过：



我创建了以下查询似乎不起作用。

What I have tried:

I have created the following query which doesn't seems to be working.

SELECT CASE WHEN COUNT(*) > 1 THEN '(Multiple)' ELSE Item END FROM 
(SELECT COUNT(*), Item FROM Table WHERE Parent = @parent 
GROUP BY Item) Table GROUP BY Item

推荐答案

我认为这样做：

I think this will do it:

SELECT   parent, 
         count(*) AS [Count] 
FROM     dbo.[mytable] 
GROUP BY parent 
HAVING   COUNT(Parent) > 1

UPDATE =========== =====================

UPDATE ================================

;WITH cte AS
(
SELECT   parent, 
         SUM(1) AS quantity
FROM     dbo.[parents] 
GROUP BY parent 
)
SELECT a.parent,
       MAX(CASE WHEN b.quantity=1 THEN a.item ELSE 'Multiple' END) AS Item
FROM   dbo.[parents] AS a
FULL JOIN cte AS b ON b.parent = a.parent
GROUP BY a.parent
ORDER BY a.parent

这篇关于查询以查找父级在SQL Server中是否有多个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！