有一个记录罪犯资料的表 casetalbe
结构如下:
idcard char(20) --罪犯的id
case_type char(1) -- 0 刑事案件 --1 民事案件
case char(400) --犯罪记录
case_date date --犯罪时间现在要选出idcard, case_type, case, case_date, 如果一个罪犯有多条犯罪记录,只显示发生最早的那一条。+--------+-----------+----------+------------+
| idcard | case_type | case | case_date |
+--------+-----------+----------+------------+
| 12345 | 0 | 抢劫 | 1998-01-28 |
| 12345 | 0 | 吸毒 | 2003-03-04 |
| 12345 | 0 | 抢劫 | 2003-03-04 |
| 12345 | 1 | 欠钱不还 | 2004-03-04 |
| 34555 | 1 | 欠钱不还 | 2004-08-18 |
| 66665 | 1 | 欠钱不还 | 2005-11-07 |
+--------+-----------+----------+------------+最终结果应该是12345那个罪犯的两个抢劫只显示1998年的那个抢劫
结构如下:
idcard char(20) --罪犯的id
case_type char(1) -- 0 刑事案件 --1 民事案件
case char(400) --犯罪记录
case_date date --犯罪时间现在要选出idcard, case_type, case, case_date, 如果一个罪犯有多条犯罪记录,只显示发生最早的那一条。+--------+-----------+----------+------------+
| idcard | case_type | case | case_date |
+--------+-----------+----------+------------+
| 12345 | 0 | 抢劫 | 1998-01-28 |
| 12345 | 0 | 吸毒 | 2003-03-04 |
| 12345 | 0 | 抢劫 | 2003-03-04 |
| 12345 | 1 | 欠钱不还 | 2004-03-04 |
| 34555 | 1 | 欠钱不还 | 2004-08-18 |
| 66665 | 1 | 欠钱不还 | 2005-11-07 |
+--------+-----------+----------+------------+最终结果应该是12345那个罪犯的两个抢劫只显示1998年的那个抢劫
left join tt b on a.idcard=b.idcard and a.case_date>=b.case_date
group by a.idcard,a.case_type,a.case,a.case_date having count(b.case_date)=1
where not exists (select 1 from casetalbe b where b.idcard=a.idcard and b.case_type=a.case_type and b.case=a.case and b.case_date<a.case_date);
where not exists (select 1 from casetalbe where idcard=a.idcard and case_date<a.case_date);
| idcard | case_type | case | case_date |
+--------+-----------+----------+------------+
| 12345 | 0 | 抢劫 | 1998-01-28 |
| 12345 | 0 | 吸毒 | 2003-03-04 |
| 12345 | 0 | 抢劫 | 2003-03-04 |
| 12345 | 1 | 欠钱不还 | 2004-03-04 |
| 34555 | 1 | 欠钱不还 | 2004-08-18 |
| 66665 | 1 | 欠钱不还 | 2005-11-07 |
------------------------------------------------------------
select * from t ,(select idcard,min(case_date) from t group by idcard ) tt
where t.idcard = tt.idcard
+--------+-----------+----------+------------+
| idcard | case_type | case | case_date |
+--------+-----------+----------+------------+
| 12345 | 0 | 抢劫 | 1998-01-28 |
| 12345 | 0 | 吸毒 | 2003-03-04 |
| 12345 | 0 | 抢劫 | 2003-03-04 |
| 12345 | 1 | 欠钱不还 | 2004-03-04 |
| 34555 | 1 | 欠钱不还 | 2004-08-18 |
| 66665 | 1 | 欠钱不还 | 2005-11-07 |
+--------+-----------+----------+------------+最终结果应该是12345那个罪犯的两个抢劫只显示1998年的那个抢劫 猜搂主针对上面的源数据想要的结果是:
+--------+-----------+----------+------------+
| idcard | case_type | case | case_date |
+--------+-----------+----------+------------+
| 12345 | 0 | 抢劫 | 1998-01-28 |
| 12345 | 0 | 吸毒 | 2003-03-04 |
| 12345 | 1 | 欠钱不还 | 2004-03-04 |
| 34555 | 1 | 欠钱不还 | 2004-08-18 |
| 66665 | 1 | 欠钱不还 | 2005-11-07 |
+--------+-----------+----------+------------+
(select idcard,case_type,casem,min(case_date)case_date from casetable group by idcard,case_type,casem ) b
where a.idcard = b.idcard
and a.case_type = b.case_type and
a.casem = b.casem and
a.case_date = b.case_date;+--------+-----------+----------+------------+
| idcard | case_type | casem | case_date |
+--------+-----------+----------+------------+
| 12345 | 0 | ÇÀ½Ù | 1998-01-28 |
| 12345 | 0 | Ç¿¼é | 2003-03-04 |
| 12345 | 1 | Ç·Ç®²»»¹ | 2004-03-04 |
| 34555 | 1 | Ç·Ç®²»»¹ | 2004-08-18 |
| 66665 | 1 | Ç·Ç®²»»¹ | 2005-11-07 |
+--------+-----------+----------+------------+
5 rows in set (0.00 sec)
-> left join casetable b on
-> a.idcard=b.idcard
-> and a.case_type = b.case_type
-> and a.casem = b.casem
-> and a.case_date>=b.case_date
-> group by a.idcard,a.case_type,a.casem,a.case_date having count(b.case_date)=1;
+--------+-----------+----------+------------+
| idcard | case_type | casem | case_date |
+--------+-----------+----------+------------+
| 12345 | 0 | ÇÀ½Ù | 1998-01-28 |
| 12345 | 0 | Ç¿¼é | 2003-03-04 |
| 12345 | 1 | Ç·Ç®²»»¹ | 2004-03-04 |
| 34555 | 1 | Ç·Ç®²»»¹ | 2004-08-18 |
| 66665 | 1 | Ç·Ç®²»»¹ | 2005-11-07 |
+--------+-----------+----------+------------+
5 rows in set (0.00 sec)
2003-03-04 不是大于 1998-01-28 ?
select idcard,case_type,case,case_date
from casetalbe
group by idcard,case_date
order by case_date
limit 1
select idcard,case_type,case,case_date
from casetalbe
group by idcard,case_date
order by unix_timestamp(case_date)
limit 1
那试下这个吧:select a.*
from
casetalbe a,
(select idcard,case_type,case,min(case_date) as min_date from casetalbe group by idcard,case_type,case) b
where b.idcard=a.idcard and b.case_type=a.case_type and b.case=a.case and b.min_date=a.case_date);
select * from casetable where case_time in (select min(case_time) from casetable group by idcard);
[/code]
我是新人,不知道对不对,进来练习下...
select a.idcard,a.case_type,a.case,a.case_date,b.* from tt a
left join tt b on a.idcard=b.idcard and a.case_date>=b.case_date看看结果就知道了,你可以测试一下,HAVING与EXISTS的速度对比如何