假设有两个表,表A和表B,两个表的数据都是不固定的,但是有事根据A表的name字段进行分组
其中表A 里面有一个a_count字段,不是固定的数值,还有一个name字段,id是标识列..输出a_count所有值的和
表B,则是根据表A的id字段,查询在B表中对于那个ID的行数.
我是用name的字段进行分组,语句如下,但是发现输出的数据不对..
Select r.Name,sum(r.a_count),count(s.ID) From A as r,B as s where s.aId=r.ID group by r.Name
比如我表a中有11条数据,表B有8条,
最后输出的时候sum(r.a_count)只有8条的值,而count(s.ID)就正确是8
其中表A 里面有一个a_count字段,不是固定的数值,还有一个name字段,id是标识列..输出a_count所有值的和
表B,则是根据表A的id字段,查询在B表中对于那个ID的行数.
我是用name的字段进行分组,语句如下,但是发现输出的数据不对..
Select r.Name,sum(r.a_count),count(s.ID) From A as r,B as s where s.aId=r.ID group by r.Name
比如我表a中有11条数据,表B有8条,
最后输出的时候sum(r.a_count)只有8条的值,而count(s.ID)就正确是8
这个不行,输出的值多了,比如我的a_count里面的值全是1,一共11条,最后查询出来的竟然有13条,怎么加都不可能13条啊,left join,inner join,right join都试过了
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[mCount] [int] NOT NULL DEFAULT ((0)),
[mSMSstate] [bit] NOT NULL,
[mName] [varchar](50) NOT NULL
)
CREATE TABLE [dbo].[tableB](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[sMsgId] [int] NOT NULL,
[sRes] [int] NOT NULL,
[sName] [varchar](50) NOT NULL
)
insert into dbo.tableA values(1,'True','test')
insert into dbo.tableA values(2,'True','test')
insert into dbo.tableA values(1,'True','test')
insert into dbo.tableA values(1,'True','test')
insert into dbo.tableA values(5,'False','test')
insert into dbo.tableA values(1,'False','test')
insert into dbo.tableB values(1,1,'test')
insert into dbo.tableB values(1,1,'test')
insert into dbo.tableB values(1,1,'test')
insert into dbo.tableB values(1,1,'test')
insert into dbo.tableB values(1,2,'test')
insert into dbo.tableB values(1,2,'test')查询语句
Select r.mName,sum(r.mCount),sum(case when r.mSMSstate='True' then r.mCount end),count(s.ID),count(case when s.sRes=1 then s.ID end) From tableA as r left join tableB as s on s.sName=r.mName group by r.mName
正确需要的结果应该为
mName ASum ATSum Bcount BTcount
test 11 4 6 4现在的结果确实查很远..
表B值算条数,而表A是要算和的...
Select r.mName,sum(r.mCount),
sum(case when r.mSMSstate='true' then 1 else 0 end),count(s.ID),
count(case when s.sRes=1 then s.ID end) From tableA as r left join tableB as s on s.sName=r.mName and r.id=s.id group by r.mName
sum(case when r.mSMSstate='true' then 1 else 0 end),count(s.ID),
count(case when s.sRes=1 then s.ID end) From tableA as r left join tableB as s on s.sName=r.mName and r.id=s.id group by r.mName
Select s.sName,count(s.ID),count(case when s.sRes=1 then s.ID end) From tableB as s group by s.sName Select r.mName,sum(r.mCount),sum(case when r.mSMSstate='True' then r.mCount end)From tableA as r group by r.mName
和两个查询语句根据Name并成一行的..