求一简单SQL语句
user 表
数据如下
u_id,u_name
1 张三
2 李四
3 王五
product 表
p_id,
p_userId,--管理员ID
p_consumerId--消费者ID
p_businesses--商家ID
p_count 数量
数据如下
p_id p_userId p_consumerId p_businesses p_count
1 1 2 3 100
2 1 3 2 200
其中 p_userId p_consumerId p_businesses 是外建，对应user表u_id
我想得到如下结果：
管理员 商家 消费用户 数量
张三 王五 李四 100
张三 李四 王五 200
请教这个连接查询应该怎么写！谢谢！
select u.u_name 管理员,(select u_name from userInfo where u_id = p.p_consumerId) 商家,(select u_name from userInfo where u_id = p.p_businesses) 消费用户,p.p_count 数量
from userInfo as u,product as p where u.u_id = p.p_userId
[解决办法]
--user表
if exists (select * from sysobjects where name = 'user')
drop table [user]
go
create table [user]
(
u_id int primary key,
u_name nvarchar(10)
)
go
--consumer表
if exists (select * from sysobjects where name = 'consumer')
drop table consumer
go
create table consumer
(
consumer_id int primary key,
consumer_name nvarchar(10)
)
go
--businesses表
if exists (select * from sysobjects where name = 'businesses')
drop table businesses
go
create table businesses
(
businesses_id int primary key,
businesses_name nvarchar(10)
)
go
--product 表
if exists (select * from sysobjects where name = 'product')
drop table product
go
create table product
(
p_id int primary key,
p_userId int foreign key references [user](u_id),
p_consumerId int foreign key references consumer(consumer_id),
p_businessesid int foreign key references businesses(businesses_id),
p_count int
)
go
insert into [user] values (1,'张三')
insert into [user] values (2,'李四')
insert into [user] values (3,'王五')
insert into consumer values (1,'张三')
insert into consumer values (2,'李四')
insert into consumer values (3,'王五')
insert into businesses values (1,'张三')
insert into businesses values (2,'李四')
insert into businesses values (3,'王五')
insert into product values (1,1,2,3,100)
insert into product values (2,1,3,2,200)
go
select * from product
select * from [user]
select * from consumer
select * from businesses
--最终你要的SQL语句
select a.u_name,c.businesses_name,b.consumer_name,d.p_count
from product d,[user] a,consumer b,businesses c
where d.p_userId = a.u_id and
d.p_consumerId = b.consumer_id and
d.p_businessesid = c.businesses_id
[解决办法]
select a.u_name 管理员,b.u_name 消费者,c.u_name 商家,p.p_count 数量
from product p,[user] a,[user] b,[user] c
where p.p_userid=a.u_id and p.p_consumerId=b.u_id and p.p_businesses=c.u_id
