[Mysql查询]订单有1个以上的产品 [英] [Mysql query]orders that have more than 1 product

问题描述

您好

这是我的数据库



桌面屏幕



我需要显示

1）订单超过1个产品



2）每种产品的销售数量清单



我的尝试：



1）我写了类似的东西，但这是错的

 SELECT orders.Number_ord FROM订单加入产品USING（Number_ord）GROUP BY COUNT（Number_prod）> 1 

2）在这里，我也有一个问题< br $>

 SELECT count（products.Number_prod），sum（products.Qty）FROM orders join products USING（Number_ord）GROUP BY Number_prod 

请帮助我

解决方案

建议＃1 ：不要发布表格图片的链接。只需在此处显示数据 - 例如：

  Number_ord买家送货地址日期 
 1 Martin Chicago 2018-12-16 
 2 John Berlin 2018-12 -17 
 3 Martin Chicago 2018-12-18 

或者更好的是，给我们一些我们可以复制的SQL来为自己创建样本数据 - 例如：

 创建 表 #orders（number_ord  int  身份（ 1 ， 1 ），买方 varchar （ 50 ），Delivery_address  varchar  （ 50 ），[日期] 日期 ）
 插入 进入 #orders 值（'  Martin'，' 芝加哥'，'  2018-12-16'），（'  John'，' 柏林'，'  2018-12-17'），（'  Martin'，'  Chicago'，'  2018-12-18'）
 
 创建 表 #products（number_prod  int ，number_ord  int ，数量 int ）
 插入 进入 #products 值（ 469841 ， 1  ， 15 ），（ 469841 ， 2  ， 15 ），（ 999999 ， 2  ， 6 ），
（ 558585 ， 2 ， 45 ），（ 469841 ， 3 ， 15 ），（ 844444 ， 3 ， 80 ）

建议＃2 - 不要写但这是错的或我也有问题 - 给我们详细信息任何错误信息或解释结果是如何错误



说了这么多，这里有一些帮助你的问题，但首先是一些警告：我的解决方案是标准的T-SQL，因此可能需要调整MySQL（抱歉我目前无法访问SQLFiddle.com）。我也无法在我当前有权访问的数据库上创建表，所以我使用了临时表 - 你需要删除＃标志才能让它工作对你而言。



问题＃1 - 列出订单超过1件产品



你不喜欢实际上需要任何连接来做到这一点，你只需要在Products表上的GROUP BY一个合适的列，例如

  SELECT ＃products.Number_Ord，count（*） as  cnt 来自 #products  group   by ＃products.Number_Ord < span class =code-keyword>  count（*）>  1  

这给出了相当简单的结果...

  Number_Ord cnt  
 2 3 
 3 2 

要获得更有意义的信息，您可以将其用作子查询 [ ^ ]例如

 选择＃orderss.Number_ord，＃orders.Buyer，＃orders.Delivery_address，＃orders。< span class =code-keyword> Date ，counts.cnt 
  from  #orders 
  INNER   JOIN （ SELECT ＃products.Number_Ord，count（*） as  cnt 来自 #products  group   ＃products.Number_Ord   count（*）>  1 ） as 计算 ＃orders.Number_Ord = counts.Number_Ord 

给出更好的结果......

<前lang =文字> Number_Ord买家送货地址日期cnt
2 John Berlin 2018-12-17 3
3 Martin Chicago 2018-12-18 2

问题＃2 - 每种产品的销售数量清单



如果您查看代码，您已经注意到您没有提到订单表中的任何内容。所有，所以摆脱那个连接给你

  SELECT  count（＃products.Number_prod），sum（＃products.Qty）< span class =code-keyword> FROM  #products  GROUP   BY  Number_prod 

现在这将给你以下结果：

 3 45 
 1 45 
 1 80 
 1 6 

Hm ..不完全是你所追求的 - 你有正确的数字，但如何判断哪个数字与哪个产品相关？从您的查询中删除计数（我也删除了表标识符/别名，因为它没有必要）

  SELECT  Number_prod，sum（数量） FROM  #products  GROUP   BY  Number_prod 

给你

 469841 45 
 558585 45 
 844444 80 
 999999 6 

以下是 GROUP BY [ ^ ] - 看起来你需要了解主题

Hello
This is my database

Screen of tables

I need to show
1) Orders that have more than 1 product

2) List of sold quantities for each product

What I have tried:

1)I wrote something like that, but it's wrong

SELECT orders.Number_ord FROM orders join products USING(Number_ord) GROUP BY COUNT(Number_prod)>1

2)Here, I have a problem too

SELECT count(products.Number_prod),sum(products.Qty) FROM orders join products USING(Number_ord) GROUP BY Number_prod

Please help me

解决方案

Suggestion #1: Don't post links to pictures of your tables. Just show the data here - for example:

Number_ord	Buyer	Delivery_address	Date
1		Martin	Chicago			2018-12-16
2		John	Berlin			2018-12-17
3		Martin	Chicago			2018-12-18

OR, even better, give us some SQL we can copy to create the sample data for ourselves - for example:

create table #orders (number_ord int identity(1,1), Buyer varchar(50), Delivery_address varchar(50), [Date] Date)
insert into #orders values('Martin','Chicago','2018-12-16'),('John','Berlin','2018-12-17'), ('Martin','Chicago','2018-12-18')

create table #products (number_prod int, number_ord int, Qty int)
insert into #products values (469841,1,15),(469841,2,15),(999999,2,6),
(558585,2,45),(469841,3,15),(844444,3,80)

Suggestion #2 - Don't write "but it's wrong" or "I have a problem too" - give us details of any error messages or explain how the results are wrong

Having said all that, here is some help with your problem but first some caveats: My solution is in standard T-SQL and may therefore need tweaking for MySQL (sorry I can't access SQLFiddle.com at the moment). I also can't create tables on the database I currently have access to, so I've used temporary tables - you will need to remove the # signs to get it to work for you.

Problem #1 - List Orders that have more than 1 product

You don't actually need any joins to do that, you just need to GROUP BY an appropriate column on the Products table e.g.

SELECT #products.Number_Ord, count(*) as cnt from #products group by #products.Number_Ord having count(*) > 1

which gives the rather bare results...

Number_Ord	cnt
2		3
3		2

To get more meaningful information you can use this as a sub-query[^] e.g.

select #orders.Number_ord, #orders.Buyer, #orders.Delivery_address, #orders.Date, counts.cnt
from #orders
INNER JOIN (SELECT #products.Number_Ord, count(*) as cnt from #products group by #products.Number_Ord having count(*) > 1) as counts on #orders.Number_Ord=counts.Number_Ord

which gives the somewhat nicer results ...

Number_Ord	Buyer	Delivery_address	Date		cnt
2		John	Berlin			2018-12-17	3
3		Martin	Chicago			2018-12-18	2

Problem #2 - List of sold quantities for each product

If you look at the code you already have notice that you are not referring to anything from the Orders table at all, so get rid of that join to give you

SELECT count(#products.Number_prod),sum(#products.Qty) FROM #products GROUP BY Number_prod

That will now give you these results:

3	45
1	45
1	80
1	6

Hm... not quite what you are after - you've got the numbers right but how to tell which number goes with which product? Remove the count from your query (I'm also taking away the table identifier/alias as it's not necessary)

SELECT Number_prod,sum(Qty) FROM #products GROUP BY Number_prod

which gives you

469841	45
558585	45
844444	80
999999	6

Here is a link to the documentation on GROUP BY[^] - it looks like you need to brush up on the subject

这篇关于[Mysql查询]订单有1个以上的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！