--1. select a.b,count(*) from A a left join B b on a.a=b.a and b.c='1' group by a.a,a.b --2.(只考虑一夫一妻) select a.b,count(*) from A a , B b where a.a=b.a and b.c='0' and a.d+(select d from A where a=b.b)>50
--1. select a.b,count(*) from A a left join B b on a.a=b.a and b.c='1' group by a.a,a.b --2.(只考虑一夫一妻) select a.b from A a , B b where a.a=b.a and b.c='0' and a.d+(select d from A where a=b.b)>50
to:rivery(river)第一个好像不对。有些人可以没有子女。
第一个 select a.b,count(*) as d from A a , B b where a.a=b.a and b.c='1' group by a.a,a.b union select a.b, 0 as d from A a , B b where a.a<>b.a
declare @tb1 table (a int,b varchar(20),c char(1),d int) insert into @tb1 select 1,'张三','0',20 union all select 2,'张小三','0',1 union all select 3,'李四','1',25 union all select 4,'李小四','1',3 union all select 5,'王五','0',5 union all select 6,'王老五','1',30 union all select 7,'赵六','0',35 union all select 8,'张小三2','1',2 declare @tb2 table (a int,b int,c char(1)) insert into @tb2 select 1,2,'1' union all select 1,8,'1' union all select 3,4,'1' union all select 6,5,'1'select a.b,count(b.b)from @tb1 a left join @tb2 b on a.a=b.a group by a.b/* 人员编号 孩子数 -------------------- ----------- 李四 1 李小四 0 王老五 1 王五 0 张三 2 张小三 0 张小三2 0 赵六 0(所影响的行数为 8 行) */
同意楼上的。 to:funsuzhou 语句不对。假如表2中有1,2,0 数据,语句就错了
(2) declare @tb1 table (a int,b varchar(20),c char(1),d int) insert into @tb1 select 1,'张三','0',20 union all select 2,'张小三','0',1 union all select 3,'李四','1',25 union all select 4,'李小四','1',3 union all select 5,'王五','0',5 union all select 6,'王老五','1',30 union all select 7,'赵六','0',35 union all select 8,'张小三2','1',2 declare @tb2 table (a int,b int,c char(1)) insert into @tb2 select 1,2,'1' union all select 1,8,'1' union all select 3,4,'1' union all select 6,5,'1' union all select 1,3,'0'select a.b from @tb1 a left join @tb2 b on a.a=b.a where b.c='0'/* b -------------------- 张三(所影响的行数为 1 行) */
再试 select a.b,count(*) as d from A a , B b where a.a=b.a and b.c='1' group by a.a,a.b union select a.b, 0 as d from A a where a.a not in(select a.a as d from A a , B b where a.a=b.a and b.c='1' group by a.a,a.b)
declare @tb1 table (a int,b varchar(20),c char(1),d int) insert into @tb1 select 1,'张三','0',20 union all select 2,'张小三','0',1 union all select 3,'李四','1',25 union all select 4,'李小四','1',3 union all select 5,'王五','0',5 union all select 6,'王老五','1',30 union all select 7,'赵六','0',35 union all select 8,'张小三2','1',2 declare @tb2 table (a int,b int,c char(1)) insert into @tb2 select 1,2,'1' union all select 1,8,'1' union all select 3,4,'1' union all select 6,5,'1' union all select 1,3,'0'--(1) select 人员编号=a.b,孩子数=count(b.b)from @tb1 a left join @tb2 b on a.a=b.a and b.c='1' group by a.b--(2) select a.b from @tb1 a left join @tb2 b on a.a=b.a where b.c='0' --(1) /* 人员编号 孩子数 -------------------- ----------- 李四 1 李小四 0 王老五 1 王五 0 张三 2 张小三 0 张小三2 0 赵六 0(所影响的行数为 8 行) /*--(2) /* b -------------------- 张三(所影响的行数为 1 行) */
问题二; select B.a from A A1,A A2,B where B.C='0' and B.a=A1.a and B.b=A2.a and A1.d+A2.d >50
楼主不如这样设计表:人员编号 int 姓名 varchar(20) 性别 char(1) 年龄 int 父编号 int 母编号 int
--(2) select a.b from @tb1 a left join @tb2 b on a.a=b.a where b.c='0' and b.a+b.b>50
第1题我支持rivery(river) 的答案, --1. select a.b,count(*) from A a left join B b on a.a=b.a and b.c='1' group by a.a,a.b 请注意他连接时用的是 left join----on,这样哪怕表A中没孩子的一样能列出
select a.b,count(*)
from A a left join B b on a.a=b.a and b.c='1'
group by a.a,a.b
--2.(只考虑一夫一妻)
select a.b,count(*)
from A a , B b
where a.a=b.a and b.c='0'
and a.d+(select d from A where a=b.b)>50
select a.b,count(*)
from A a left join B b on a.a=b.a and b.c='1'
group by a.a,a.b
--2.(只考虑一夫一妻)
select a.b
from A a , B b
where a.a=b.a and b.c='0'
and a.d+(select d from A where a=b.b)>50
select a.b,count(*) as d
from A a , B b where a.a=b.a and b.c='1'
group by a.a,a.b
union
select a.b, 0 as d
from A a , B b where a.a<>b.a
insert into @tb1
select 1,'张三','0',20 union all
select 2,'张小三','0',1 union all
select 3,'李四','1',25 union all
select 4,'李小四','1',3 union all
select 5,'王五','0',5 union all
select 6,'王老五','1',30 union all
select 7,'赵六','0',35 union all
select 8,'张小三2','1',2
declare @tb2 table (a int,b int,c char(1))
insert into @tb2
select 1,2,'1' union all
select 1,8,'1' union all
select 3,4,'1' union all
select 6,5,'1'select a.b,count(b.b)from @tb1 a left join @tb2 b on a.a=b.a
group by a.b/*
人员编号 孩子数
-------------------- -----------
李四 1
李小四 0
王老五 1
王五 0
张三 2
张小三 0
张小三2 0
赵六 0(所影响的行数为 8 行)
*/
to:funsuzhou
语句不对。假如表2中有1,2,0 数据,语句就错了
declare @tb1 table (a int,b varchar(20),c char(1),d int)
insert into @tb1
select 1,'张三','0',20 union all
select 2,'张小三','0',1 union all
select 3,'李四','1',25 union all
select 4,'李小四','1',3 union all
select 5,'王五','0',5 union all
select 6,'王老五','1',30 union all
select 7,'赵六','0',35 union all
select 8,'张小三2','1',2
declare @tb2 table (a int,b int,c char(1))
insert into @tb2
select 1,2,'1' union all
select 1,8,'1' union all
select 3,4,'1' union all
select 6,5,'1' union all
select 1,3,'0'select a.b from @tb1 a left join @tb2 b on a.a=b.a where b.c='0'/*
b
--------------------
张三(所影响的行数为 1 行)
*/
select a.b,count(*) as d
from A a , B b where a.a=b.a and b.c='1'
group by a.a,a.b
union
select a.b, 0 as d
from A a where a.a not in(select a.a as d
from A a , B b where a.a=b.a and b.c='1'
group by a.a,a.b)
insert into @tb1
select 1,'张三','0',20 union all
select 2,'张小三','0',1 union all
select 3,'李四','1',25 union all
select 4,'李小四','1',3 union all
select 5,'王五','0',5 union all
select 6,'王老五','1',30 union all
select 7,'赵六','0',35 union all
select 8,'张小三2','1',2
declare @tb2 table (a int,b int,c char(1))
insert into @tb2
select 1,2,'1' union all
select 1,8,'1' union all
select 3,4,'1' union all
select 6,5,'1' union all
select 1,3,'0'--(1)
select 人员编号=a.b,孩子数=count(b.b)from @tb1 a left join @tb2 b on a.a=b.a and b.c='1'
group by a.b--(2)
select a.b from @tb1 a left join @tb2 b on a.a=b.a where b.c='0'
--(1)
/*
人员编号 孩子数
-------------------- -----------
李四 1
李小四 0
王老五 1
王五 0
张三 2
张小三 0
张小三2 0
赵六 0(所影响的行数为 8 行)
/*--(2)
/*
b
--------------------
张三(所影响的行数为 1 行)
*/
select B.a from A A1,A A2,B where B.C='0'
and B.a=A1.a and B.b=A2.a and A1.d+A2.d >50
姓名 varchar(20)
性别 char(1)
年龄 int
父编号 int
母编号 int
select a.b from @tb1 a left join @tb2 b on a.a=b.a where b.c='0' and b.a+b.b>50
这个不用考虑。第一题的写法好像不对
--1.
select a.b,count(*)
from A a left join B b on a.a=b.a and b.c='1'
group by a.a,a.b
请注意他连接时用的是 left join----on,这样哪怕表A中没孩子的一样能列出