SQL在两个不同的表上进行GroupBy操作。

huangapple
go评论98阅读模式
英文:

SQL GroupBy on 2 different tables

问题

尝试创建一个查询，显示特定的姓名没有订购特定的物品。

在这里，我想要获取没有订购物品1（或2或3）的姓名。

当我连接这两个表并在itemid != 1的情况下进行过滤时，仍然会得到订购1的行，带有物品2和3。

如何在itemid != 1的情况下仅获得订单4？

英文:

Trying to come up with a query which shows that a particular name did not order particular item

Name OrderID
Name 1 Order 1
Name 2 Order 2
Name 3 Order 3
Name 4 Order 4
OrderID Item ID
Order 1 Item 1
Order 1 Item 2
Order 1 Item 3
Order 2 Item 1
Order 2 Item 2
Order 2 Item 3
Order 2 Item 4
Order 2 Item 5
Order 3 Item 1
Order 3 Item 2
Order 3 Item 4
Order 4 Item 3
Order 4 Item 4
Order 4 Item 5
Order 4 Item 6

Here I'm looking to get the Name which did not order Item 1 (or 2 or 3)

When I join the 2 tables, and do where itemid != 1, I'm still getting Order 1 rows with Item 2,3

How do I get only Order 4 when I do itemid != 1?

Here I'm looking to get the Name which did not order Item 1 (or 2 or 3)

When I join the 2 tables, and do where itemid != 1, I'm still getting Order 1 rows with Item 2,3

How do I get only Order 4 when I do itemid != 1?

答案1

得分: 1

选择名称
从 t1
其中不存在 (
选择 1 从 t2
其中 t2.OrderId = t1.OrderId
和 t2.ItemId = 'item 1'
);

英文:

How about...

  1. Select Name
  2. from t1
  3. where not exists (
  4.   select 1 from t2
  5.   where t2.OrderId = t1.OrderId 
  6.      and t2.ItemId = 'item 1'
  7. );

答案2

得分: 0

假设您只想使用基本的SQL语法，您可以在HAVING子句中使用JOIN、GROUP BY和2个条件（尚未订购item1且已经订购其他商品）来获得您想要的结果。

以下是一个示例代码（获取未订购item 1的名称）：

  1. SELECT
  2.    o.name
  3. FROM
  4.     orders o
  5. JOIN
  6.     order_items oi
  7. ON
  8.     o.order_id = oi.order_id
  9. GROUP BY
  10.     o.name
  11. HAVING
  12.     (SUM(CASE WHEN oi.item_id = 1 THEN 1 ELSE 0 END) = 0
  13.         AND
  14.     SUM(CASE WHEN oi.item_id != 1 THEN 1 ELSE 0 END) > 0
  15.     )

RUNNING EXAMPLE SQLFIDDLE LINK

示例输出：

name
name_4
英文:

Assume you only want to use Basic SQL syntax, you can use JOIN, GROUP BY, AND 2 conditions(haven't ordered item1 AND have ordered other items) in HAVING clause to get what you want.

See the following code as example(get the name who haven't order item 1:

  1. SELECT
  2.    o.name
  3. FROM
  4.     orders o
  5. JOIN
  6.     order_items oi
  7. ON
  8.     o.order_id = oi.order_id
  9. GROUP BY
  10.     o.name
  11. HAVING
  12.     (SUM(CASE WHEN oi.item_id =1 THEN 1 ELSE 0 END) =0
  13.         AND
  14.     SUM(CASE WHEN oi.item_id !=1 THEN 1 ELSE 0 END) >0
  16. )

RUNNING EXAMPLE SQLFIDDLE LINK

example output:

name
name_4

答案3

得分: 0

以下是您要翻译的内容：

  1. WITH
  2. -- 您的输入
  3. orders("Name", Order_ID) AS (
  4.           SELECT 'Name 1', 'Order 1'
  5. UNION ALL SELECT 'Name 2', 'Order 2'
  6. UNION ALL SELECT 'Name 3', 'Order 3'
  7. UNION ALL SELECT 'Name 4', 'Order 4'
  8. )
  9. ,
  10. order_items(Order_ID, Item_ID) AS (
  11.           SELECT 'Order 1', 1
  12. UNION ALL SELECT 'Order 1', 2
  13. UNION ALL SELECT 'Order 1', 3
  14. UNION ALL SELECT 'Order 2', 1
  15. UNION ALL SELECT 'Order 2', 2
  16. UNION ALL SELECT 'Order 2', 3
  17. UNION ALL SELECT 'Order 2', 4
  18. UNION ALL SELECT 'Order 2', 5
  19. UNION ALL SELECT 'Order 3', 1
  20. UNION ALL SELECT 'Order 3', 2
  21. UNION ALL SELECT 'Order 3', 4
  22. UNION ALL SELECT 'Order 4', 3
  23. UNION ALL SELECT 'Order 4', 4
  24. UNION ALL SELECT 'Order 4', 5
  25. UNION ALL SELECT 'Order 4', 6
  26. )
  27. -- 您的输入结束 - 真正的查询从下面开始
  28. SELECT
  29.   "Name"
  30. FROM orders
  31. LEFT JOIN order_items
  32.   ON order_items.order_id = orders.order_id
  33.   AND item_id = 1
  34. WHERE order_items.order_id IS NULL;
  35. -- 输出   Name  
  36. -- 输出 --------
  37. -- 输出  Name 4
英文:

Or like this - a failing LEFT JOIN - using the data formats as from your dbfiddle entry...

  1. WITH
  2. -- your input
  3. orders("Name",Order_ID) AS (
  4.           SELECT 'Name 1','Order 1'
  5. UNION ALL SELECT 'Name 2','Order 2'
  6. UNION ALL SELECT 'Name 3','Order 3'
  7. UNION ALL SELECT 'Name 4','Order 4'
  8. )
  9. ,
  10. order_items(Order_ID,Item_ID) AS (
  11.           SELECT 'Order 1',1
  12. UNION ALL SELECT 'Order 1',2
  13. UNION ALL SELECT 'Order 1',3
  14. UNION ALL SELECT 'Order 2',1
  15. UNION ALL SELECT 'Order 2',2
  16. UNION ALL SELECT 'Order 2',3
  17. UNION ALL SELECT 'Order 2',4
  18. UNION ALL SELECT 'Order 2',5
  19. UNION ALL SELECT 'Order 3',1
  20. UNION ALL SELECT 'Order 3',2
  21. UNION ALL SELECT 'Order 3',4
  22. UNION ALL SELECT 'Order 4',3
  23. UNION ALL SELECT 'Order 4',4
  24. UNION ALL SELECT 'Order 4',5
  25. UNION ALL SELECT 'Order 4',6
  26. )
  27. -- end of your input - real query starts below
  28. SELECT
  29.   "Name"
  30. FROM orders
  31. LEFT JOIN order_items
  32.   ON order_items.order_id=orders.order_id
  33.  AND item_id = 1
  34. WHERE order_items.order_id IS NULL;
  35. -- out   Name  
  36. -- out --------
  37. -- out  Name 4

答案4

得分: 0

这个查询基本上会返回那些没有订购某个物品的人的姓名，你可以通过选择given_item_id来确定是哪个物品。如果你需要更详细的输出，比如在第一行显示某个物品，然后在左边显示没有订购它的人的列表，那么我们可能需要知道你正在使用的数据库（Oracle？PostgreSQL？MySQL？），因为这些信息需要用于选择正确的聚合/连接函数。

英文:

It's a little difficult to reply not having the actual tables at hand and not knowing the exact structure of the output you wish to see.

This query basically returns you the names of the people that DID NOT order some item, and you choose which one by picking the given_item_id. If you need more granular output, like some item on the first row and then the list of the people who did not order it on the left then we might need to know the DB you are using (Oracle? PostgreSQL? MySQL?), as this information is required to pick the correct aggregation/concatenation function.

  1. SELECT DISTINCT ords.person_name
  2. FROM orders AS ords
  3. LEFT JOIN items AS itms ON ords.order_id = itms.order_id
  4. WHERE itms.item_id = 'given_item_id'
  5.    OR ords.person_name NOT IN (
  6.      SELECT person_name
  7.      FROM orders
  8.      WHERE order_id IN (
  9.        SELECT order_id
  10.        FROM items
  11.        WHERE item_id = 'given_item_id'
  12.      )
  13.    );

答案5

得分: 0

An other way to do it using left join

  1. select t1.Name
  2. from table1 t1
  3. left join (
  4.   select Name
  5.   from table1 t1
  6.   inner join table2 t2 on t1.OrderID = t2.OrderID
  7.   WHERE t2.ItemID = 'Item 1'
  8. ) as s on s.Name = t1.Name
  9. where s.Name is null;

示例在此处

英文:

An other way to do it using left join

  1. select t1.Name
  2. from table1 t1
  3. left join (
  4. select Name
  5. from table1 t1
  6. inner join table2 t2 on t1.OrderID = t2.OrderID
  7. WHERE t2.ItemID = 'Item 1'
  8. ) as s on s.Name = t1.Name
  9. where s.Name is null;

Demo here

答案6

得分: 0

以下是代码部分的翻译：

  1. Jast another example, all not ordered by Name items
  2. select n.Name,i.ItemId,t.ItemId xItemId from
  3. table1 n left join (select distinct ItemId from table2) i on 1=1
  4. left join
  5. (
  6. select t1.Name,t2.ItemId
  7. from table1 t1 left join table2 t2 on t2.OrderId=t1.OrderId
  8. group by t1.Name,t2.ItemId
  9. )t on t.Name=n.Name and t.ItemId=i.ItemId
  10. where t.Itemid is null

请注意，这部分是原始代码，没有进行翻译。

英文:

Jast another example, all not ordered by Name items

  1. select n.Name,i.ItemId,t.ItemId xItemId from
  2. table1 n left join (select distinct  ItemId from table2) i on 1=1
  3. left join
  4. (
  5. select t1.Name,t2.ItemId
  6. from table1 t1 left join table2 t2 on t2.OrderId=t1.OrderId
  7. group by t1.Name,t2.ItemId
  8. )t on t.Name=n.Name and t.ItemId=i.ItemId
  9. where t.Itemid is null
Name ItemId xItemId
Name 1 Item 4 null
Name 1 Item 5 null
Name 1 Item 6 null
Name 2 Item 6 null
Name 3 Item 3 null
Name 3 Item 5 null
Name 3 Item 6 null
Name 4 Item 1 null
Name 4 Item 2 null

huangapple
  • 本文由 发表于 2023年2月24日 06:12:07
  • 转载请务必保留本文链接：https://go.coder-hub.com/75550845.html
  • group-by
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定