select name, user1=(select name from [user] where id=a.userid1), user2=(select name from [user] where id=a.userid2), msg1=(select message from [msg] where id=a.msgid1), msg2=(select message from [msg] where id=a.msgid2), from class a
select name, (select name from user where id=a.userid1) user1, (select name from user where id=a.userid2) user2, (select message from msg where id=a.msgid1) msg1, (select message from msg where id=a.msgid2) msg2, from class
再请教下:当数据量大的时候 这样的表设计和SQL查询会不会对性能有影响?
表不合理,所以才会出现低效率的sql语句,如果A表为: name userid msgid效率就会明显提高了。
select a.name class_name ,b1.name user1_name ,b2.name user2_name ,c1.message message1 ,c2.message message2 from class a ,user b1 ,user b2 ,msg c1 ,msg c2 where a.userid1 = b1.id and a.userid2 = b2.id and a.msgid1 = c1.id and a.msgid2 = c2.id
表A:class id name 表B:user id name 表C:msg id message 增加: 表AB id A.ID B.ID表AC id A.ID C.ID是否这样改?
select name1 = (select name from user where id = t.userid1), name2 = (select name from user where id = t.userid2), name3 = (select name from msg where id = t.userid1), name4 = (select name from msg where id = t.userid2) from class t貌似这样设计没什么不妥.
select name,
user1=(select name from [user] where id=a.userid1),
user2=(select name from [user] where id=a.userid2),
msg1=(select message from [msg] where id=a.msgid1),
msg2=(select message from [msg] where id=a.msgid2),
from class a
(select name from user where id=a.userid1) user1,
(select name from user where id=a.userid2) user2,
(select message from msg where id=a.msgid1) msg1,
(select message from msg where id=a.msgid2) msg2,
from class
name
userid
msgid效率就会明显提高了。
select a.name class_name
,b1.name user1_name
,b2.name user2_name
,c1.message message1
,c2.message message2
from class a
,user b1
,user b2
,msg c1
,msg c2
where a.userid1 = b1.id
and a.userid2 = b2.id
and a.msgid1 = c1.id
and a.msgid2 = c2.id
针对表A的数据内 有多个相关user的信息需要存放,因此会有userid1和userid2等字段,msgid也有多个信息,而不是简单的重复。
表A:class
id
name 表B:user
id
name 表C:msg
id
message 增加:
表AB
id
A.ID
B.ID表AC
id
A.ID
C.ID是否这样改?
select
name1 = (select name from user where id = t.userid1),
name2 = (select name from user where id = t.userid2),
name3 = (select name from msg where id = t.userid1),
name4 = (select name from msg where id = t.userid2)
from class t貌似这样设计没什么不妥.