表a
字段 id name
值 1 张三
2 李四
表b
字段 id cj xm
值 1 2 3
1 3 3
1 4 4
2 2 5
2 3 6我想要的结果是
name cj xm
张三 5 3
张三 4 4
李四 2 5
李四 3 6select (select name from a where id=b.id) as name,sum(cj),xm from b group by xm,id应该是这样,但是我不需要group by id ,但是还必须用id 才能查到name
怎么写???
字段 id name
值 1 张三
2 李四
表b
字段 id cj xm
值 1 2 3
1 3 3
1 4 4
2 2 5
2 3 6我想要的结果是
name cj xm
张三 5 3
张三 4 4
李四 2 5
李四 3 6select (select name from a where id=b.id) as name,sum(cj),xm from b group by xm,id应该是这样,但是我不需要group by id ,但是还必须用id 才能查到name
怎么写???
from a,b
where a.id = b.id
group by a.id , a.name , b.xm
insert into A values(1, '张三')
insert into A values(2, '李四')
create table B( id int, cj int, xm int)
insert into B values(1, 2, 3 )
insert into B values(1, 3, 3 )
insert into B values(1, 4, 4 )
insert into B values(2, 2, 5 )
insert into B values(2, 3, 6 )
goselect a.id , a.name , b.xm , sum(b.cj) cj
from a,b
where a.id = b.id
group by a.id , a.name , b.xmdrop table a,b/*
id name xm cj
----------- ---------- ----------- -----------
1 张三 3 5
1 张三 4 4
2 李四 5 2
2 李四 6 3(所影响的行数为 4 行)
*/
insert into A values(1, '张三')
insert into A values(2, '李四')
create table B( id int, cj int, xm int)
insert into B values(1, 2, 3 )
insert into B values(1, 3, 3 )
insert into B values(1, 4, 4 )
insert into B values(2, 2, 5 )
insert into B values(2, 3, 6 )
goselect a.name , b.xm , sum(b.cj) cj
from a,b
where a.id = b.id
group by a.name , b.xmdrop table a,b/*
name xm cj
---------- ----------- -----------
张三 3 5
张三 4 4
李四 5 2
李四 6 3(所影响的行数为 4 行)
*/
left join(select xm,sum(cj) cj,dh from abc b group by xm,dh)b
on a.xm = b.xm
and a.dh = b.dh
---------------------------------
这个不行吗?
因些语句中用到id ,group 中不用id
---
他想要带ID的,但分组时又不要ID。
insert into A values(1, '张三')
insert into A values(2, '李四')
create table B( id int, cj int, xm int)
insert into B values(1, 2, 3 )
insert into B values(1, 3, 3 )
insert into B values(1, 4, 4 )
insert into B values(2, 2, 5 )
insert into B values(2, 3, 6 )
goselect a.name , sum(b.cj) cj , b.xm
from a,b
where a.id = b.id
group by a.name , b.xmdrop table a,b/*
name cj xm
---------- ----------- -----------
张三 5 3
张三 4 4
李四 2 5
李四 3 6(所影响的行数为 4 行)
*/
drop table tb1
if object_id('tb2') is not null
drop table tb2
go
create table tb1(id int, name varchar(10))
insert into tb1 values(1, '张三')
insert into tb1 values(2, '李四')
create table tb2( id int, cj int, xm int)
insert into tb2 values(1, 2, 3 )
insert into tb2 values(1, 3, 3 )
insert into tb2 values(1, 4, 4 )
insert into tb2 values(2, 2, 5 )
insert into tb2 values(2, 3, 6 )select b.name,a.cj,a.xm from
(
select id,sum(cj)as cj,xm from tb2
group by id,xm
) a
inner join
tb1 b
on a.id=b.id
拿分来
哈哈
insert into Aa values(1, '张三')
insert into Aa values(2, '李四')
create table Bb( id int, cj int, xm int)
insert into Bb values(1, 2, 3 )
insert into Bb values(1, 3, 3 )
insert into Bb values(1, 4, 4 )
insert into Bb values(2, 2, 5 )
insert into Bb values(2, 3, 6 )
goselect distinct a.id,a.name,bb.xm,(select sum(cj) from bb b where a.id=id and bb.xm=b.xm) cj
from aa a inner join bb on a.id=bb.id
drop table aa,bb这种代码整个不用GROUP BY,但效果是一样的。看执行计划,比GROUP BY 还略快一些