j***y
发帖数: 2074 | 1 一个公司发来的题目，不过我实在是不会啊。请帮忙：
If you know SQL, 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.
谢谢啊。 |
m*********2
发帖数: 701 | 2 select count (*) from CustomerOrders group by customerID.
gives
【在 j***y 的大作中提到】
: 一个公司发来的题目，不过我实在是不会啊。请帮忙：
: If you know SQL, 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.
: 谢谢啊。
|
v***n
发帖数: 5085 | 3 huh... This is a reasonable company... |
v***n
发帖数: 5085 | 4 你没看懂 人家公司要看的是一个inner join
【在 m*********2 的大作中提到】
: select count (*) from CustomerOrders group by customerID.
:
: gives
|
j***y
发帖数: 2074 | 5 谢谢啊，综合一下网上搜索的create的方法，是否应该这样？
CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_SID),
Foreign Key (Customer_SID) references CUSTOMER(SID));
select count (*) from ORDERS group by CUSTOMER
对了，这个count是个啥意思啊？ |
j***y
发帖数: 2074 | 6
好象你说的有道理，结果应该是一个customer一行。mercury的结果似乎还是一个order
一行？
【在 v***n 的大作中提到】
: 你没看懂 人家公司要看的是一个inner join
|
v***n
发帖数: 5085 | 7 u r close, but I bet an inner join is what they would like to see.
【在 j***y 的大作中提到】
: 谢谢啊，综合一下网上搜索的create的方法，是否应该这样？
: CREATE TABLE ORDERS
: (Order_ID integer,
: Order_Date date,
: Customer_SID integer,
: Amount double,
: Primary Key (Order_SID),
: Foreign Key (Customer_SID) references CUSTOMER(SID));
: select count (*) from ORDERS group by CUSTOMER
: 对了，这个count是个啥意思啊？
|
j***y
发帖数: 2074 | 8
能否直接给个答案啊？那边公司等着要，没时间学啊。
【在 v***n 的大作中提到】
: u r close, but I bet an inner join is what they would like to see.
|
v***n
发帖数: 5085 | 9 不能
【在 j***y 的大作中提到】
:
: 能否直接给个答案啊？那边公司等着要，没时间学啊。
|
j***y
发帖数: 2074 | 10 搜索了个答案，大家给看看：
CREATE TABLE orders
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references customers(SID));
SELECT Customer_Firstname, Customer_Lastname, orders.Number
FROM customers
INNER JOIN (SELECT Customer_SID, count(*) as Number from orders Group by
Customer_SID order by Number desc limit 5) AS orders
on customers.Customer_ID = orders.Customer_SID ; |
|
|
c******n
发帖数: 710 | 11 Assume two tables: orders and customers
SELECT names, COUNT(*)
FROM orders JOIN customers ON orders.customerid=customers.id
GROUP BY customers.id |
m*********2
发帖数: 701 | 12 why?
CustomerOrders is the relationship table.
【在 v***n 的大作中提到】
: 你没看懂 人家公司要看的是一个inner join
|
R****i
发帖数: 104 | 13 Count(*) is counting the number of orders made by this customer.
You can change the count(*) to sum(Amount) which will summarize the total
amount ($) of this customer.
【在 c******n 的大作中提到】
: Assume two tables: orders and customers
: SELECT names, COUNT(*)
: FROM orders JOIN customers ON orders.customerid=customers.id
: GROUP BY customers.id
|
R****i
发帖数: 104 | 14
^ A typo here: Primary Key (Order_ID).
【在 j***y 的大作中提到】
: 谢谢啊，综合一下网上搜索的create的方法，是否应该这样？
: CREATE TABLE ORDERS
: (Order_ID integer,
: Order_Date date,
: Customer_SID integer,
: Amount double,
: Primary Key (Order_SID),
: Foreign Key (Customer_SID) references CUSTOMER(SID));
: select count (*) from ORDERS group by CUSTOMER
: 对了，这个count是个啥意思啊？
|
g**e
发帖数: 6127 | 15 确实是非常reasonable，不过问个group的查询，90％的人答不上来，因为平时用的少
。更不用说问having，having和where的区别之类的。这种题目问fresh grad没什么意思
【在 v***n 的大作中提到】
: huh... This is a reasonable company...
|
m********l
发帖数: 4394 | 16 确实, 俺现在还没懂
出个题.
如何把每个customer的orders全列出来, 然后来个总和
意思
【在 g**e 的大作中提到】
: 确实是非常reasonable，不过问个group的查询，90％的人答不上来，因为平时用的少
: 。更不用说问having，having和where的区别之类的。这种题目问fresh grad没什么意思
|
s*******d
发帖数: 4135 | 17 inner join不行吧，得用left join，因为要求每个customer，可能有些customer没有
place any order
【在 v***n 的大作中提到】
: 你没看懂 人家公司要看的是一个inner join
|
v***n
发帖数: 5085 | 18 huh... no order customer?... how could that happen?
【在 s*******d 的大作中提到】
: inner join不行吧，得用left join，因为要求每个customer，可能有些customer没有
: place any order
|
s*******d
发帖数: 4135 | 19 这个不是很正常，只注册了个custmoer的账号，还没有买东西呢。
【在 v***n 的大作中提到】
: huh... no order customer?... how could that happen?
|
L********n
发帖数: 930 | 20 Agree, use left join, not inner join
【在 s*******d 的大作中提到】
: 这个不是很正常，只注册了个custmoer的账号，还没有买东西呢。
|
v***n
发帖数: 5085 | 21 To clarify, I think it should be called "left/right outer join"
【在 L********n 的大作中提到】
: Agree, use left join, not inner join
|
n********5
发帖数: 323 | 22 the table design is same as LZ.
single query.
select c.id, count(*) from customers c left join orders o on c.id = o.c_id
group by o.c_id
i think something like that. |
