MYSQL多表联合查询 （4表）

Question

要查询
表A的ID,NUMBER,PRICE,ORDER_TIME,STATUS
表B的USER_ID,STARTIME,STOPTIME,
表C的CHANNEL_PAY
表D的COMPANY
其中表A的COMPANY_ID=表B的COMPANY_ID=表C的 COMPANY_ID=表D的UID
最后的形式是
ID,USER_ID,COMPANY,NUMBER,STARTIME,STOPTIME,PRICE,ORDER_TIME,.........等等
1, 5 ，某公司 ， 2 ， 2011 ， 2013 ， 500，2010，。。。。等等
select buy.id,buy.member_number,buy.price,buy.order_time,buy.status,userinfo_extend.user_id,userinfo_extend.starttime,userinfo_extend.overtime,pay_type.channel_pay,company_userinfo.company_name from buy left join userinfo_extend on buy.company_id=userinfo_extend.company_id left join pay_type on buy.company_id=pay_type.company_id left join company_userinfo on buy.company_id=company_userinfo.uid

呃 写好了。。

Answer 1

select * from 表1 inner join 表2 on 关联条件

select * from 表1 left outer join 表2 on 关联条件

select * from 表1 right outer join 表2 on 关联条件 

select * from 表1 cross join 表2 on 关联条件

MYSQL查询

查询平均成绩大于70分的同学的学号和平均成绩
SELECT s.id,AVG(sc.score) FROM student s,studentcourse sc WHERE s.id=sc.student_id GROUP BY s.id HAVING  AVG(sc.score)>70;

查询所有同学的学号、姓名、选课数、总成绩
SELECT id,NAME
FROM student
WHERE id NOT IN (SELECT student_id
FROM studentcourse
WHERE course_id IN (SELECT course.id
FROM teacher,course
WHERE teacher.id=course.teacher_id
AND teacher.name=’关羽’));



查询学生信息和平均成绩
SELECT s.id,s.name,s.city,s.age, c.name,sc.score,t.name
FROM student s,studentcourse sc,course c,teacher t
WHERE s.id=sc.student_id AND c.id=sc.course_id AND c.teacher_id=t.id GROUP BY s.id; 

Answer 2

SELECT
A.ID,
A.NUMBER,
A.PRICE,
A.ORDER_TIME,
B.USER_ID,
B.STARTIME,
B.STOPTIME,
C.CHANNEL_PAY,
D.COMPANY
FROM
D表 D
LEFT JOIN A表 A ON A.COMPANY_ID = D.UID
LEFT JOIN B表 B ON B.COMPANY_ID = D.UID
LEFT JOIN C表 C ON C.COMPANY_ID = D.UID

有条件的再加行：
WHERE ..........
排序(如A表的ID由大到小排):
ORDER BY A.ID DESC本回答被提问者采纳

Answer 3

select a.id, a.number, a.price, a.order_time, a.status, b.user_id, b.startime, b.stoptime, c.channel_pay, d.company from a, b, c, d where a.company_id = b.company_id and a.company_id = c.company_id and a.company_id= d.uid
你给的信息也太少了，表结构也不知道，除了company_id需不需要别的字段关联，否则怎么确定在各表中的唯一值？追问

不需要别的字段关联
COMPANY_ID和UID在各表中的值是唯一的

追答

那这样就应该可以了，报出的是什么错误？还是你现在已经弄好了？

Answer 4

select 要显示的若干列（格式是：表别名.列名，例如：a.ID）
from a a,
b b,
c c,
d d
where a.COMPANY_ID = b.COMPANY_ID
and a.COMPANY_ID = c.COMPANY_ID
and a.COMPANY_ID = d.UID;