现设有表a,字段为主键aID和列name,记录数如下:
aId Name
1 a
2 b
3 c
4 d
由于具体需求原因,统计表a的记录数比较麻烦,请教各位是否可以通过合并一张只有一个主键bId的表b,达到如下效果,从而count(b.bId)来统计出a表的记录数:
aId Name bId
1 a 1
2 b 2
3 c 3
4 d 4
ruguo 可以,请问各位这个合并的sql语句该怎么写啊
aId Name
1 a
2 b
3 c
4 d
由于具体需求原因,统计表a的记录数比较麻烦,请教各位是否可以通过合并一张只有一个主键bId的表b,达到如下效果,从而count(b.bId)来统计出a表的记录数:
aId Name bId
1 a 1
2 b 2
3 c 3
4 d 4
ruguo 可以,请问各位这个合并的sql语句该怎么写啊
Food表中我加了一列Counter,这一列的值应该是在另外一个表中UserFood中Food.ID出现的次数,请问这个sql如何写.
我用这个select [ID],IsNULL((Select count(1) from UserFood where UserFood.FoodID = Food.ID),0) from Food,可以等到想要的值,但是不知道如何插入到Food表中.谢谢!!!Food表
-------------
ID FoodName Counter
-------------
1 第一个 NULL
2 第二个 NULL
3 第三个 NULL
UserFood表
-------------
ID FoodID
-------------
1 1
2 2
3 1
4 1想要的结果是:
Food表
-------------
ID FoodName Counter
-------------
1 第一个 3
2 第二个 1
3 第三个 0
create table food (id char(1),foodname varchar(10),counter int)
insert into food
select '1' , '第一个' , null union
select '2' , '第二个' , null union
select '3' , '第三个' , nullcreate table usefood (id char(1),foodid int)
insert into usefood
select '1' , 1 union
select '2' , 2 union
select '3' , 1 union
select '4' , 1
select a.id as id, a.foodname as foodname, isnull(b.counter, 0) as counter
from food as a left join (select foodid , count(*) as counter from usefood group by foodid) as b
on a.id = b.foodid--select * from food
--select foodid , count(*) as counter from usefood group by foodiddrop table food
drop table usefoodid foodname counter
---- ---------- -----------
1 第一个 3
2 第二个 1
3 第三个 0(所影响的行数为 3 行)
insert into a select 1,'a'
union all select 2,'b'
union all select 3,'c'
union all select 4,'d'
union all select 6,'e'
union all select 8,'f'select aid,[name],bId=(select count(*) from a where atb.aid>=aid) from a atbaid name bId
----------- ---------- -----------
1 a 1
2 b 2
3 c 3
4 d 4
6 e 5
8 f 6(所影响的行数为 6 行)
select aid,name,aid as bid from a如果要count,
可以
select count(b.bid) from
(
select aid,name,aid as bid from a
) b
没必要搞一张表b