求一个SQL语句，这个有比较大的难度哦
现在有一个数据库，如下
- HTML code
QUERY_GI HIT_GI QUERY_REF HIT_REF SCORE155041721 155041492 NC_004812 NC_006560 1205155041721 155041491 NC_004812 NC_006560 1008155041721 9629382 NC_004812 NC_001806 996155041721 51557541 NC_004812 NC_006151 746155041721 50313302 NC_004812 NC_001491 718155041721 9629874 NC_004812 NC_001847 714155041721 40787926 NC_004812 NC_005261 696155041721 9629790 NC_004812 NC_001844 694155041721 13242453 NC_004812 NC_002686 670155041721 9625933 NC_004812 NC_001348 662
其中QUERY_GI，HIT_GI为编号，QUERY_REF与HIT_REF分别为QUERY_GI，HIT_GI所在的区域。Score为得分。
现在要得到QUERY_GI，HIT_GI，要求如下：
如果有两个或者两个以上的HIT_GI在同一个HIT_REF中，则选取Score最高的HIT_GI.
比如：
- HTML code
QUERY_GI HIT_GI QUERY_REF HIT_REF SCORE155041721 155041492 NC_004812 NC_006560 1205155041721 155041491 NC_004812 NC_006560 1008
155041492和155041491都在NC_006560区域中，
则选取
QUERY_GI=155041721 HIT_GI=155041492
就可以了
QUERY_GI=155041721和HIT_GI=155041491就不用选取了。
[解决办法]
没看明白 hit_ref惟一吗？如果惟一就 max（score) group by hit_ref
[解决办法]
你的汉语能力很可怜。
分析了一下斧数据，估计你想要的是
"取QUERY_GI , HIT_REF 相同记录中 SCORE 最大的一条记录"
- SQL code
db2# select * from t_yukiooy;
+-----------+-----------+-----------+-----------+-------+
| QUERY_GI | HIT_GI | QUERY_REF | HIT_REF | SCORE |
+-----------+-----------+-----------+-----------+-------+
| 155041721 | 155041492 | NC_004812 | NC_006560 | 1205 |
| 155041721 | 155041491 | NC_004812 | NC_006560 | 1008 |
| 155041721 | 9629382 | NC_004812 | NC_001806 | 996 |
| 155041721 | 51557541 | NC_004812 | NC_006151 | 746 |
| 155041721 | 50313302 | NC_004812 | NC_001491 | 718 |
| 155041721 | 9629874 | NC_004812 | NC_001847 | 714 |
| 155041721 | 40787926 | NC_004812 | NC_005261 | 696 |
| 155041721 | 9629790 | NC_004812 | NC_001844 | 694 |
| 155041721 | 13242453 | NC_004812 | NC_002686 | 670 |
| 155041721 | 9625933 | NC_004812 | NC_001348 | 662 |
| 155041721 | 10140967 | NC_004812 | NC_002531 | 468 |
| 155041721 | 51518047 | NC_004812 | NC_006146 | 463 |
| 155041721 | 82503235 | NC_004812 | NC_007605 | 461 |
| 155041721 | 139424508 | NC_004812 | NC_009334 | 460 |
| 155041721 | 24943128 | NC_004812 | NC_004367 | 452 |
| 155041721 | 13095623 | NC_004812 | NC_002665 | 448 |
| 155041721 | 9629613 | NC_004812 | NC_001826 | 443 |
| 155041721 | 66476626 | NC_004812 | NC_007016 | 435 |
| 155041721 | 18653853 | NC_004812 | NC_003401 | 434 |
| 155041721 | 9628049 | NC_004812 | NC_001650 | 430 |
| 155041721 | 9845402 | NC_004812 | NC_002512 | 425 |
| 155041721 | 9631237 | NC_004812 | NC_001987 | 414 |
| 155041721 | 61660218 | NC_004812 | NC_004065 | 409 |
| 155041721 | 51874301 | NC_004812 | NC_001716 | 406 |
| 155041721 | 139472838 | NC_004812 | NC_009333 | 400 |
| 155041721 | 9626002 | NC_004812 | NC_001350 | 389 |
| 155041721 | 52139279 | NC_004812 | NC_006273 | 381 |
| 155041721 | 51556602 | NC_004812 | NC_006150 | 380 |
| 155041721 | 20026696 | NC_004812 | NC_003521 | 379 |
| 155041721 | 14251103 | NC_004812 | NC_002794 | 374 |
| 155041721 | 9628383 | NC_004812 | NC_001664 | 365 |
| 155041721 | 9633150 | NC_004812 | NC_000898 | 359 |
| 9625915 | 13242434 | NC_001348 | NC_002686 | 5695 |
| 9625915 | 50313284 | NC_001348 | NC_001491 | 4432 |
| 9625915 | 9629770 | NC_001348 | NC_001844 | 4394 |
| 9625915 | 40787908 | NC_001348 | NC_005261 | 4159 |
| 9625915 | 51557522 | NC_001348 | NC_006151 | 4131 |
| 9625915 | 9629856 | NC_001348 | NC_001847 | 4111 |
| 9625915 | 9629399 | NC_001348 | NC_001806 | 3820 |
| 9625915 | 9629288 | NC_001348 | NC_001798 | 3811 |
| 9625915 | 56694741 | NC_001348 | NC_006560 | 3678 |
| 9625915 | 30984447 | NC_001348 | NC_004812 | 3657 |
| 9625915 | 9631215 | NC_001348 | NC_001987 | 1227 |
| 9625915 | 9625981 | NC_001348 | NC_001350 | 1204 |
| 9625915 | 13095602 | NC_001348 | NC_002665 | 1204 |
| 9625915 | 139472823 | NC_001348 | NC_009333 | 1202 |
| 9625915 | 66476594 | NC_001348 | NC_007016 | 1184 |
| 9625915 | 18653831 | NC_001348 | NC_003401 | 1177 |
| 9625915 | 51518066 | NC_001348 | NC_006146 | 1164 |
| 9625915 | 82503253 | NC_001348 | NC_007605 | 1160 |
| 9625915 | 139424528 | NC_001348 | NC_009334 | 1153 |
| 9625915 | 24943108 | NC_001348 | NC_004367 | 1139 |
| 9625915 | 10140947 | NC_001348 | NC_002531 | 1137 |
| 9625915 | 9628027 | NC_001348 | NC_001650 | 1136 |
| 9625915 | 9629568 | NC_001348 | NC_001826 | 1100 |
| 9625915 | 51874281 | NC_001348 | NC_001716 | 1062 |
| 9625915 | 9628359 | NC_001348 | NC_001664 | 1053 |
| 9625915 | 9633128 | NC_001348 | NC_000898 | 1052 |
| 9625915 | 52139259 | NC_001348 | NC_006273 | 973 |
| 9625915 | 14251073 | NC_001348 | NC_002794 | 964 |
| 9625915 | 9845371 | NC_001348 | NC_002512 | 932 |
| 9625915 | 20026677 | NC_001348 | NC_003521 | 618 |
| 9625915 | 51556577 | NC_001348 | NC_006150 | 592 |
+-----------+-----------+-----------+-----------+-------+
63 rows in set (0.00 sec)
db2# select *
-> from t_yukiooy a
-> where not exists (
-> select 1 from t_yukiooy
-> where HIT_REF=a.HIT_REF
-> and QUERY_GI=a.QUERY_GI
-> and SCORE>a.SCORE);
+-----------+-----------+-----------+-----------+-------+
| QUERY_GI | HIT_GI | QUERY_REF | HIT_REF | SCORE |
+-----------+-----------+-----------+-----------+-------+
| 155041721 | 155041492 | NC_004812 | NC_006560 | 1205 |
| 155041721 | 9629382 | NC_004812 | NC_001806 | 996 |
| 155041721 | 51557541 | NC_004812 | NC_006151 | 746 |
| 155041721 | 50313302 | NC_004812 | NC_001491 | 718 |
| 155041721 | 9629874 | NC_004812 | NC_001847 | 714 |
| 155041721 | 40787926 | NC_004812 | NC_005261 | 696 |
| 155041721 | 9629790 | NC_004812 | NC_001844 | 694 |
| 155041721 | 13242453 | NC_004812 | NC_002686 | 670 |
| 155041721 | 9625933 | NC_004812 | NC_001348 | 662 |
| 155041721 | 10140967 | NC_004812 | NC_002531 | 468 |
| 155041721 | 51518047 | NC_004812 | NC_006146 | 463 |
| 155041721 | 82503235 | NC_004812 | NC_007605 | 461 |
| 155041721 | 139424508 | NC_004812 | NC_009334 | 460 |
| 155041721 | 24943128 | NC_004812 | NC_004367 | 452 |
| 155041721 | 13095623 | NC_004812 | NC_002665 | 448 |
| 155041721 | 9629613 | NC_004812 | NC_001826 | 443 |
| 155041721 | 66476626 | NC_004812 | NC_007016 | 435 |
| 155041721 | 18653853 | NC_004812 | NC_003401 | 434 |
| 155041721 | 9628049 | NC_004812 | NC_001650 | 430 |
| 155041721 | 9845402 | NC_004812 | NC_002512 | 425 |
| 155041721 | 9631237 | NC_004812 | NC_001987 | 414 |
| 155041721 | 61660218 | NC_004812 | NC_004065 | 409 |
| 155041721 | 51874301 | NC_004812 | NC_001716 | 406 |
| 155041721 | 139472838 | NC_004812 | NC_009333 | 400 |
| 155041721 | 9626002 | NC_004812 | NC_001350 | 389 |
| 155041721 | 52139279 | NC_004812 | NC_006273 | 381 |
| 155041721 | 51556602 | NC_004812 | NC_006150 | 380 |
| 155041721 | 20026696 | NC_004812 | NC_003521 | 379 |
| 155041721 | 14251103 | NC_004812 | NC_002794 | 374 |
| 155041721 | 9628383 | NC_004812 | NC_001664 | 365 |
| 155041721 | 9633150 | NC_004812 | NC_000898 | 359 |
| 9625915 | 13242434 | NC_001348 | NC_002686 | 5695 |
| 9625915 | 50313284 | NC_001348 | NC_001491 | 4432 |
| 9625915 | 9629770 | NC_001348 | NC_001844 | 4394 |
| 9625915 | 40787908 | NC_001348 | NC_005261 | 4159 |
| 9625915 | 51557522 | NC_001348 | NC_006151 | 4131 |
| 9625915 | 9629856 | NC_001348 | NC_001847 | 4111 |
| 9625915 | 9629399 | NC_001348 | NC_001806 | 3820 |
| 9625915 | 9629288 | NC_001348 | NC_001798 | 3811 |
| 9625915 | 56694741 | NC_001348 | NC_006560 | 3678 |
| 9625915 | 30984447 | NC_001348 | NC_004812 | 3657 |
| 9625915 | 9631215 | NC_001348 | NC_001987 | 1227 |
| 9625915 | 9625981 | NC_001348 | NC_001350 | 1204 |
| 9625915 | 13095602 | NC_001348 | NC_002665 | 1204 |
| 9625915 | 139472823 | NC_001348 | NC_009333 | 1202 |
| 9625915 | 66476594 | NC_001348 | NC_007016 | 1184 |
| 9625915 | 18653831 | NC_001348 | NC_003401 | 1177 |
| 9625915 | 51518066 | NC_001348 | NC_006146 | 1164 |
| 9625915 | 82503253 | NC_001348 | NC_007605 | 1160 |
| 9625915 | 139424528 | NC_001348 | NC_009334 | 1153 |
| 9625915 | 24943108 | NC_001348 | NC_004367 | 1139 |
| 9625915 | 10140947 | NC_001348 | NC_002531 | 1137 |
| 9625915 | 9628027 | NC_001348 | NC_001650 | 1136 |
| 9625915 | 9629568 | NC_001348 | NC_001826 | 1100 |
| 9625915 | 51874281 | NC_001348 | NC_001716 | 1062 |
| 9625915 | 9628359 | NC_001348 | NC_001664 | 1053 |
| 9625915 | 9633128 | NC_001348 | NC_000898 | 1052 |
| 9625915 | 52139259 | NC_001348 | NC_006273 | 973 |
| 9625915 | 14251073 | NC_001348 | NC_002794 | 964 |
| 9625915 | 9845371 | NC_001348 | NC_002512 | 932 |
| 9625915 | 20026677 | NC_001348 | NC_003521 | 618 |
| 9625915 | 51556577 | NC_001348 | NC_006150 | 592 |
+-----------+-----------+-----------+-----------+-------+
62 rows in set (0.02 sec)
db2#