3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
在SQL server 2000 中怎么处理
有三个表
表1：
ID Name Counts...
1 AAA
2 BBB
....
表2：
ID Name ....
1 AAA
2 AAA
...
表3：
ID Name ....
1 BBB
2 BBB
...
现在想把表2和3中等于AAA和BBB的总数count放入到表1的Counts中，该怎么做？
换句话就是表1中的counts就是记录表2和表3的相应统计数。
表1：
ID Name Counts...
1 AAA
2 BBB
....
表2：
ID Name ....
1 AAA
2 AAA
...
表3：
ID Name ....
1 BBB
2 BBB
...
现在想把表2和3中等于AAA和BBB的总数count放入到表1的Counts中，该怎么做？
换句话就是表1中的counts就是记录表2和表3的相应统计数。
...全文
请发表友善的回复…
发表回复
wzx2275067 2003-12-25
SELECT ClassID,ClassName,
{case Belong '0' then SELECT COUNT(StuScore2002.ClassID) AS Amount
FROM Class INNER JOIN
StuScore2002 ON Class.ClassID = StuScore2002.ClassID
GROUP BY StuScore2002.ClassID, Class.ClassName
when '1' then SELECT COUNT(NewStu.ClassName) AS Amount
FROM Class INNER JOIN
NewStu ON Class.ClassName = NewStu.ClassName
GROUP BY NewStu.ClassName, Class.ClassName
end
}
as counts
FROM Class
这个有什么错误？
{case Belong '0' then SELECT COUNT(StuScore2002.ClassID) AS Amount
FROM Class INNER JOIN
StuScore2002 ON Class.ClassID = StuScore2002.ClassID
GROUP BY StuScore2002.ClassID, Class.ClassName
when '1' then SELECT COUNT(NewStu.ClassName) AS Amount
FROM Class INNER JOIN
NewStu ON Class.ClassName = NewStu.ClassName
GROUP BY NewStu.ClassName, Class.ClassName
end
}
as counts
FROM Class
这个有什么错误？
zjcxc--个人微信公共号同名 2003-12-20
--下面是测试
--测试数据
declare @表1 table(id int,name varchar(10),counts int)
insert into @表1(id,name)
select 1,'AAA'
union all select 2,'BBB'
declare @表2 table(id int,name varchar(10))
insert into @表2
select 1,'AAA'
union all select 2,'AAA'
declare @表3 table(id int,name varchar(10))
insert into @表3
select 1,'BBB'
union all select 2,'BBB'
--方法1.
update @表1 set counts=b.ab
from @表1 a join (
select name=isnull(a.name,b.name),ab=sum(isnull(aa,0)+isnull(bb,0))
from(
select name,aa=count(*) from @表2 group by name
) a full join(
select name,bb=count(*) from @表3 group by name
) b on a.name=b.name group by isnull(a.name,b.name)
) b on a.name=b.name
--显示结果
select * from @表1
--方法2.
update @表1 set Counts=(select count(*) from
(select name from @表2 union all select name from @表3) aa where name=a.name)
from @表1 a
--显示结果
select * from @表1
--方法3.
update @表1 set counts=b.ab
from @表1 a join (
select name,ab=count(*)
from(select name from @表2 union all select name from @表3) a
group by name
) b on a.name=b.name
--显示结果
select * from @表1
/*--测试结果
--方法1
id name counts
----------- ---------- -----------
1 AAA 2
2 BBB 2
（所影响的行数为 2 行）
--方法2
id name counts
----------- ---------- -----------
1 AAA 2
2 BBB 2
（所影响的行数为 2 行）
--方法3
id name counts
----------- ---------- -----------
1 AAA 2
2 BBB 2
（所影响的行数为 2 行）
--*/
--测试数据
declare @表1 table(id int,name varchar(10),counts int)
insert into @表1(id,name)
select 1,'AAA'
union all select 2,'BBB'
declare @表2 table(id int,name varchar(10))
insert into @表2
select 1,'AAA'
union all select 2,'AAA'
declare @表3 table(id int,name varchar(10))
insert into @表3
select 1,'BBB'
union all select 2,'BBB'
--方法1.
update @表1 set counts=b.ab
from @表1 a join (
select name=isnull(a.name,b.name),ab=sum(isnull(aa,0)+isnull(bb,0))
from(
select name,aa=count(*) from @表2 group by name
) a full join(
select name,bb=count(*) from @表3 group by name
) b on a.name=b.name group by isnull(a.name,b.name)
) b on a.name=b.name
--显示结果
select * from @表1
--方法2.
update @表1 set Counts=(select count(*) from
(select name from @表2 union all select name from @表3) aa where name=a.name)
from @表1 a
--显示结果
select * from @表1
--方法3.
update @表1 set counts=b.ab
from @表1 a join (
select name,ab=count(*)
from(select name from @表2 union all select name from @表3) a
group by name
) b on a.name=b.name
--显示结果
select * from @表1
/*--测试结果
--方法1
id name counts
----------- ---------- -----------
1 AAA 2
2 BBB 2
（所影响的行数为 2 行）
--方法2
id name counts
----------- ---------- -----------
1 AAA 2
2 BBB 2
（所影响的行数为 2 行）
--方法3
id name counts
----------- ---------- -----------
1 AAA 2
2 BBB 2
（所影响的行数为 2 行）
--*/
zjcxc--个人微信公共号同名 2003-12-20
--上面的有些地方写错了,改一下:
--方法1.
update 表1 set counts=b.ab
from 表1 a join (
select name=isnull(a.name,b.name),ab=sum(isnull(aa,0)+isnull(bb,0))
from(
select name,aa=count(*) from 表2 group by name
) a full join(
select name,bb=count(*) from 表3 group by name
) b on a.name=b.name group by isnull(a.name,b.name)
) b on a.name=b.name
--方法2.
update 表1 set Counts=(select count(*) from
(select name from 表2 union all select name from 表3) aa where name=a.name)
from 表1 a
--方法3.
update 表1 set counts=b.ab
from 表1 a join (
select name,ab=count(*)
from(select name from 表2 union all select name from 表3) a
group by name
) b on a.name=b.name
--方法1.
update 表1 set counts=b.ab
from 表1 a join (
select name=isnull(a.name,b.name),ab=sum(isnull(aa,0)+isnull(bb,0))
from(
select name,aa=count(*) from 表2 group by name
) a full join(
select name,bb=count(*) from 表3 group by name
) b on a.name=b.name group by isnull(a.name,b.name)
) b on a.name=b.name
--方法2.
update 表1 set Counts=(select count(*) from
(select name from 表2 union all select name from 表3) aa where name=a.name)
from 表1 a
--方法3.
update 表1 set counts=b.ab
from 表1 a join (
select name,ab=count(*)
from(select name from 表2 union all select name from 表3) a
group by name
) b on a.name=b.name
zjcxc--个人微信公共号同名 2003-12-20
--方法2.
update 表1 set Counts=(select count(*) from
(select name from 表2 union al select name from 表3) aa where name=a.name)
from 表1 a
--方法3.
update 表1 set counts=b.ab
from 表1 a join (
select name,ab=count(*)
from(select name from 表2 union all select name from 表3) a
group by name
) b on a.name=b.name
update 表1 set Counts=(select count(*) from
(select name from 表2 union al select name from 表3) aa where name=a.name)
from 表1 a
--方法3.
update 表1 set counts=b.ab
from 表1 a join (
select name,ab=count(*)
from(select name from 表2 union all select name from 表3) a
group by name
) b on a.name=b.name
zjcxc--个人微信公共号同名 2003-12-20
--方法1.
update 表1 set counts=b.ab
from 表1 a join (
select name=isnull(a.name,b.name,ab=sum(isnull(aa,0)+isnull(bb,0))
from(
select name,aa=count(*) from 表2 group by name
) a full join(
select name,bb=count(*) from 表3 group by name
) b on a.name=b.name
) b on a.name=b.name
update 表1 set counts=b.ab
from 表1 a join (
select name=isnull(a.name,b.name,ab=sum(isnull(aa,0)+isnull(bb,0))
from(
select name,aa=count(*) from 表2 group by name
) a full join(
select name,bb=count(*) from 表3 group by name
) b on a.name=b.name
) b on a.name=b.name
yoki 2003-12-20
update table1
set counts=(select count(*) from table2 where name='AAA')
where name='AAA'
update table1
set counts=(select count(*) from table2 where name='BBB')
where name='BBB'
set counts=(select count(*) from table2 where name='AAA')
where name='AAA'
update table1
set counts=(select count(*) from table2 where name='BBB')
where name='BBB'
victorycyz 2003-12-20
不过，表1其实是多余的。
victorycyz 2003-12-20
update table1 set counts=count(b.id) from table1 a join table2 b on a.name=b.name group by a.id
update table1 set counts=count(b.id) from table1 a join table3 b on a.name=b.name group by a.id
update table1 set counts=count(b.id) from table1 a join table3 b on a.name=b.name group by a.id
lakeofbeer 2003-12-19
定义两个变量
分别放2表和3表 AAAA 和 BBBB中 count 的和
然后 update
分别放2表和3表 AAAA 和 BBBB中 count 的和
然后 update
hdslah 2003-12-19
select id,name ,(select count(*) from (select name from 表2 union all select name from 表3) b where b.name =a.name) count from 表1 a
lakeofbeer 2003-12-19
txlicenhe(马可)
能否讲一下
case .....then ....when ...
的作用呢？
还没接触过。
查到了。。知道了哈。。
能否讲一下
case .....then ....when ...
的作用呢？
还没接触过。
查到了。。知道了哈。。
lakeofbeer 2003-12-19
txlicenhe(马可)
能否讲一下
case .....then ....when ...
的作用呢？
还没接触过。
能否讲一下
case .....then ....when ...
的作用呢？
还没接触过。
lakeofbeer 2003-12-19
对表1，表2做一个触发器
txlicenhe 2003-12-19
select *,
(case [name] 'AAA' then (select count(*) from 表2 where [name] = 'AAA') when 'BBB' then (select count(*) from 表3 where [name] = 'BBB') else 0 end)
as counts
from 表1
(case [name] 'AAA' then (select count(*) from 表2 where [name] = 'AAA') when 'BBB' then (select count(*) from 表3 where [name] = 'BBB') else 0 end)
as counts
from 表1
wzx2275067 2003-12-19
需要实时更新
cloudchen 2003-12-18
select (select count(*) from 表2 where name = 'AAA')+(select count(*) from 表3 where name = 'BBB')