select 甲.*,乙.c,max(乙.s0+乙.s1+乙.s2)
from 甲,乙
----
where 甲.wqid =乙.wqid
and 甲.a<乙.c
and 乙.c<甲.b
----如果是group by ,select必须是聚合函数,或者是group by 的列
from 甲,乙
----
where 甲.wqid =乙.wqid
and 甲.a<乙.c
and 乙.c<甲.b
----如果是group by ,select必须是聚合函数,或者是group by 的列
是甲中的一个wid对应乙中多条wid地记录 只要符合条件a<c<b 就是怎么求多条纪录s0+s1+s2的和的最大值
from 甲 inner join 乙
on 甲.wid=乙.wid
where c>a and c<b
group by 甲.wqid
select wqid, max(s0+s1+s2)
from 甲 inner join 乙
on 甲.wid=乙.wid
where c>a and c<b
group by 甲.wqid
from t1,t2
where t1.wid = t2.wid
and t1.a<t2.c
and t2.c <t1.b
group by t1.wid,t1.a,t1.b,t1.wqid,t2.c
---不知道这么写符合你的要求吗?select 甲.*----如果这里有*的话,group by 必须包含甲表的所有的列
,乙.c, -- group by 子句也必须包含乙表中想要选择的列 max(乙.s0+乙.s1+乙.s2)
from 甲,乙
group by 甲.wqid --这里不太明白,既然wqid已经是主键,分不分组有什么区别吗?having 甲.wid=乙.wid
and 甲.a<乙.c<甲.b -- 这里觉得应该分成两个条件来写
CREATE TABLE t1 (
wid varchar(5) ,
a varchar(5) ,
b varchar(5) ,
wqid varchar(5) ,
)
GOALTER TABLE t1 WITH NOCHECK ADD
CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED
(
[wqid]
) ON [PRIMARY]
GO
CREATE TABLE t2 (
wid varchar(5) ,
c varchar(5) ,
s0 varchar(5) ,
s1 varchar(5) ,
s2 varchar(5)
)
go
insert into t1 values( '01','1','9','1')
insert into t1 values( '02','2','8','2')
insert into t1 values( '01','8','9','3')select * from t1
insert into t2 values('01','5','1','2','3')
insert into t2 values('01','9','2','2','3')
insert into t2 values('01','3','3','2','3')select * from t2
select t1.* ,t2.c,(select max(t2.s0 + t2.s1 + t2.s2) from t2 )
from t1,t2
where t1.wid = t2.wid
and t1.a<t2.c
and t2.c <t1.b
group by t1.wid,t1.a,t1.b,t1.wqid,t2.c
drop table t1
drop table t2
测试的脚本,结果如下:
wid a b wqid c max(t2.s0 + t2.s1 + t2.s2)
--------------------------
01 1 9 1 3 323
01 1 9 1 5 323