按 COUNT(*) > 1 筛选并显示所有相关行

Question

我有资产表（见下文）。对于所有不是"Automobile"的Asset_Type，我需要显示它，并将Asset_Value四舍五入到最接近的数百。我需要省略只有一个资产的Asset_Type类型，并按资产类型对结果进行排序。

+-------------------+----------------------------------+---------------+
|    Asset_Type     |        Asset_Description         |  Asset_Value  |
+-------------------+----------------------------------+---------------+
| Automobile        | Model T                          | $100,923.99   |
| Automobile        | 1967 Ford Mustang Convertible    | $60,000.00    |
| Automobile        | 1975 MGB                         | $52,000.00    |
| Automobile        | 1962 Avanti                      | $88,000.00    |
| Wine              | 2000 LaGrange Cabernet Sauvignon | $235.25       |
| Wine              | 1999 LaGrange Cabernet Sauvignon | $400.88       |
| Fine Art          | Hula-Hoop Girl Painting          | $1,000.00     |
| Antique Furniture | 1860 Setee                       | $1,200.00     |
| Antique Furniture | 1860 4-Post Bed                  | $1,450.00     |
| Antique Furniture | Art Deco Dresser                 | $869.99       |
| Antique Furniture | 1830 Empire Chairs (4)           | $2,200.00     |
| Structure         | Historic Register Barn           | $335,000.00   |
| Structure         | Historic Register Silo           | $335,000.00   |
| Fine Art          | The Thinker                      | $1,200,000.00 |
| Fine Art          | The Scream                       | $3,350,000.00 |
| Coins             | 1880 2-Headed Penny              | $500.00       |
| Coins             | 1932 A-Series Dime               | $750.00       |
| Coins             | Buffalo Nickel                   | $469.99       |
| Stamps            | Moon Landing                     | $175.00       |
| Stamps            | American Centennial              | $3,000.50     |
| Commodities       | 175 Acre Cotton Field            | $750,500.00   |
| Memorabilia       | UofF Football Trophy             | $16,555.55    |
| Memorabilia       | UofF Football Jersey             | $16,555.55    |
+-------------------+----------------------------------+---------------+

我可以得到大多数的标准，除了省略只有一个计数的资产。

SELECT Asset_Type, ROUND(Asset_Value/100, 0)*100 AS [Rounded Asset Value]
FROM Asset
WHERE Asset_Type <> "Automobile"
ORDER BY Asset_Type;

我尝试过使用GROUP BY Asset_Type, Asset_Value HAVING COUNT(*) > 1，但这会导致两行具有匹配的资产类型和值。由于我仍然需要列出所有资产类型，GROUP BY不会列出所有行。

感谢您的帮助！

Answer 1

试试这个

SELECT Asset_Type,
       Round(Asset_Value / 100, 0) * 100 AS [Rounded Asset Value]
FROM   Asset
WHERE  Asset_Type IN (SELECT Asset_Type
                      FROM   Asset
                      WHERE  Asset_Type <> 'Automobile'
                      GROUP  BY Asset_Type
                      HAVING Count(*) > 1)
ORDER  BY Asset_Type 

或使用EXISTS

SELECT Asset_Type,
       Round(Asset_Value / 100, 0) * 100 AS [Rounded Asset Value]
FROM   #test t
WHERE  EXISTS (SELECT Asset_Type
               FROM   #test f
               WHERE  Asset_Type <> 'Automobile'
                      AND f.Asset_Type = t.Asset_Type
               GROUP  BY Asset_Type
               HAVING Count(*) > 1)
ORDER  BY Asset_Type 

Answer 2

这里有一个替代Shep使用联接而不是子查询的答案：

SELECT a1.Asset_Type, ROUND(a1.Asset_Value/100, 0)*100 AS [Rounded Asset Value]
FROM Asset a1
INNER JOIN
(
    SELECT Asset_Type, COUNT(*) AS frequency
    FROM Asset
    GROUP BY Asset_Type
    HAVING COUNT(*) > 1
) a2
ON a1.Asset_Type = a2.Asset_Type
WHERE a1.Asset_Type <> "Automobile"
ORDER BY a1.Asset_Type;

Answer 3

有很多方法可以实现这一点。如果我正确地理解了目标，以下内容应该有效：

SELECT a.Asset_Type,
       Round(a.Asset_Value / 100, 0) * 100 AS [Rounded Asset Value]
FROM   Asset a
       JOIN (SELECT Asset_Type
             FROM   Asset
             GROUP  BY Asset_Type
             HAVING Count(1) > 1) types
         ON types.Asset_Type = a.Asset_Type
WHERE  a.Asset_Type <> "Automobile"
ORDER  BY Asset_Type 

在派生表中，您可以获得具有多行的所有不同资产类型的列表，然后连接到原始查询中的列表，这样您基本上只过滤具有多行将的类型。

Answer 4

试试这个：

SELECT Asset_Type, ROUND(Asset_Value/100, 0)*100 AS [Rounded Asset Value]
FROM Asset
WHERE Asset_Type <> "Automobile"
and Asset_Type not in 
    (select Asset_Type, count(*) 
    from Asset
    group by Asset_Type
    having count(*) =1)
ORDER BY Asset_Type;