再发一贴,加上另一张贴共计60分,ab两表中与a.mid字段相同的值相同值的个数?

ccssme 2003-10-17 10:16:05
a.mid b.mid num
1 1 5
2 1 3
1 1 5
2 2 3
以A表为准,找出a.mid相同的值在a与b表中的个数(答案如num所示)
http://expert.csdn.net/Expert/topic/2363/2363566.xml?temp=.9161035
...全文
42 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
ccssme 2003-10-20
  • 打赏
  • 举报
回复
按yujohny(踏网无痕)的思路,我做出来了,谢谢各位
ccssme 2003-10-20
  • 打赏
  • 举报
回复
按yujohny(踏网无痕)的思路,我做出来了,谢谢各位
aierong 2003-10-17
  • 打赏
  • 举报
回复
楼主你到底要什么结果


create table a(mid int)
insert into a select 1
insert into a select 2
insert into a select 1
insert into a select 2

create table b(mid int)
insert into b select 1
insert into b select 1
insert into b select 1
insert into b select 2




select a.mid,b.sums num
from a join (
select a.mid,a.sums+b.sums sums
from (
select mid,count(*) sums
from a
group by mid) a join (
select mid,count(*) sums
from b
group by mid) b on a.mid=b.mid
) b
on a.mid=b.mid
group by a.mid,b.sums


mid num
----------- -----------
1 5
2 3

(所影响的行数为 2 行)

ccssme 2003-10-17
  • 打赏
  • 举报
回复
我试过了还不行,我要这种形式的:
(.....)AS Num
这样要求最后一个select 只能返回一个,不能返回两个值
Dennis618 2003-10-17
  • 打赏
  • 举报
回复
declare @a table(mid int)
declare @b table(mid int)
insert into @a values(1)
insert into @a values(2)
insert into @a values(1)
insert into @a values(2)
insert into @b values(1)
insert into @b values(1)
insert into @b values(1)
insert into @b values(2)

select m.mid,(select count(*) from @a a where a.mid=m.mid)+(select count(*) from @b b where b.mid=m.mid) as num from @a M

結果:

mid num
----------- -----------
1 5
2 3
1 5
2 3
(影響 4 個資料列)
yujohny 2003-10-17
  • 打赏
  • 举报
回复
select a.mid,count(*) num from a left join (select mid from a union all select mid from b) T on a.mid=T.mid
group by a.mid
aierong 2003-10-17
  • 打赏
  • 举报
回复
select a.mid,b.sums num
from a join (
select a.mid,a.sums+b.sums sums
from (
select mid,count(*) sums
from a
group by mid) a join (
select mid,count(*) sums
from b
group by mid) b on a.mid=b.mid
) b
on a.mid=b.mid
aierong 2003-10-17
  • 打赏
  • 举报
回复

create table a(mid int)
insert into a select 1
insert into a select 2
insert into a select 1
insert into a select 2

create table b(mid int)
insert into b select 1
insert into b select 1
insert into b select 1
insert into b select 2


select a.mid,b.sums
from a join (
select a.mid,a.sums+b.sums sums
from (
select mid,count(*) sums
from a
group by mid) a join (
select mid,count(*) sums
from b
group by mid) b on a.mid=b.mid
) b
on a.mid=b.mid


mid sums
----------- -----------
1 5
2 3
1 5
2 3

(所影响的行数为 4 行)
ccssme 2003-10-17
  • 打赏
  • 举报
回复
表A结构:aID mid ...
表b结构:bID mid ...
yoki 2003-10-17
  • 打赏
  • 举报
回复
select sum(1) as num from A
group by a.mid
having count(*)>1
zjcxc 元老 2003-10-17
  • 打赏
  • 举报
回复
--测试
declare @a table(mid int)
insert into @a select 1
insert into @a select 2
insert into @a select 1
insert into @a select 2

declare @b table (mid int)
insert into @b select 1
insert into @b select 1
insert into @b select 1
insert into @b select 2

--按表A取
select aa.mid,num=(select sum(1) from @a where mid=aa.mid)+(select sum(1) from @b where mid=aa.mid) from @a aa

--按表B取
select aa.mid,num=(select sum(1) from @a where mid=aa.mid)+(select sum(1) from @b where mid=aa.mid) from @b aa

/*--结果

--按表A取
mid num
----------- -----------
1 5
2 3
1 5
2 3

(所影响的行数为 4 行)

--按表B取
mid num
----------- -----------
1 5
1 5
1 5
2 3

(所影响的行数为 4 行)
--*/
zjcxc 元老 2003-10-17
  • 打赏
  • 举报
回复
--按表A取
select aa.mid,num=(select sum(1) from a where mid=aa.mid)+(select sum(1) from b where mid=aa.mid) from a aa

--按表B取
select aa.mid,num=(select sum(1) from a where mid=aa.mid)+(select sum(1) from b where mid=aa.mid) from b aa
zjcxc 元老 2003-10-17
  • 打赏
  • 举报
回复
select b.mid,num=(select sum(1) from a where mid=aa.mid)+(select sum(1) from b where mid=aa.mid) from b aa
yoki 2003-10-17
  • 打赏
  • 举报
回复
select b.sums as Num
from a join (
select a.mid,a.sums+b.sums sums
from (
select mid,count(*) sums
from a
group by mid) a join (
select mid,count(*) sums
from b
group by mid) b on a.mid=b.mid
) b
on a.mid=b.mid
group by a.mid,b.sums
ccssme 2003-10-17
  • 打赏
  • 举报
回复
要求最后一个select 只能返回一个,不能返回两个值
select a.mid,b.sums num这就是两个值了,我加在视图中怎么AS Num??
我要求把取得的值放在Num中,把Num当做一个字段

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧