a******u
发帖数: 239 | 1 Question:
please, as simply as possible, design a single table to hold orders made by
customers. You may assume details (including names) about customers are
held in some other table. Using the table that you designed for orders, and
this other table, present a single query that gives the number of orders
for every customer, one line per customer.
My answer:
1. table design
Table: Orders
order_id Long int Primary key (auto increase)
customer_id Long int Foreign key
order_date date
… (other information about the order)
Table: Customers
customer_id Long int Primary key (auto increase)
customer_name string
… (other information about the customer)
2. SQL statement
SELECT Customers. customer_name, COUNT(Orders. order_id) AS Ordernum
FROM Customers
INNER JOIN Orders
ON Customers. customer_id =Orders. customer_id
ORDER BY Customers. customer_name
However, they said my answer is not correct, why?
Thank you very much! | b***e
发帖数: 383 | 2 ORDER BY Customers. customer_name
应该用group by customer_id， 在select语句 那里也把 customer_id 加进去。 | a9
发帖数: 21638 | 3 至少得有个给customer的订单号吧？
by
and
【在 a******u 的大作中提到】
: Question:
: please, as simply as possible, design a single table to hold orders made by
: customers. You may assume details (including names) about customers are
: held in some other table. Using the table that you designed for orders, and
: this other table, present a single query that gives the number of orders
: for every customer, one line per customer.
: My answer:
: 1. table design
: Table: Orders
: order_id Long int Primary key (auto increase)
| a******u
发帖数: 239 | 4 So, it should be:
SELECT Customers. customer_name, COUNT(Orders. order_id) AS Ordernum
FROM Customers
WHERE Customers.customer_name = “customername”
INNER JOIN Orders
ON Customers. customer_id =Orders. customer_id
GROUP BY Customers. customer_id
Now I understand why I should use "Group By".
Thank you very much. | r****t
发帖数: 10904 | 5 1. order by 肯定不行的，必须 group by
2. on 是不是改成 where 才对？
3. inner join 我没学过，按 sql 标准是直接 from customers, orders，问题里面有要求是啥实现没有？
by
and
【在 a******u 的大作中提到】
: Question:
: please, as simply as possible, design a single table to hold orders made by
: customers. You may assume details (including names) about customers are
: held in some other table. Using the table that you designed for orders, and
: this other table, present a single query that gives the number of orders
: for every customer, one line per customer.
: My answer:
: 1. table design
: Table: Orders
: order_id Long int Primary key (auto increase)
|
|
