34,838
社区成员
发帖
与我相关
我的任务
分享求一简单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
请教这个连接查询应该怎么写！谢谢！
数据如下
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
请教这个连接查询应该怎么写！谢谢！
...全文
请发表友善的回复…
发表回复
pard521 2013-04-09
- 打赏
- 举报
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
奔四在望 2013-04-08
- 打赏
- 举报
--创建user表
CREATE TABLE #USER(u_id INT,u_name NVARCHAR(10))
--插入user表数据
INSERT INTO #USER
SELECT 1,'张三'
UNION ALL
SELECT 2,'李四'
UNION ALL
SELECT 3,'王五'
--创建product表
CREATE TABLE #product(p_id INT,p_userid INT,p_consumerId INT,p_businesses INT,p_count INT)
--插入product表数据
insert into #product
SELECT 1,1,2,3,100
UNION ALL
SELECT 2,1,3,2,200
SELECT a1.u_name 管理员,a3.u_name 商家,a2.u_name 消费用户,p.p_count
FROM #product p,#USER a1,#USER a2,#USER a3 WHERE
p.p_userid = a1.u_id
AND p.p_consumerId = a2.u_id
AND p.p_businesses = a3.u_id
szm341 2013-04-08
- 打赏
- 举报
这个就很简洁了，应该没有更好的解决方案了吧呵呵
山城忙碌人 2013-04-08
- 打赏
- 举报
谢谢你！！！！！能得到我要的数据！
山城忙碌人 2013-04-08
- 打赏
- 举报
连接三张表？请问有其它解决办法吗，谢谢你的答案！
szm341 2013-04-08
- 打赏
- 举报
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
MrYangkang 2013-04-08
- 打赏
- 举报
--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
坚_持 2013-04-08
- 打赏
- 举报
if object_id('userInfo','u') is not null
drop table userInfo
create table userInfo
(u_id int identity(1,1) primary key,u_name varchar(10))
insert into userInfo values('张三'),('李四'),('王五')
if object_id('product','u') is not null
drop table product
create table product
(
p_id int identity(1,1),
p_userId int references userInfo(u_id) ,
p_consumerId int references userInfo(u_id),
p_businesses int references userInfo(u_id),
p_count int
)
insert into product values(1,2,3,100),(1,3,2,200)
select * from userInfo
select * from product
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
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
