求一查询语句~
t1表
id number class_id
06 2 596
74 4 597
74 1 598
74 5 599
74 13 600
t2表
id number class_id
06 2 596
74 1 598
74 2 599
74 4 597
要得到如下结果，该如何写sql？
id number class_id
74 3 599
74 13 600
[解决办法]
74 3 599
这个是怎么出来的阿~~~~
[解决办法]
看不明白.
楼主解释一下结果是怎么出来的.
[解决办法]
74 13 600,这个是取最大.
74 3 599这个呢?
[解决办法]
select id , max(number) number,max(class_id) class_id from t1 where id = 74 group by id
union all
select id , count(*) number,max(class_id) class_id from t1 where id = 74 group by id
[解决办法]
create table t1 (id varchar(10), number int, class_id varchar(10))
insert into t1 select '06 ', 2, '596 '
union all select '74 ', 4, '597 '
union all select '74 ', 1, '598 '
union all select '74 ', 5, '599 '
union all select '74 ', 13, '600 '
create table t2( id varchar(10), number int, class_id varchar(10))
insert into t2 select '06 ', 2, '596 '
union all select '74 ', 1, '598 '
union all select '74 ', 2, '599 '
union all select '74 ', 4, '597 '
select max(a.id)id,isnull(avg(b.number),avg(a.number))number,max(a.class_id)class_id
from t1 a full join t2 b on b.class_id=a.class_id
group by isnull(b.number*0,a.class_id)
id number class_id
---------- ----------- ----------
74 2 599
74 13 600
（所影响的行数为 2 行）
[解决办法]
try
Select
A.id,
A.number - IsNull(B.number, 0) As number,
A.class_id
From
t1 A
Left Join
t2 B
On A.id = B.id And A.class_id = B.class_id
Where (B.id Is Null) Or A.number - IsNull(B.number, 0) > 0
[解决办法]
Create Table t1
(idChar(2),
numberInt,
class_idInt)
Insert t1 Select '06 ', 2, 596
Union All Select '74 ', 4, 597
Union All Select '74 ', 1, 598
Union All Select '74 ', 5, 599
Union All Select '74 ', 13, 600
Union All Select '77 ', 10, 601
Create Table t2
(idChar(2),
numberInt,
class_idInt)
Insert t2 Select '06 ', 2, 596
Union All Select '74 ', 1, 598
Union All Select '74 ', 2, 599
Union All Select '74 ', 4, 597
GO
Select
A.id,
A.number - IsNull(B.number, 0) As number,
A.class_id
From
t1 A
Left Join
t2 B
On A.id = B.id And A.class_id = B.class_id
Where (B.id Is Null) Or A.number - IsNull(B.number, 0) > 0
GO
Drop Table t1, t2
--Result
/*
idnumberclass_id
743599
7413600
7710601
*/
[解决办法]
select max(a.id)id,isnull(avg(a.number),avg(a.number))number,max(a.class_id)class_id
from t1 a full join t2 b on b.class_id=a.class_id
group by isnull(b.number*0,a.class_id)
