CREATE TABLE USERNAME ( NAME_ID INT, NAME VARCHAR(10) ) CREATE TABLE SOLD ( SOLD_ID INT, NAME_ID INT, NAME_WAY VARCHAR(100), date VARCHAR(36), CONSUME INT) INSERT INTO USERNAME VALUES ('1','JAY'), ('2','MIKE'), ('3','KOBE'), ('4','BRANT') INSERT INTO SOLD VALUES ('1001','1','现金','2010-10-12','100'), ('1002','2','现金','2010-09-23','221'), ('1003','3','刷卡','2010-08-23','213'), ('1004','4','现金','2010-12-23','234'), ('1005','3','现金','2010-09-12','232'), ('1006','2','现金','2010-12-23','231')SELECT USERNAME.NAME AS NAME,MAX(SOLD.date) AS 最近一次就餐时间,count(SOLD.SOLD_ID) AS 消费次数,AVG(SOLD.CONSUME) AS 平均消费 FROM USERNAME INNER JOIN SOLD ON USERNAME.NAME_ID=SOLD.NAME_ID GROUP BY NAME
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
(
NAME_ID INT,
NAME VARCHAR(10)
)
CREATE TABLE SOLD
(
SOLD_ID INT,
NAME_ID INT,
NAME_WAY VARCHAR(100),
date VARCHAR(36),
CONSUME INT)
INSERT INTO USERNAME VALUES
('1','JAY'),
('2','MIKE'),
('3','KOBE'),
('4','BRANT')
INSERT INTO SOLD VALUES
('1001','1','现金','2010-10-12','100'),
('1002','2','现金','2010-09-23','221'),
('1003','3','刷卡','2010-08-23','213'),
('1004','4','现金','2010-12-23','234'),
('1005','3','现金','2010-09-12','232'),
('1006','2','现金','2010-12-23','231')SELECT USERNAME.NAME AS NAME,MAX(SOLD.date) AS
最近一次就餐时间,count(SOLD.SOLD_ID) AS 消费次数,AVG(SOLD.CONSUME) AS 平均消费
FROM USERNAME
INNER JOIN SOLD
ON USERNAME.NAME_ID=SOLD.NAME_ID
GROUP BY NAME