知道两个表
table1 | table2
ID TIME | ID CODE TYPE
01 2007-6-23 9:50:52 | 01 11 7
01 2007-6-23 14:54:08 | 02 15 6
01 2007-6-13 8:53:33 | 03 13 8
05 2007-6-23 9:50:52 | 04 11 3
| 05 11 5
| 06 13 9
查询语句:
select max(TIME),count(id) from table1
where TIME in (select max(TIME) from table1
group by id) group by id
结果为:
max(TIME) count(id)
2007-6-23 9:50:52 1
2007-6-23 14:54:08 2
我想要的结果是:
max(TIME) count(id)
2007-6-23 9:50:52 1
2007-6-23 14:54:08 1
查询语句:
select count(id),sum(TYPE),id,CODE from table1 a,table2 b
where a.id=b.id and TIME in (select max(TIME) from table1
group by id) group by id,CODE
结果为:
count(id) sum(TYPE) id CODE
1 5 05 11
2 14 01 11
我想要的结果是:
count(id) sum(TYPE) id CODE
1 5 05 11
1 7 01 11
因为我需要计算table2中TYPE的值,而table1中的每个ID最后时间只有1个,该怎么写SQL语句???
那位高手指点下!!
table1 | table2
ID TIME | ID CODE TYPE
01 2007-6-23 9:50:52 | 01 11 7
01 2007-6-23 14:54:08 | 02 15 6
01 2007-6-13 8:53:33 | 03 13 8
05 2007-6-23 9:50:52 | 04 11 3
| 05 11 5
| 06 13 9
查询语句:
select max(TIME),count(id) from table1
where TIME in (select max(TIME) from table1
group by id) group by id
结果为:
max(TIME) count(id)
2007-6-23 9:50:52 1
2007-6-23 14:54:08 2
我想要的结果是:
max(TIME) count(id)
2007-6-23 9:50:52 1
2007-6-23 14:54:08 1
查询语句:
select count(id),sum(TYPE),id,CODE from table1 a,table2 b
where a.id=b.id and TIME in (select max(TIME) from table1
group by id) group by id,CODE
结果为:
count(id) sum(TYPE) id CODE
1 5 05 11
2 14 01 11
我想要的结果是:
count(id) sum(TYPE) id CODE
1 5 05 11
1 7 01 11
因为我需要计算table2中TYPE的值,而table1中的每个ID最后时间只有1个,该怎么写SQL语句???
那位高手指点下!!
FROM table1 L
JOIN table1 R
ON L.ID = R.ID
GROUP BY L.ID,L.Time
HAVING L.Time = MAX(R.Time)结果是
id Time
05 2007-06-23 09:50:52.000
01 2007-06-23 14:54:08.000第二个查询
select A.ID, Type, CODE
from
(SELECT L.ID,L.Time
FROM table1 L
JOIN table1 R
ON L.ID = R.ID
GROUP BY L.ID,L.Time
HAVING L.Time = MAX(R.Time)) as A
inner join table2 B
on A.ID = B.ID结果是
id type code
01 7 11
05 5 11我不明白你前面那个count(id)什么意思
select max(TIME),count(id) from table1 a
where TIME in (select max(TIME) from table1
where id = a.id) group by id--第二個
select count(a.id), sum(TYPE),a.id,CODE from table1 a,table2 b
where a.id=b.id and TIME in (select max(TIME) from table1
where id = a.id) group by a.id,CODE
Create Table table1
(ID Char(2),
[TIME] DateTime)
Create Table table2
(ID Char(2),
CODE Int,
TYPE Int)
Insert table1 Select '01', '2007-6-23 9:50:52'
Union All Select '01', '2007-6-23 14:54:08'
Union All Select '01', '2007-6-13 8:53:33'
Union All Select '05', '2007-6-23 9:50:52'Insert table2 Select '01', 11, 7
Union All Select '02', 15, 6
Union All Select '03', 13, 8
Union All Select '04', 11, 3
Union All Select '05', 11, 5
Union All Select '06', 13, 9
GO
--測試
select max(TIME),count(id) from table1 a
where TIME in (select max(TIME) from table1
where id = a.id) group by idselect count(a.id), sum(TYPE),a.id,CODE from table1 a,table2 b
where a.id=b.id and TIME in (select max(TIME) from table1
where id = a.id) group by a.id,CODE
GO
--刪除測試環境
Drop Table table1, table2
--結果
/*
max(TIME) count(id)
2007-06-23 14:54:08.000 1
2007-06-23 09:50:52.000 1count(id) sum(TYPE) id CODE
1 7 01 11
1 5 05 11
*/
我估計他加上這個的原因就是檢查id有沒有重復,估計實際應用的時候他會去掉。