oracle SQL如何选择具有2个或更多计数的帐户？

2g32fytz 于 2023-06-22 发布在 Oracle

关注(0)|答案(2)|浏览(156)

问题案例是用我想要的类似方法制作的。
我想得到的数据，如果客户订购了同一项目多次（2或更多）。我希望每个订单的详细信息是分开的，但如果他们已经订购了相同的产品两次或更多次，我只想看到两次行，可以切断其余的。
目前，我得到了同一个客户的行和行。
我知道你可以做COUNT和GROUP BY，但我实际上想要的是略有不同（有点难以列出我所做的一切）。
示例案例
假设我正在查看连接在一起的3个表
1.客户详细信息（客户）

客户帐户、职务、姓名、出生日期、性别、年龄、订单ID

1.交货详情（交货）

客户帐户、订单ID、国家/地区、州、城市、郊区/城镇、街道名称、街道编号、单位编号、邮政编码

1.采购详细信息（订单）

客户帐户、订单ID、产品ID、数量、产品价格

我需要客户帐户和名称，订单编号，交货郊区，产品编号

产品ID范围为5 - 10（包括5和10）
客户名称以J开头

SELECT
    CUSTOMER.customer_account,
    CUSTOMER.customer_name,
    CUSTOMER.order_id,
    DELIVERY.suburb,
    ORDER.product_id

FROM CUSTOMER
    LEFT JOIN DELIVERY
        ON CUSTOMER.customer_account = DELIVERY.customer_account
        AND CUSTOMER.order_id = DELIVERY.order_id
    LEFT JOIN ORDER
        ON CUSTOMER.customer_account = ORDER.customer_account
        AND CUSTOMER.order_id = ORDER.order_id

WHERE
    product_id >= 5
    AND
    product_id <= 10
    AND
    SUBSTR(customer_name, 1, 1) = 'J'

假设我得到的输出是（* 将被删除）
| 客户帐户|客户名称|订单id|郊区|产品ID|
| - -----|- -----|- -----|- -----|- -----|
| ACC0000000000001|约翰|000001|一个|六|
| ACC0000000000001|约翰|000007|一个|六|
| ACC0000000000001|约翰|000012|一个|六|
| ACC0000000000001|约翰|000020|一个|九个|
| ACC0000000000002|雅各布|000025| B级|七个|
| ACC0000000000002|雅各布|000028| B级|七个|
| ACC0000000000002|雅各布|000029| B级|七个|
| ACC0000000000003|杰伦|000035| C类|十个|
| ACC0000000000004|杰伦|000040| C类|十个|
| ACC0000000000004|杰伦|000048| C类|八|
| ACC0000000000004|杰伦|000050| C类|八|
| ACC0000000000004|杰伦|000059| C类|六|
| ACC0000000000004|杰伦|000068| C类|六|
| ACC0000000000005|杰森|000080|一个|九个|
| ACC0000000000005|杰森|000088|一个|九个|
| ACC0000000000005|杰森|000090|一个|九个|
| ACC0000000000006|詹姆斯|000098|开|六|
| ACC0000000000006|詹姆斯|000106|开|六|
| ACC0000000000007|乔丹|000112| T型|六|
| ACC0000000000007|乔丹|000117| T型|七个|
| ACC0000000000007|乔丹|000126| T型|七个|
| ACC0000000000007|乔丹|000133| T型|六|
| ACC0000000000007|乔丹|000139| T型|5个|
| ACC0000000000007|乔丹|000148| T型|七个|
| ACC0000000000007|乔丹|000155| T型|七个|
| ACC0000000000007|乔丹|000167| T型|六|
| ACC0000000000008|乔丹|000172| L型|九个|
| ACC0000000000008|乔丹|000180| L型|九个|
| ACC0000000000008|乔丹|000186| L型|九个|
| ACC0000000000008|乔丹|000188| L型|八|
我想要的输出是
| 客户帐户|客户名称|订单id|郊区|产品ID|
| - -----|- -----|- -----|- -----|- -----|
| ACC0000000000001|约翰|000001|一个|六|
| ACC0000000000001|约翰|000007|一个|六|
| ACC0000000000002|雅各布|000025| B级|七个|
| ACC0000000000002|雅各布|000028| B级|七个|
| ACC0000000000003|杰伦|000035| C类|十个|
| ACC0000000000004|杰伦|000040| C类|十个|
| ACC0000000000004|杰伦|000048| C类|八|
| ACC0000000000004|杰伦|000050| C类|八|
| ACC0000000000004|杰伦|000059| C类|六|
| ACC0000000000004|杰伦|000068| C类|六|
| ACC0000000000005|杰森|000080|一个|九个|
| ACC0000000000005|杰森|000088|一个|九个|
| ACC0000000000006|詹姆斯|000098|开|六|
| ACC0000000000006|詹姆斯|000106|开|六|
| ACC0000000000007|乔丹|000112| T型|六|
| ACC0000000000007|乔丹|000117| T型|七个|
| ACC0000000000007|乔丹|000126| T型|七个|
| ACC0000000000007|乔丹|000133| T型|六|
| ACC0000000000007|乔丹|000139| T型|5个|
| ACC0000000000008|乔丹|000172| L型|九个|
| ACC0000000000008|乔丹|000180| L型|九个|
| ACC0000000000008|乔丹|000188| L型|八|
我的想法是做一些像ROWNUM/FETCH具体每一个，但不是真的很确定，如果这是正确的方法，也不知道如何使用它…

oracle

来源：https://stackoverflow.com/questions/76511372/sql-how-to-select-for-accounts-with-2-or-more-counts

2条答案

按热度按时间

fkvaft9z1#

您可以通过以下方式使用ROWNUMBER()和PARTITION BY来实现此目的。

SELECT 
    CUSTOMER.customer_account,
    CUSTOMER.customer_name,
    CUSTOMER.order_id,
    DELIVERY.suburb,
    ORDER.product_id
FROM CUSTOMER
    LEFT JOIN DELIVERY
        ON CUSTOMER.customer_account = DELIVERY.customer_account
        AND CUSTOMER.order_id = DELIVERY.order_id
    LEFT JOIN (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_account, order_id) AS row_num
                  FROM ORDER
                  WHERE (order_id, customer_account) IN (
                    SELECT order_id, customer_account
                    FROM ORDER
                    GROUP BY order_id, customer_acccount
                    HAVING COUNT(*) >= 2
                  )
               ) AS LIMITED_ORDER
        ON CUSTOMER.customer_account = LIMITED_ORDER.customer_account
        AND CUSTOMER.order_id = LIMITED_ORDER.order_id
WHERE
    product_id >= 5
    AND
    product_id <= 10
    AND
    SUBSTR(customer_name, 1, 1) = 'J'
    AND
    row_num <= 2;

赞(0）回复(0）举报 2023-06-22

busg9geu2#

您需要：

对ORDER使用带引号的标识符，因为它是保留字;
将ORDER表从LEFT OUTER JOIN更改为INNER JOIN，因为连接是必需的而不是可选的;
使用COUNT解析函数计算每个customer_account和product_id配对的频率;
使用ROW_NUMBER解析函数对每个customer_account和product_id对中的行进行编号，以便将输出限制为每个对两行。

像这样：

SELECT c.customer_account,
       c.customer_name,
       c.order_id,
       d.suburb,
       o.product_id
FROM   CUSTOMER c
       INNER JOIN (
         SELECT o.*,
                COUNT(order_id) OVER (PARTITION BY customer_account, product_id) AS cnt,
                ROW_NUMBER() OVER (
                  PARTITION BY customer_account, product_id
                  ORDER BY order_id
                ) AS rn
         FROM   "ORDER" o
         WHERE  product_id BETWEEN 5 AND 10
       ) o
       ON     c.customer_account =  o.customer_account
          AND c.order_id         =  o.order_id
          AND o.cnt              >= 2
          AND o.rn               <= 2
       LEFT OUTER JOIN DELIVERY d
       ON     c.customer_account = d.customer_account
          AND c.order_id         = d.order_id
WHERE  c.customer_name LIKE 'J%';

赞(0）回复(0）举报 2023-06-22

我来回答

oracle SQL如何选择具有2个或更多计数的帐户？

2条答案

相关问题

热门标签

最新问答