Выберите группу строк, состоящую ровно из одного определенного элемента

ProgramBox

Выберите группу строк, состоящую ровно из одного определенного элемента

Post author:admin
Запись опубликована:9 января, 2022
Post category:Вопросы по программированию

#sql #sql-server #tsql #sql-server-2012

Вопрос:

Я работаю над одним анализом, в котором я хочу получить определенный продукт, который был куплен в моей таблице товаров. Допустим, у меня есть эти данные:

ProductID	Название продукта	ОрдерИд
1	Футболка	1
4	Губная помада	1
4	Губная помада	2
6	Джинсы	3
4	Губная помада	3
4	Губная помада	4
1	Футболка	5
6	Джинсы	5
4	Губная помада	5

Как получить полный Заказ, в котором есть покупка только губной помады?

Результат должен быть таким:

ProductID	Название продукта	ОрдерИд
4	Губная помада	2
4	Губная помада	4

1. В соответствии с руководством по вопросам, пожалуйста, покажите, что вы пробовали, и расскажите нам, что вы нашли (на этом сайте или в другом месте) и почему это не соответствует вашим потребностям.

2. Мы выбираем строки , а не элементы. Будьте осторожны с тем, как вы формулируете свой вопрос и что вы предполагаете. Если заказ содержит много строк (предположительно разных) помад, что содержит / представляет ваш набор результатов? Вы предполагаете, что в «заказе» есть одна строка для помады, как и в некоторых ответах. Ваши выборочные данные согласуются с этим предположением. Будьте осторожны — реальная жизнь никогда не бывает такой «аккуратной».

3. @SMor, мне нужно получить заказ, в котором есть одна покупка, которая является помадой, и если заказ содержит этот товар и многие другие товары, он может храниться под другим номером строки. Следовательно, я очень хотел получить этот конкретный предмет и знаю, о чем я здесь спрашиваю. Спасибо

Ответ №1:

Вы можете проверить, не существует ли для какого-либо элемента заказа другого элемента:

 SELECT *
FROM t AS t1
WHERE ProductName = 'Lipstick'
AND NOT EXISTS (
    SELECT *
    FROM t AS t2
    WHERE t2.OrderId = t1.OrderId
    AND t2.ProductName <> 'Lipstick'
)

Пояснение: внешний запрос выбирает все элементы заказа помады. Это вернет заказ № 1, 2, 3, 4 и 5. Давайте назовем эти строки элементами заказа помады.

Затем подзапрос для каждой строки в элементах заказа губной помады выбирает связанные элементы заказа (t2.OrderID = t1.OrderID), которые не содержат губной помады (t2.ProductName <> ‘Губная помада’). Если такой строки не существует, то элемент заказа помады уникален.

Ответ №2:

Один из подходов, чтобы получить список заказов, которые включают только губную помаду:

 SELECT OrderId
FROM   Item
GROUP
    BY OrderId
HAVING Min(ProductName) = 'Lipstick'
AND    Max(ProductName) = 'Lipstick'
;

Ответ №3:

Извлекайте только те продукты, в которых продукт предыдущей строки и текущий продукт совпадают. Вот почему для получения продукта предыдущих строк используется функция lag ().

 SELECT t.ProductId, t.ProductName, t.OrderId 
FROM (SELECT *
           , LAG(ProductId) OVER (ORDER BY OrderId) prev_product
      FROM item) t
WHERE t.ProductId = t.prev_product
    AND t.ProductName = 'Lipstick'

Другой способ использования предыдущего и текущего порядка строк

 SELECT t.ProductId, t.ProductName, t.OrderId 
FROM (SELECT *
           , LAG(OrderId) OVER (ORDER BY OrderId) prev_order
      FROM item) t
WHERE t.OrderId != t.prev_order
    AND t.ProductName = 'Lipstick'

Пожалуйста, проверьте по URL https://dbfiddle.uk/?rdbms=sqlserver_2016amp;fiddle=8cf359bb9bd73fd9fc9b4726117ff872

В соответствии с данными извлеките даже идентификатор заказа.

 SELECT *
FROM item
WHERE ProductName = 'Lipstick'
    AND OrderId % 2 = 0

Н. Б.: Но я рекомендую только первый вариант.

Ответ №4:

Другой вариант-использование условной агрегации

 SELECT i.OrderId
FROM Item i
GROUP BY
  i.OrderId
HAVING COUNT(CASE WHEN ProductName = 'Lipstick' THEN 1 END) > 1
  AND COUNT(CASE WHEN ProductName <> 'Lipstick' THEN 1 END) = 0;

Ответ №5:

Вы можете структурировать запрос таким образом, чтобы он показывал вам все заказы, содержащие один товар, а затем вы можете решить, какой товар извлекать, предполагая, что этот товар не всегда будет «Помадой». Кроме того, базы данных в реальной жизни часто сложны, и у вас может быть идентификатор заказа с несколькими строками одного элемента. В этом случае вам понадобится отдельный счетчик. Это можно сделать несколькими способами, но будет показано два варианта.

Подготовка базы данных

 CREATE TABLE tablename (ProductId INTEGER, 
                        ProductName VARCHAR(100), 
                        OrderId INTEGER);

INSERT INTO tablename VALUES
  (1, 'Tshirt', 1),
  (4, 'Lipstick', 1),
  (4, 'Lipstick', 2),
  (6, 'Jeans', 3),
  (4, 'Lipstick', 3),
  (4, 'Lipstick', 4),
  (1, 'Tshirt', 5),
  (6, 'Jeans', 5),
  (4, 'Lipstick', 5);

Вариант 1: Использование Перекрестного применения

 WITH Product_count AS (
SELECT t1.*, t2.cnt
FROM tablename t1 CROSS APPLY
     (SELECT COUNT(DISTINCT t2.ProductName) AS cnt
      FROM tablename t2
      WHERE t2.OrderId = t1.OrderId 
     ) t2
)
SELECT ProductId, ProductName, OrderId
FROM Product_count
WHERE cnt = 1 AND ProductName = 'Lipstick';

Вариант 2: Использование функций Окна

 WITH Product_count AS (
SELECT *,
     DENSE_RANK() OVER (PARTITION BY OrderId ORDER BY ProductName)  
       DENSE_RANK() OVER (PARTITION BY OrderId ORDER BY ProductName DESC) - 1 AS cnt
FROM tablename
)
SELECT ProductId, ProductName, OrderId
FROM Product_count
WHERE cnt = 1 AND ProductName = 'Lipstick';

Метки: Выберите группу строк, состоящую ровно из одного определенного элемента