Сколько клиентов купили товар А в том же порядке, что и товар Б
У меня есть таблица daily_orders, где я хотел найти Клиентов, которые покупали чехлы для мобильных телефонов в том же порядке, что и мобильный телефон.
Вот структура таблицы
CREATE TABLE daily_orders (
Order_date datetime,
orderid varchar(20) NOT NULL,
CustomerID int NOT NULL,
Product varchar(20) ,
Product_Category text,
Units int
);
-- insert some values
INSERT INTO daily_orders VALUES ('2021-06-15 20:06:31','AABS123',46162,'sku1223','mobile phone',1);
INSERT INTO daily_orders VALUES ('2021-07-01 13:21:22','AABWA23',87949,'sku213','laptops',1);
INSERT INTO daily_orders VALUES ('2021-06-11 07:01:25','AA12WA13',122123,'sku1223','mobile phone',1);
INSERT INTO daily_orders VALUES ('2021-06-11 07:01:25','AA12WA13',122123,'sku1223','mobile covers',2);
ВЫВОД должен быть
CustomerID
122123
Запрос, который я пробовал
select a.customerID
from daily_orders a
inner join daily_orders b on a.CustomerID = b.CustomerID
where a.OrderID = b.OrderID and a.CustomerID = b.CustomerID
Но я не получаю желаемого результата. Любое предложение будет полезно.
orderid TEXT NOT NULL НЕТ НЕТ НЕТ НЕТ НЕТ! Этот тип данных устарел 20 лет назад. Получите помощь в проектировании таблиц и баз данных. Этому навыку нужно учиться, и он не приходит волшебным образом с определенным уровнем знаний SQL.
Ответы 5
Вы можете соединить таблицу с самой собой (один экземпляр для чехлов, другой для телефонов), чтобы получить желаемый результат. Например:
select c.CustomerId
from daily_orders c
join daily_orders p on p.orderid = c.orderid
where c.Product_Category = 'mobile covers'
and p.Product_Category = 'mobile phone'
другой вариант - использовать string_agg
select t.customerid
from ( select d.customerid,
string_agg(convert(varchar(100), d.Product_Category), ',') cat
from daily_orders d
group by d.customerid
) t
where t.cat = 'mobile phone,mobile covers'
DBFiddle здесь
Но не преобразовывать в varchar без указания длины.
@AaronBertrand Вы правы, я отредактировал свой ответ
Это запрос «существует ли что-то», который лучше всего выполняется с использованием существуют.
Также обратите внимание, что типы данных Текст давно устарели и больше не должны использоваться, всегда указывайте varchar (сущ.).
select o.CustomerID
from daily_orders o
where Product_Category = 'mobile phone'
and exists (
select * from daily_orders o2
where o2.orderid = o.orderid
and o2.Product_Category = 'mobile covers'
);
Предполагая, что типом данных orderid и Product_Category является VARCHAR, а не TEXT, и нет случаев разных CustomerID для каждого orderid, сначала отфильтруйте таблицу, чтобы получить только клиентов, которые купили «чехлы для мобильных телефонов» и/или «мобильный телефон», а затем используйте агрегацию, чтобы получить только клиентов, которые купили оба товара в одном заказе:
SELECT DISTINCT CustomerID
FROM daily_orders
WHERE Product_Category IN ('mobile covers', 'mobile phone')
GROUP BY CustomerID, orderid
HAVING COUNT(DISTINCT Product_Category) = 2;
SELECT DISTINCT CustomerID
FROM (
SELECT CustomerID, orderid FROM daily_orders WHERE Product_Category = 'mobile covers'
INTERSECT
SELECT CustomerID, orderid FROM daily_orders WHERE Product_Category = 'mobile phone'
) t
Смотрите демо.
Следующий запрос должен работать, если вы измените тип данных с text на varchar
Table Definition
CREATE TABLE daily_orders (
Order_date datetime,
orderid varchar(20) NOT NULL,
CustomerID int NOT NULL,
Product varchar(20),
Product_Category varchar(20),
Units int
);
Solution
SELECT dor1.customerid,
dor1.product_category,
dor2.product_category
FROM daily_orders dor1
inner join daily_orders dor2
ON dor1.customerid = dor2.customerid
AND dor1.orderid = dor2.orderid
WHERE dor1.product_category <> dor2.product_category
AND dor1.product_category = 'mobile phone'
AND dor2.product_category = 'mobile covers';
Вы уверены, что используете SQL Server?