将所有不在前n个的数据选择为“其他”。

Question

我希望有人能指出我在哪里出错了，但我已经花了30分钟看着这个问题，却没有任何进展。

我有一个临时表格，其中填充了数据。前端应用程序无法为我执行任何逻辑，所以请原谅表格中丑陋的情况语句逻辑。

用户对返回的结果集感到满意，因为我得到了前10条记录。他们现在决定要看到其余国家（所有不在前10名的行）作为“其他”的一组。

我尝试创建不在前10名的国家的分组，但它没有起作用，我计划将此结果与前10个结果合并。

SELECT c.Country, count(*) AS 'Total_Number_of_customers', COALESCE(ili.new_customers,0) AS 'New_Customers', COALESCE(ilb.existing_first,0) AS 'Existing_First_Trans', COALESCE(ilc.existing_old,0) AS 'Existing_Prev_Trans'
FROM #customer_tmp c 
LEFT JOIN (SELECT z.country, count(*) AS 'new_customers' FROM #customer_tmp z where z.customer_type='New_Customer' group by z.country)ili ON ili.country = c.country
LEFT JOIN (SELECT zy.country, count(*) AS 'existing_first' FROM #customer_tmp zy where zy.customer_type='Existing_Customer' AND zy.first_transaction=1 group by zy.country)ilb ON ilb.country = c.country
LEFT JOIN (SELECT zx.country, count(*) AS 'existing_old' FROM #customer_tmp zx where zx.customer_type='Existing_Customer' AND zx.first_transaction=0 group by zx.country)ilc ON ilc.country = c.country
GROUP BY c.country, ili.new_customers, ilb.existing_first, ilc.existing_old
ORDER BY 2 DESC

这里是我用来从表格中获取结果的SQL语句。

作为参考，我的临时表格中每一行都包含一个客户ID，他们被创建的日期和他们的客户类型，这与我想要实现的目标有关。

希望这是一个简单的问题，我可能只是有点慢。。

非常感谢。

- Wes Price

Answer 1

使用 SQL Server 中的 EXCEPT 运算符：

SELECT <fields>
FROM <table>
WHERE <conditons>
EXCEPT
<Query you want excluded>

- JNK

Answer 2

可以在查询中添加行号，然后按行号进行选择：

SELECT * FROM (

SELECT c.Country, count(*) AS 'Total_Number_of_customers', 
row_number() OVER (ORDER BY COUNT(*) DESC) AS 'r',
COALESCE(ili.new_customers,0) AS 'New_Customers', COALESCE(ilb.existing_first,0) AS 'Existing_First_Trans', COALESCE(ilc.existing_old,0) AS 'Existing_Prev_Trans'
FROM #customer_tmp c 
LEFT JOIN (SELECT z.country, count(*) AS 'new_customers' FROM #customer_tmp z where z.customer_type='New_Customer' group by z.country)ili ON ili.country = c.country
LEFT JOIN (SELECT zy.country, count(*) AS 'existing_first' FROM #customer_tmp zy where zy.customer_type='Existing_Customer' AND zy.first_transaction=1 group by zy.country)ilb ON ilb.country = c.country
LEFT JOIN (SELECT zx.country, count(*) AS 'existing_old' FROM #customer_tmp zx where zx.customer_type='Existing_Customer' AND zx.first_transaction=0 group by zx.country)ilc ON ilc.country = c.country
GROUP BY c.country, ili.new_customers, ilb.existing_first, ilc.existing_old
ORDER BY 2 DESC

) sub_query WHERE sub_query.r >= 10

这种方法更加灵活，因为你可以运行一个查询，然后很容易地将结果分成“前十”和“其余部分”。

(这等同于Bob的回答;我猜我们恰好同时在处理这个问题！)

- Matt Gibson

Answer 3

以下是使用常用表达式（CTE）的一种方法：

WITH CTE AS
    (
    SELECT c.Country, count(*) AS 'Total_Number_of_customers', COALESCE(ili.new_customers,0) AS 'New_Customers', COALESCE(ilb.existing_first,0) AS 'Existing_First_Trans', COALESCE(ilc.existing_old,0) AS 'Existing_Prev_Trans'
        , ROW_NUMBER() OVER (ORDER BY count(*) DESC) AS sequence
    FROM #customer_tmp c 
    LEFT JOIN (SELECT z.country, count(*) AS 'new_customers' FROM #customer_tmp z where z.customer_type='New_Customer' group by z.country)ili ON ili.country = c.country
    LEFT JOIN (SELECT zy.country, count(*) AS 'existing_first' FROM #customer_tmp zy where zy.customer_type='Existing_Customer' AND zy.first_transaction=1 group by zy.country)ilb ON ilb.country = c.country
    LEFT JOIN (SELECT zx.country, count(*) AS 'existing_old' FROM #customer_tmp zx where zx.customer_type='Existing_Customer' AND zx.first_transaction=0 group by zx.country)ilc ON ilc.country = c.country
    GROUP BY c.country, ili.new_customers, ilb.existing_first, ilc.existing_old
    )

SELECT *
FROM CTE
WHERE sequence > 10
ORDER BY sequence

- bobs

Answer 4

SELECT  country, COUNT(*) cnt, SUM(new_customer), SUM(existing_first_trans), SUM(existing_prev_trans)
FROM    (
        SELECT  CASE
                WHEN country IN
                (
                SELECT  TOP 10 country
                FROM    #customer_tmp
                ORDER BY
                        COUNT(*) DESC
                ) THEN
                        country
                ELSE    'Others'
                END AS country,
                CASE WHEN customer_type = 'New_Customer' THEN 1 END AS new_customer,
                CASE WHEN customer_type = 'Existing_Customer' AND first_transaction = 1 THEN 1 AS existing_first_trans,
                CASE WHEN customer_type = 'Existing_Customer' AND first_transaction = 0 THEN 1 AS existing_prev_trans,
        FROM    #customer_tmp
        )
GROUP BY
        country
ORDER BY
        CASE country WHEN 'Others' THEN 2 ELSE 1 END, cnt DESC

- Quassnoi