如何将select *语句与count语句结合起来？ [英] How to combine select * statement with count statement?

问题描述

我有两张桌子： -

 Product and Order_Details 
 
 
 Product Order_Details 
 ----------------- ----------------- 
 Product_ID（int）Order_ID（int） 
 Product_Name（varchar）Product_Name（varchar）
 Product_Spec 

样本数据： -

 Order_Details 
 ----------------- 
 OrderID Product_Name 
 1 Alpha 
 2 Alpha 
 3 Alpha 
 4 Bravo 
 5 Charlie 

< pre lang =text>产品
-----------------
Product_ID Product_Name Product_Spec
1 Alpha好
2 Bravo优秀
3 Charlie Mediocre

我写了两个SQL查询： -



查询1：

 从产品中选择 *  

查询2：

 选择  top   1  Product_Name，count（*） as  cnt 来自 [Order_Details]  group   by  Product_Name  order   by  count（*） desc   INNER  上的关键字> JOIN  [Product] （Order_Details.Product_Name = Product.Product_Name）

首次查询结果返回： -

 Product_ID Product_Name Product_Spec 
 1 Alpha好
 2 Bravo优秀
 3 Charlie Mediocre 

第二次查询结果返回： -  
 Product_Name cnt 
 ------------ ----- 
 Alpha 3 

所需输出： -

 Product_ID Product_Name Product_Spec 
 1 Alpha好
 

我尝试过：



我正在尝试根据我在[Order_Details]和Query2上执行的计数操作显示[Product]表中的所有可用值（select *）。关于如何做到这一点的任何想法？

解决方案

首先要注意的是你的数据库设计是错误的：你不应该在Order_Details表中存储产品名称，但产品ID，作为Products表的外键：

产品：

 ID INT 
名称NVARCHAR 
 Spec NVARCHAR 

订单：

 ID INT 
 PID INT，外键

您可能需要在订单表中添加额外信息：某种类型的链接指向客户，以及订单中的其他项目，日期，此类事物。

通常情况下，这里有三到四个表：产品，客户，订单，OrderDetails和它们相关：

OrderDetails有产品和订单的外键。

订单有一个客户的外键。



然后你收集你的信息

  SELECT  PID，COUNT（PID） AS  OrderCount  FROM  OrderDetails  GROUP   BY  PID 

哪个会返回

< pre lang =text> 1 1
2 1
3 1

对于您的数据。

然后您使用JOIN来获取您想要的信息：

  SELECT   TOP   1  p.ID，p.Name，p.Spec  FROM 产品p 
  JOIN （ SELECT  PID，COUNT（PID） AS  OrderCount  FROM  OrderDetails  GROUP   BY  PID）od 
  ON  p.ID = od.PID 
  ORDER   BY  od.OrderCount  DESC  

|我不打算建议架构更改，而是尝试展示如何实现这一点。有时我们需要查询现有的表格，我们无权自行做出一些修改。



从我准备好的问题中你尝试了以下内容（伪代码）

 选择 * 来自产品其中​​产品=（最常见的销售商品  Order_Details）

我建议的方法如下

  SELECT  *  FROM 产品
  WHERE 产品 in （选择产品 FROM  
（ SELECT   TOP   1 产品，数量（产品）
  FROM  Order_Details 
  GROUP   by 产品
  ORDER   by 计数（产品） desc ） ）

这可能不是最优雅的做事方式，但有时当需要一次性查询时，它有助于能够得到如上所示的快速答案。



快乐编码。

I have two tables :-

Product and Order_Details
    
    
         Product                     Order_Details
        -----------------         -----------------
        Product_ID (int)           Order_ID (int)
        Product_Name(varchar)      Product_Name(varchar)
        Product_Spec

Sample Data :-

Order_Details
        -----------------
    OrderID	    Product_Name
    1	         Alpha
    2	         Alpha
    3	         Alpha
    4            Bravo
    5            Charlie

Product
    -----------------
    Product_ID	    Product_Name      Product_Spec 
        1	         Alpha              Good
        2	         Bravo              Excellent
        3	         Charlie            Mediocre

I have written two SQL queries :-

Query 1:

Select * from Product

Query 2:

select top 1 Product_Name, count(*) as cnt from [Order_Details] group by Product_Name order by count(*) desc INNER JOIN [Product] on (Order_Details.Product_Name = Product.Product_Name)

First Query Results return :-

Product_ID	    Product_Name      Product_Spec 
            1	         Alpha              Good
            2	         Bravo              Excellent
            3	         Charlie            Mediocre

Second Query Results return :-
        Product_Name	    cnt 
        ------------       -----
          Alpha              3

Desired output :-

Product_ID      Product_Name      Product_Spec
     1             Alpha             Good

What I have tried:

I'm trying to display all values available (select *)in [Product] table based on the count operation I have done on [Order_Details] and Query2. Any ideas on how to do this?

解决方案

The first thing to notice is that your database design is wrong: you should not be storing the product name in your Order_Details table, but the Product ID instead, as a Foreign Key to the Products table:
Products:

ID     INT
Name   NVARCHAR
Spec   NVARCHAR

Orders:

ID     INT
PID    INT, Foreign key

And you probably need extra info in your Orders table: some kind of link to the customer, and to other items in the order, date, that kind of thing.
Normally, you'd have three or four tables here: Products, Customers, Orders, OrderDetails and they would be related:
OrderDetails has foreign keys to Products and Orders.
Orders has a foreign key to Customers.

Then you'd collect your info as

SELECT PID, COUNT(PID) AS OrderCount FROM OrderDetails GROUP BY PID

Which would return

1    1
2    1
3    1

For your data.
You then use a JOIN onto that to get the info you want:

SELECT TOP 1 p.ID, p.Name, p.Spec FROM Products p
JOIN (SELECT PID, COUNT(PID) AS OrderCount FROM OrderDetails GROUP BY PID) od
   ON p.ID = od.PID
ORDER BY od.OrderCount DESC

|I am not going to suggest schema changes, but instead try and show how this can be achieved. Sometimes we need to query existing tables where we do not have the liberty to make some of the changes suggested.

From my ready of the question you are attempting the following (pseudo code)

Select * From Product where Product = (most common sold item in Order_Details)

My suggested approach is below

SELECT * FROM Product
WHERE Product in (Select Product FROM 
    (SELECT TOP 1 Product, Count(Product) 
     FROM Order_Details 
     GROUP by Product 
     ORDER by Count(Product) desc))

This may not be the most elegant way of doing things, but sometimes when a one-off query is needed it is helpful to be able to get a quick answer as shown above.

Happy coding.

这篇关于如何将select *语句与count语句结合起来？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！