如何在列中查询具有特定值的最新记录(How to query for the most recent records with specific values in column)
我有一个DB学校(只有3个)。 在DB中,提交有关其性能的记录。
我希望用户可以选择仅查询每所学校的最新条目。
目前 - 我可以查询最新的整体...
$result = mysqli_query($con,"SELECT * FROM reports, academicyears,schools,terms WHERE reports.report_id=(SELECT MAX(report_id) FROM reports WHERE school_id=1 OR school_id=2 OR school_id=3) AND academicyears.ay_id=reports.ay_id AND schools.school_id=reports.school_id AND terms.term_id=reports.term_id ORDER BY reports.datesubmitted DESC
但我希望能够同时找到最新的School 1&School 2&School 3。
我可以看到OR没有给我我想要的东西......它只是返回最新记录! 该查询应该是什么?
此外 - 下一步是找到最新的记录,而不必命名学校ID。 随着学校数量的增长 - 这将是完全无效的! 那可能吗?
I have a DB of schools (there are only 3). In the DB, records are submitted regarding their performance.
I want an option for the user to query only the most recent entries for each school.
At the moment - I can query the most recent overall...
$result = mysqli_query($con,"SELECT * FROM reports, academicyears,schools,terms WHERE reports.report_id=(SELECT MAX(report_id) FROM reports WHERE school_id=1 OR school_id=2 OR school_id=3) AND academicyears.ay_id=reports.ay_id AND schools.school_id=reports.school_id AND terms.term_id=reports.term_id ORDER BY reports.datesubmitted DESC
But I would like to be able to find the most recent for School 1 & School 2 & School 3 at the same time.
I can see the OR isn't giving me what I want... it just returns the latest record! What should the query be?
Also - the next step is then find the most recent records without having to name the school ids at all. As the number of schools grow - that would be totally inefficient! Is that possible?
原文:https://stackoverflow.com/questions/30924406
最满意答案
我通过声明位置数组和数组图像以简单的方式完成了这项工作。 我有viewDidLoad方法如下。
override func viewDidLoad() { super.viewDidLoad() self.mapView.delegate = self let location = [ CLLocationCoordinate2D(latitude: 12, longitude: 77), CLLocationCoordinate2D(latitude: 12.5, longitude: 77.5), CLLocationCoordinate2D(latitude: 13, longitude: 78), CLLocationCoordinate2D(latitude: 13.5, longitude: 78) ] let pins = [ "red.png", "blue.png", "green.png", "yellow.png" ] var annotations = [MKPointAnnotation]() for (index, eachLocation) in location.enumerate() { let pinImageName = pins[index] let annotation = MKPointAnnotation() annotation.coordinate = eachLocation annotation.title = "\(pinImageName)" annotations.append(annotation) } mapView.addAnnotations(annotations) }
并且在viewForAnnotation委托方法中,只是从title属性获取该图像名称并显式禁用该标注
func mapView(mapView: MKMapView, viewForAnnotation annotation: MKAnnotation) -> MKAnnotationView? { let annotationReuseId = "Truck" var trkPin = mapView.dequeueReusableAnnotationViewWithIdentifier(annotationReuseId) if trkPin == nil { trkPin = MKAnnotationView(annotation: annotation, reuseIdentifier: annotationReuseId) trkPin!.canShowCallout = false } else { trkPin!.annotation = annotation if let image = annotation.title { trkPin!.image = UIImage(named: image ?? "default.png") } else { trkPin!.image = UIImage(named: "default.png") } } return trkPin }
I have done this in the simple way by declaring array of location and array images. And I have viewDidLoad method as follows.
override func viewDidLoad() { super.viewDidLoad() self.mapView.delegate = self let location = [ CLLocationCoordinate2D(latitude: 12, longitude: 77), CLLocationCoordinate2D(latitude: 12.5, longitude: 77.5), CLLocationCoordinate2D(latitude: 13, longitude: 78), CLLocationCoordinate2D(latitude: 13.5, longitude: 78) ] let pins = [ "red.png", "blue.png", "green.png", "yellow.png" ] var annotations = [MKPointAnnotation]() for (index, eachLocation) in location.enumerate() { let pinImageName = pins[index] let annotation = MKPointAnnotation() annotation.coordinate = eachLocation annotation.title = "\(pinImageName)" annotations.append(annotation) } mapView.addAnnotations(annotations) }
And in the viewForAnnotation delegate method just getting that image name from the title property and explicitly disabling the callout
func mapView(mapView: MKMapView, viewForAnnotation annotation: MKAnnotation) -> MKAnnotationView? { let annotationReuseId = "Truck" var trkPin = mapView.dequeueReusableAnnotationViewWithIdentifier(annotationReuseId) if trkPin == nil { trkPin = MKAnnotationView(annotation: annotation, reuseIdentifier: annotationReuseId) trkPin!.canShowCallout = false } else { trkPin!.annotation = annotation if let image = annotation.title { trkPin!.image = UIImage(named: image ?? "default.png") } else { trkPin!.image = UIImage(named: "default.png") } } return trkPin }