两个表
A表 ID PARTY_NAME PARTY_TOPIC
B表 ID 参加的人a
ID 参加的人b
ID 参加的人c.....
表的ID是对应的,A表的一条记录对应B表多条或者0条
显示结果:
party_name party_topic 参加的总人数Select A.ID, A.PARTY_NAME, A.PARTY_TOPIC, nvl(G.Tol, 0) FROM A
LEFT JOIN (Select ID,count([参加的人数]) AS Tol FROM B Group By ID) AS G
ON A.ID=G.ID现在我想增加一个条件 根据B表中参加的人的user_name 来查询出 要显示的结果 ,该怎么写?谢谢~
A表 ID PARTY_NAME PARTY_TOPIC
B表 ID 参加的人a
ID 参加的人b
ID 参加的人c.....
表的ID是对应的,A表的一条记录对应B表多条或者0条
显示结果:
party_name party_topic 参加的总人数Select A.ID, A.PARTY_NAME, A.PARTY_TOPIC, nvl(G.Tol, 0) FROM A
LEFT JOIN (Select ID,count([参加的人数]) AS Tol FROM B Group By ID) AS G
ON A.ID=G.ID现在我想增加一个条件 根据B表中参加的人的user_name 来查询出 要显示的结果 ,该怎么写?谢谢~
解决方案 »
- java和数据库连接的那段知识有木有视频教程?
- oracle中这几个表有什么关系?
- 建了一个PUBLIC的DBLINK,假如说有20个用户,我想让其中的一个不能使用DBLINK,怎么办?
- 创建数据库链接时遇到的问题,拜求解答!
- 大家好,我想问一个两个oracle数据库通讯的问题
- pl/sql 有无 bool-excption?a:b;这样的三元运算符?
- 装了oracle client端的电脑怎样连到装了oracle database的电脑??
- ★★在dbastudio中能查看到结果,但是为什么在SqlPlus中在同一个表中不能查询到结果,提示信息是表或试图不存在★★
- 初级问题 up有分 关注有分
- 关于建索引的问题
- 菜鸟求助,月计划拆分到每一天
- pro*c中对于sql_context,sqlda,sqlca作为函数参数出错——急急
LEFT JOIN (Select ID,count([参加的人数]) AS Tol FROM B where username='' --增加条件 Group By ID) AS G
ON A.ID=G.ID
user_name就是参加的人?
LEFT JOIN (Select ID,count([参加的人数]) AS Tol FROM B Group By ID) AS G
ON A.ID=G.ID
以上sql得出的结果为
A.ID, A.PARTY_NAME, A.PARTY_TOPIC, nvl(G.Tol, 0)
1 饮食 饮食健康很重要 3
2 运动 生命在于运动 2
3 读书 书是人类最好的朋友 2
A表数据
A.ID, A.PARTY_NAME, A.PARTY_TOPIC
1 饮食 饮食健康很重要
2 运动 生命在于运动
3 读书 书是人类最好的朋友
B表数据
ID user_name
1 a
1 b
1 c
2 a
2 d
3 e
3 f
我想根据user_name a 查询出a参加的party 结果A.ID, A.PARTY_NAME, A.PARTY_TOPIC, nvl(G.Tol, 0)
1 饮食 饮食健康很重要 3
2 运动 生命在于运动 2
谢谢!
Select A.ID, A.PARTY_NAME, A.PARTY_TOPIC, nvl(G.Tol, 0)
FROM A LEFT JOIN
(Select ID,count([参加的人数]) AS Tol
FROM B B1
where exists (select 1 from B B2 where B2.username='name' and B1.ID = B2.ID)
) AS G
ON A.ID=G.ID
FROM B B1
返回的结果是固定的,是这个ID 对应的总人数
我写了一个
Select A.ID, A.PARTY_NAME, A.PARTY_TOPIC, nvl(G.Tol, 0)
FROM (select * from A where vote_id = (select vote_id from B where user_name='a'))A LEFT JOIN
(Select ID,count([参加的人数]) AS Tol
FROM B B1) AS G
ON A.ID=G.ID
这样是不对的,因为(select * from A where vote_id = (select vote_id from B where user_name='a'))A 返回的是多列
+1Select A.ID, A.PARTY_NAME, A.PARTY_TOPIC, nvl(G.Tol, 0) FROM A
LEFT JOIN (Select B2.ID,B2.count([参加的人数]) AS Tol FROM B AS B1
INNER JOIN B AS B2
ON B1.ID=B2.ID WHERE B1.user_name='a'
Group By B2.ID) AS G
ON A.ID=G.ID思路基本就是这样
顺着9#的语句看思路,一目了然的
包括指定人员的ID,该ID对应的所有人员
user_name是个条件,没有它的时候把所有ID都显示出来,有它的时候只显示对应的ID,还求指点如何是好。。
实际上就是那样的,
统计所有时,where B1.ID = B2.ID
指定名字时,在where条件后面再追加个条件就好了and B2.username='name'
A的结果
A.ID, A.PARTY_NAME
1 饮食
2 运动
3 读书
G的结果
ID TOL
1 3
2 2
left join后得出的还是A.ID, A.PARTY_NAME Tol
1 饮食 3
2 运动 2
3 读书 0
A的结果
A.ID, A.PARTY_NAME
1 饮食
2 运动
3 读书
G的结果
ID TOL
1 3
2 2
left join后得出的还是A.ID, A.PARTY_NAME Tol
1 饮食 3
2 运动 2
3 读书 0
with A as
(
select 1 id, '饮食' PARTY_NAME, '饮食健康很重要' PARTY_TOPIC from dual
union all
select 2, '运动', '生命在于运动' from dual
union all
select 3, '读书', '书是人类最好的朋友' from dual
)
,
--with
b as
(
select 1 ID, 'a' user_name from dual
union all
select 1, 'b' user_name from dual
union all
select 1, 'c' user_name from dual
union all
select 2, 'a' user_name from dual
union all
select 2, 'd' user_name from dual
union all
select 3, 'e' user_name from dual
union all
select 3, 'f' user_name from dual
)
select a.id,a.PARTY_NAME,a.PARTY_TOPIC,c.rn from a,
(
select b.id,b.rn,b.user_name
from
(
select id,count(user_name)over(partition by id order by id) rn ,user_name from b
) b
where user_name='a'
)c
where a.id(+)=c.id--result:1 饮食 饮食健康很重要 3
2 运动 生命在于运动 2
Select A.ID, A.PARTY_NAME, A.PARTY_TOPIC, nvl(G.Tol, 0) FROM A
LEFT JOIN (Select ID,count([参加的人数]) AS Tol FROM B where username='' --增加条件 Group By ID) AS G
ON A.ID=G.ID
INNER JOIN B ON A.ID = B.ID AND B.NAME = '名字'
Select A.ID, A.PARTY_NAME, A.PARTY_TOPIC, nvl(G.Tol, 0) FROM A
LEFT JOIN (Select ID,count([参加的人数]) AS Tol FROM B where username='' --增加条件 Group By ID) AS G
ON A.ID=G.ID
INNER JOIN B ON A.ID = B.ID AND B.NAME = '名字'
(Select ID,count([参加的人数]) AS Tol
FROM B B1
where exists (select 1 from B B2 where B2.username='name' and B1.ID = B2.ID)
)也需要,因为count()得出的参加的总人数
LEFT JOIN (Select VOTE_ID,count(user_name) AS Tol FROM ierp_VOTE_attendee B1 where exists (select 1 from ierp_VOTE_attendee B2 where B2.user_name='a' and B1.VOTE_ID = B2.VOTE_ID)
Group By VOTE_ID) G
ON A.VOTE_ID=G.VOTE_ID
INNER JOIN ierp_VOTE_attendee B ON A.VOTE_ID = B.VOTE_ID AND B.USER_NAME = 'a'多谢各位相助
---------- -------------------------------- --------------------------------
1 dog love dog
2 cat love cat
3 fox love foxSQL> select * from tabB; ID NAME
---------- --------------------------------
1 aaaa
2 aaab
3 aaac
1 aaad
1 aaae
1 aaaf
2 aaba
2 aabc已选择8行。SQL> edit
已写入 file afiedt.buf 1* select a.PARTY_NAME,a.PARTY_TOPIC,b.rs from tabA a,(select id,count(1) as rs from tabB group by id) b where a.id = b.id
SQL> /PARTY_NAME PARTY_TOPIC RS
-------------------------------- -------------------------------- ----------
dog love dog 4
cat love cat 3
fox love fox 1SQL>
SQL> create table tabA(id number,PARTY_NAME varchar2(16),PARTY_TOPIC varchar2(16));表已创建。SQL> create table tabB(id number,name varchar2(16));表已创建。SQL> select * from tabA; ID PARTY_NAME PARTY_TOPIC
---------- -------------------------------- --------------------------------
1 dog love dog
2 cat love cat
3 fox love foxSQL> select * from tabB; ID NAME
---------- --------------------------------
1 aaaa
2 aaab
3 aaac
1 aaad
1 aaae
1 aaaf
2 aaba
2 aabc已选择8行。SQL> edit
已写入 file afiedt.buf 1* select a.PARTY_NAME,a.PARTY_TOPIC,b.rs from tabA a,(select id,count(1) as rs from tabB group by id) b where a.id = b.id
SQL> /PARTY_NAME PARTY_TOPIC RS
-------------------------------- -------------------------------- ----------
dog love dog 4
cat love cat 3
fox love fox 1SQL>
---------- -------------------------------- --------------------------------
1 dog love dog
2 cat love cat
3 fox love foxSQL> select * from tabB; ID NAME
---------- --------------------------------
1 aaaa
2 aaab
3 aaac
1 aaad
1 aaae
1 aaaf
2 aaba
2 aabc已选择8行。SQL> edit
已写入 file afiedt.buf 1* select a.PARTY_NAME,a.PARTY_TOPIC,b.rs from tabA a,(select id,count(1) as rs from tabB group by id) b where a.id = b.id
SQL> /PARTY_NAME PARTY_TOPIC RS
-------------------------------- -------------------------------- ----------
dog love dog 4
cat love cat 3
fox love fox 1SQL>