表a:
aid name age sex
---------- ---------- ------ ----
1 hua 22 m
2 wang 23 m
3 sun 23 m
4 abc 23 m
5 bbb 23 m
6 ccc 23 m (所影响的行数为 6 行)
表b:
bid bname fzr address
-------- ------------ -------- --------------------------------------------------
01 china man ppp
02 japan man ppp
03 koren man ppp
04 usa man ppp(所影响的行数为 4 行)
表c:
aid bid date
---------- -------- ------------------------------------------------------
1 01 1999-01-01 00:00:00.000
1 02 1999-01-01 00:00:00.000
1 03 1999-01-01 00:00:00.000
1 04 1999-01-01 00:00:00.000
2 01 1999-01-01 00:00:00.000
3 02 1999-01-01 00:00:00.000
4 03 1999-01-01 00:00:00.000
5 04 1999-01-01 00:00:00.000(所影响的行数为 8 行)现在要求得到a表中对应于所有的bid的aid 就是1
aid name age sex
---------- ---------- ------ ----
1 hua 22 m
2 wang 23 m
3 sun 23 m
4 abc 23 m
5 bbb 23 m
6 ccc 23 m (所影响的行数为 6 行)
表b:
bid bname fzr address
-------- ------------ -------- --------------------------------------------------
01 china man ppp
02 japan man ppp
03 koren man ppp
04 usa man ppp(所影响的行数为 4 行)
表c:
aid bid date
---------- -------- ------------------------------------------------------
1 01 1999-01-01 00:00:00.000
1 02 1999-01-01 00:00:00.000
1 03 1999-01-01 00:00:00.000
1 04 1999-01-01 00:00:00.000
2 01 1999-01-01 00:00:00.000
3 02 1999-01-01 00:00:00.000
4 03 1999-01-01 00:00:00.000
5 04 1999-01-01 00:00:00.000(所影响的行数为 8 行)现在要求得到a表中对应于所有的bid的aid 就是1
解决方案 »
- 克隆一个表?
- 行变字符串
- 拼接问题
- sql初级问题求助
- 字符串 问题(求一函数)
- 如何运行sql 无人值守安装
- VS.NET2003连接SQL2005,用SqlDataAdapter生成查询语句成功,而生成更新、删除语句失败。我们现在被迫另外建立了一个SQL2000的数据库,专
- 高分求助!我在SQL数据库端做了一个函数,返回一个table,现在想得到这个table中的某些行列该怎么做?谢谢
- 如何在第二次扫描表的时候从第一次扫描的结束点开始,而不需要从表的第一条记录重新开始扫?(在线等结贴!!)
- 求一个高级语言操作数据库的教程
- access 查询升级到sql视图 的问题——完整版
- 如何判断一个用户函数已经在数据库中
---------
这句话通吗?
1对应了所有b表中的bid
2,3,4,5 只对应了1个 而 6没有
这样说不知道能看懂不?
if object_id('a') is not null drop table a
go
create table a([aid] int,[name] varchar(10),[age] int,[sex] varchar(10))
insert a select 1,'hua',22,'m'
union all select 2,'wang',23,'m'
union all select 3,'sun',23,'m'
union all select 4,'abc',23,'m'
union all select 5,'bbb',23,'m'
union all select 6,'ccc',23,'m'
go
if object_id('b') is not null drop table b
go
create table b([bid] varchar(10),[bname] varchar(10),[fzr] varchar(10),[address] varchar(10))
insert b select '01','china','man','ppp'
union all select '02','japan','man','ppp'
union all select '03','koren','man','ppp'
union all select '04','usa','man','ppp'
go
if object_id('c') is not null drop table c
go
create table c([aid] int,[bid] varchar(10),[date] varchar(10))
insert c select 1,'01','1999-01-01'
union all select 1,'02','1999-01-01'
union all select 1,'03','1999-01-01'
union all select 1,'04','1999-01-01'
union all select 2,'01','1999-01-01'
union all select 3,'02','1999-01-01'
union all select 4,'03','1999-01-01'
union all select 5,'04','1999-01-01'
goselect a.aid
from c
join a on c.aid=a.aid
join b on c.bid=b.bid
group by a.aid
having count(a.aid)
=(select count(1) from b)
/*
aid
-----------
1(1 行受影响)
*/
from (select id,cnt=count(distinct bid) from c group by id) K
where cnt=(select count(*) from b)
go
create table a([aid] int,[name] varchar(10),[age] int,[sex] varchar(10))
insert a select 1,'hua',22,'m'
union all select 2,'wang',23,'m'
union all select 3,'sun',23,'m'
union all select 4,'abc',23,'m'
union all select 5,'bbb',23,'m'
union all select 6,'ccc',23,'m'
go
if object_id('b') is not null drop table b
go
create table b([bid] varchar(10),[bname] varchar(10),[fzr] varchar(10),[address] varchar(10))
insert b select '01','china','man','ppp'
union all select '02','japan','man','ppp'
union all select '03','koren','man','ppp'
union all select '04','usa','man','ppp'
go
if object_id('c') is not null drop table c
go
create table c([aid] int,[bid] varchar(10),[date] varchar(10))
insert c select 1,'01','1999-01-01'
union all select 1,'02','1999-01-01'
union all select 1,'03','1999-01-01'
union all select 1,'03','1999-01-01' --考虑重复的情况
union all select 1,'04','1999-01-01'
union all select 2,'01','1999-01-01'
union all select 2,'01','1999-01-01'
union all select 2,'01','1999-01-01'
union all select 3,'02','1999-01-01'
union all select 4,'03','1999-01-01'
union all select 5,'04','1999-01-01'
goselect aid
from (select distinct aid,bid from c) c
group by aid
having count(aid)
=(select count(1) from b)
/*
aid
-----------
1(1 行受影响)
*/考虑C中可能出现重复的情况。
b 表是国籍表
c 表 是加入表
要求得到加入了所有国籍的人员信息
if object_id('a') is not null drop table a
go
create table a([aid] int,[name] varchar(10),[age] int,[sex] varchar(10))
insert a select 1,'hua',22,'m'
union all select 2,'wang',23,'m'
union all select 3,'sun',23,'m'
union all select 4,'abc',23,'m'
union all select 5,'bbb',23,'m'
union all select 6,'ccc',23,'m'
go
if object_id('b') is not null drop table b
go
create table b([bid] varchar(10),[bname] varchar(10),[fzr] varchar(10),[address] varchar(10))
insert b select '01','china','man','ppp'
union all select '02','japan','man','ppp'
union all select '03','koren','man','ppp'
union all select '04','usa','man','ppp'
go
if object_id('c') is not null drop table c
go
create table c([aid] int,[bid] varchar(10),[date] varchar(10))
insert c select 1,'01','1999-01-01'
union all select 1,'02','1999-01-01'
union all select 1,'03','1999-01-01'
union all select 1,'03','1999-01-01' --考虑重复的情况
union all select 1,'04','1999-01-01'
union all select 2,'01','1999-01-01'
union all select 2,'01','1999-01-01'
union all select 2,'01','1999-01-01'
union all select 3,'02','1999-01-01'
union all select 4,'03','1999-01-01'
union all select 5,'04','1999-01-01'
goselect *
from a
where aid=(
select aid
from (select distinct aid,bid from c) c
group by aid
having count(aid)
=(select count(1) from b))/*
aid name age sex
----------- ---------- ----------- ----------
1 hua 22 m(1 行受影响)
*/
from a
where aid in(
select aid
from (select distinct aid,bid from c) c
group by aid
having count(aid)
=(select count(1) from b))/*
aid name age sex
----------- ---------- ----------- ----------
1 hua 22 m(1 行受影响)
*/修正一下,以此为准。