/*
user_id username phone
1 AA 13700011100
b表根据A表的phone取出相同的记录
msg_id msgContent phone infoCost
1 信息内容 13700011100 50
2 信息内容 13700011100 49.5
3 信息内容 13700011100 49
4 信息内容 13700011100 48.5
5 信息内容 13700011100 48
info_id price phone
1 0.5 13700011100
*/
select b1.msg_id,b1.msgContent,b1.phone,b1.infoCost from A A1 join C C1 on A1.phone=C1.phone join B b1
on b1.phone=C1.phone
user_id username phone
1 AA 13700011100
b表根据A表的phone取出相同的记录
msg_id msgContent phone infoCost
1 信息内容 13700011100 50
2 信息内容 13700011100 49.5
3 信息内容 13700011100 49
4 信息内容 13700011100 48.5
5 信息内容 13700011100 48
info_id price phone
1 0.5 13700011100
*/
select b1.msg_id,b1.msgContent,b1.phone,b1.infoCost from A A1 join C C1 on A1.phone=C1.phone join B b1
on b1.phone=C1.phone
from a join b on a.phone=b.phone
join c on a.phone=c.phone
from a,b, c where a.phone=b.phone and a.phone=c.phone
go
create table a(user_id int, username varchar(5), phone varchar(20))
insert a select 1 , 'AA', 13700011100
if object_id('b')is not null drop table b
go
create table b(msg_id int, msgContent nvarchar(10), phone varchar(20),infoCost float)
insert b select 1, N'信息内容', 13700011100 ,50
insert b select 2, N'信息内容' , 13700011100 , 49.5
insert b select 3, N'信息内容' ,13700011100 , 49
insert b select 4, N'信息内容' ,13700011100 , 48.5
insert b select 5, N'信息内容' ,13700011100, 48
if object_id('c')is not null drop table c
go
create table c(info_id int, price int, phone varchar(20))
insert c select 1 , 0.5 , 13700011100
select a.username,c.price,b.msgContent,b.phone,b.infoCost
from a,b, c where a.phone=b.phone and a.phone=c.phone
/*username price msgContent phone infoCost
-------- ----------- ---------- -------------------- ----------------------
AA 0 信息内容 13700011100 50
AA 0 信息内容 13700011100 49.5
AA 0 信息内容 13700011100 49
AA 0 信息内容 13700011100 48.5
AA 0 信息内容 13700011100 48*/
--> (让你望见影子的墙)生成测试数据,时间:2008-12-15
if not object_id('tb1') is null
drop table tb1
Go
Create table tb1([user_id] int,[username] nvarchar(2),[phone] varchar(11))
Insert tb1
select 1,N'AA',13700011100
Go
Select * from tb1--> (让你望见影子的墙)生成测试数据,时间:2008-12-15
if not object_id('tb2') is null
drop table tb2
Go
Create table tb2([msg_id] int,[msgContent] nvarchar(4),[phone] varchar(11),[infoCost] decimal(18,1))
Insert tb2
select 1,N'信息内容',13700011100,50 union all
select 2,N'信息内容',13700011100,49.5 union all
select 3,N'信息内容',13700011100,49 union all
select 4,N'信息内容',13700011100,48.5 union all
select 5,N'信息内容',13700011100,48
Go
Select * from tb2--> (让你望见影子的墙)生成测试数据,时间:2008-12-15
if not object_id('tb3') is null
drop table tb3
Go
Create table tb3([info_id] int,[price] decimal(18,1),[phone] varchar(11))
Insert tb3
select 1,0.5,13700011100
Go
Select * from tb3select tb1.username,tb3.price,tb2.msgContent ,tb2. phone ,tb2.infoCost
from tb1 right join tb2 on tb1.phone=tb2.phone
left join tb3 on tb2.phone=tb3.phoneAA .5 信息内容 13700011100 50.0
AA .5 信息内容 13700011100 49.5
AA .5 信息内容 13700011100 49.0
AA .5 信息内容 13700011100 48.5
AA .5 信息内容 13700011100 48.0
Insert tb1 select 1,N'AA',13700011100
goCreate table tb2([msg_id] int,[msgContent] nvarchar(4),[phone] varchar(11),[infoCost] decimal(18,1))
Insert tb2
select 1,N'信息内容',13700011100,50 union all
select 2,N'信息内容',13700011100,49.5 union all
select 3,N'信息内容',13700011100,49 union all
select 4,N'信息内容',13700011100,48.5 union all
select 5,N'信息内容',13700011100,48
GoCreate table tb3([info_id] int,[price] decimal(18,1),[phone] varchar(11))
Insert tb3 select 1,0.5,13700011100
Goselect a.username,c.price,b.msgcontent,a.phone,b.infocost from tb1 a
join tb2 b
on b.phone=a.phone
join tb3 c
on c.phone=a.phone
insert a select 1 , 'AA', 13700011100
create table b(msg_id int, msgContent nvarchar(10), phone varchar(20),infoCost float)
insert b select 1, N'信息内容', 13700011100 ,50
insert b select 2, N'信息内容' , 13700011100 , 49.5
insert b select 3, N'信息内容' ,13700011100 , 49
insert b select 4, N'信息内容' ,13700011100 , 48.5
insert b select 5, N'信息内容' ,13700011100, 48
create table c(info_id int, price decimal(18,1), phone varchar(20))
insert c select 1 , 0.5 , 13700011100 select a.username , c.price , a.phone , b.msgContent , b.infoCost
from a,b,c
where a.phone = b.phone and a.phone = c.phonedrop table a , b , c/*
username price phone msgContent infoCost
-------- -------------------- -------------------- ---------- -----------------------------------------------------
AA .5 13700011100 信息内容 50.0
AA .5 13700011100 信息内容 49.5
AA .5 13700011100 信息内容 49.0
AA .5 13700011100 信息内容 48.5
AA .5 13700011100 信息内容 48.0(所影响的行数为 5 行)
*/