mysql group by方法

Question

采购表：

========================
id|username|product|price|
01|Desmond |tv     |$40  |
02|Desmond |watch  |$20  |
03|Desmond |ring   |$30  |
04|Desmond |laptop |$40  |
========================

产品表：

============================
id|product|type |promotion_ID|
01|tv     |big  |001         |
02|watch  |small|002         |
03|ring   |small|002         |
04|laptop |big  |001         |
============================

促销ID：

==============================
id|promotion_ID|promotion_name|
01|001         |mid-year      |
02|002         |end-year      |
==============================

我想显示基于while循环方法的Purchase表的结果，以显示mysql的所有信息，这是没有问题的，但我想根据它们的促销id/促销名称对它们进行分组。我可以知道吗，我该怎么做呢？

例如，显示结果：

年中

=======================
username|product|price|
Desmond |tv     |$40  |
Desmond |laptop |$40  |
=======================

年终

=======================
username|product|price|
Desmond |watch  |$20  |
Desmond |ring   |$30  |
=======================

Answer 1

你只是在找ORDER BY和JOIN吗？

SELECT promotion_name, 
    username, 
    p.product,
    price
FROM Purchase P
    JOIN Product PR ON P.Product=PR.Product
    JOIN Promotion PO ON PR.Promotion_Id=PO.Promotion_Id
ORDER BY promotion_name, p.product

关于您的数据库，我不建议将Product存储在Purchase表中--您应该存储Product_Id。

Answer 2

This may help you..

Select username,product,price from Purchase
Join Product on Product.product = Purchase.product 
Join Promotion On promotion.promotion_ID  = Product.promotion_ID
where promotion_name = 'mid-year' // pass your argument
Order by Product.product

Answer 3

试一试

SELECT pur.username, pur.product, pur.price
FROM tbl_purchase pur 
INNER JOIN tbl_product pro USING(product)
INNER JOIN tbl_promotion prm USING(promotion_ID)
ORDER BY prm.promotion_name

注意：您尚未在所有表中引用主键，因此请对所有表的列product和promotion_id建立索引