Union All查询的语法不正确(Incorrect syntax for Union All query)
我正在尝试将所有查询与工会结合起来:
use SalesDWH go select cast(COUNT([specimen id]) as varchar) +'|'+[practice name]+'|'+b.[mlis practice id]+'|'+[practice code]+'|'+[Requesting Physician]+'|'+c.salesrep+'|'+ cast(c.dateestablished as varchar)+'|'+ c.practicecity+'|'+c.practicestate from quicklabdump a inner join qlmlismapping b on (b.[quiklab practice code] = a.[practice code]) inner join PracticeandPhysician c on (a.[Requesting Physician]=c.doctorfirstname+' '+c.DOCTORLASTNAME and a.[practice code]=c.practicecode) where DATEPART(yy, [DATE entered]) = 2011 and DATEPART(mm, [DATE entered]) = 12 group by a.[practice name],b.[mlis practice id],a.[practice code], a.[Requesting Physician],c.salesrep,c.dateestablished, c.practicecity,c.practicestate order by COUNT([specimen id]) desc union all select cast(COUNT([specimen id]) as varchar) +'|'+[practice name]+'|'+b.[mlis practice id]+'|'+[practice code]+'|'+[Requesting Physician]+'|'+c.salesrep+'|'+ cast(c.dateestablished as varchar)+'|'+ c.practicecity+'|'+c.practicestate from quicklabdump a inner join qlmlismapping b on (b.[quiklab practice code] = a.[practice code]) inner join PracticeandPhysician c on (a.[Requesting Physician]=c.doctorfirstname+' '+c.DOCTORLASTNAME and a.[practice code]=c.practicecode) where DATEPART(yy, [DATE entered]) = 2011 and DATEPART(mm, [DATE entered]) = 11 group by a.[practice name],b.[mlis practice id],a.[practice code], a.[Requesting Physician],c.salesrep,c.dateestablished, c.practicecity,c.practicestate order by COUNT([specimen id]) desc
但我得到这个错误:
Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'union'.
我究竟做错了什么?
I am trying to combine to queries with union all:
use SalesDWH go select cast(COUNT([specimen id]) as varchar) +'|'+[practice name]+'|'+b.[mlis practice id]+'|'+[practice code]+'|'+[Requesting Physician]+'|'+c.salesrep+'|'+ cast(c.dateestablished as varchar)+'|'+ c.practicecity+'|'+c.practicestate from quicklabdump a inner join qlmlismapping b on (b.[quiklab practice code] = a.[practice code]) inner join PracticeandPhysician c on (a.[Requesting Physician]=c.doctorfirstname+' '+c.DOCTORLASTNAME and a.[practice code]=c.practicecode) where DATEPART(yy, [DATE entered]) = 2011 and DATEPART(mm, [DATE entered]) = 12 group by a.[practice name],b.[mlis practice id],a.[practice code], a.[Requesting Physician],c.salesrep,c.dateestablished, c.practicecity,c.practicestate order by COUNT([specimen id]) desc union all select cast(COUNT([specimen id]) as varchar) +'|'+[practice name]+'|'+b.[mlis practice id]+'|'+[practice code]+'|'+[Requesting Physician]+'|'+c.salesrep+'|'+ cast(c.dateestablished as varchar)+'|'+ c.practicecity+'|'+c.practicestate from quicklabdump a inner join qlmlismapping b on (b.[quiklab practice code] = a.[practice code]) inner join PracticeandPhysician c on (a.[Requesting Physician]=c.doctorfirstname+' '+c.DOCTORLASTNAME and a.[practice code]=c.practicecode) where DATEPART(yy, [DATE entered]) = 2011 and DATEPART(mm, [DATE entered]) = 11 group by a.[practice name],b.[mlis practice id],a.[practice code], a.[Requesting Physician],c.salesrep,c.dateestablished, c.practicecity,c.practicestate order by COUNT([specimen id]) desc
But I am getting this error:
Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'union'.
What am I doing wrong?
原文:https://stackoverflow.com/questions/8673703
更新时间:2021-12-22 20:12
最满意答案
您应该尝试使用
NSPredicate
和NSSortDescriptor
类NSArray *sortedArray = [yourArray sortedArrayUsingDescriptors: @[[NSSortDescriptor sortDescriptorWithKey:@"insComp" ascending:YES], [NSSortDescriptor sortDescriptorWithKey:@"Id" ascending:YES]]];
这里有一个很好的教程,专门用于对目标c中的数据收集类进行排序和过滤以及所有这些类操作。
You should try going with
NSPredicate
andNSSortDescriptor
classesNSArray *sortedArray = [yourArray sortedArrayUsingDescriptors: @[[NSSortDescriptor sortDescriptorWithKey:@"insComp" ascending:YES], [NSSortDescriptor sortDescriptorWithKey:@"Id" ascending:YES]]];
There is a nice tutorial dedicated to sorting and filtering and all such kind operations on data collection classes in objective c here.
相关问答
更多-
我有点担心将一个可变对象作为一个集合中的键的一部分,好像该对象更改其哈希代码等可能会改变但该集合不会知道它(除非你让addMarker重新启动该对象然后添加它返回新的哈希码) 但是在你的例子中你不能只使用字典(或者我错过了什么?) self.markerDict = [[NSMutableDictionary alloc]init]; - (void) addMarker: (RMMarker*)marker AtLatLong:(CLLocationCoordinate2D)point { if ( ...
-
您应该尝试使用NSPredicate和NSSortDescriptor类 NSArray *sortedArray = [yourArray sortedArrayUsingDescriptors: @[[NSSortDescriptor sortDescriptorWithKey:@"insComp" ascending:YES], ...
-
你可以使用mutableCopy函数。 所以喜欢 devices = [[devices2 sortedArrayUsingDescriptors:[NSArray arrayWithObject:[NSSortDescriptor sortDescriptorWithKey:@"created" ascending:YES]]] mutableCopy]; you can use mutableCopy function. So like devices = [[devices2 sortedArr ...
-
如何使用NSSortDescriptor对NSMutableArray进行排序(How to use NSSortDescriptor to sort an NSMutableArray)[2021-12-27]
您需要过滤以及对数组进行排序。 保持原始代码的结构,你可以添加一个像这样的过滤器: NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey: @"price" ascending: YES]; NSArray *sortedArray = [self.displayItems sortedArrayUsingDescriptors: [NSArr ... -
我从未使用Google Places API,但我猜[results objectForKey:@"result"]实际上会返回另一个字典,因此objectForKey:有效。 因为objective-c只使用指针来引用对象,所以它实际上从未被转换为NSMutableArray 。 另外,objective-c在编译时不知道一个方法是否存在,因为它具有动态特性(你实际上可以在运行时添加方法甚至整个类)。 根据编译器设置,它可能只显示一条警告, objectForKey:在运行时可能找不到objectForK ...
-
您只有一个字典coreDict并且您在每个循环coreDict相同的字典添加到您的数组中。 无论何时设置密钥comp_id的值,它都会在coreDict所有实例中更改该值。 相反,您应该在每个循环上创建一个新字典并将其添加到数组中。 更换: [coreDict setValue:[NSNumber numberWithInt:(int)complianceId] forKey:@"comp_id"]; [coreArray addObject:coreDict]; 有: NSDictionary *dic ...
-
发现问题 - 我是将数组添加为对象而不是将每个对象添加到数组中。 然后在NSArray上调用sort,而不是我的自定义对象。 从[searchResults addObject:documents];更改上面的代码[searchResults addObject:documents]; to [searchResults addObjectsFromArray:documents]; 解决了这个问题! Found out the issue - I was adding arrays as objects ...
-
如何使用NSSortDescriptor对NSMutableArray进行排序?(How to sort a NSMutableArray using NSSortDescriptor?)[2023-12-06]
要按评论数n排序,您的排序描述符将是: NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey: @"reviews.n" ascending: YES]; To sort by the number of reviews n, your sort descriptor would be: NSSortDescriptor *sortDescr ... -
如何排序UIImage在ios中以降序添加NSMutableArray(How to sort UIImage added NSMutableArray in ios in descending)[2022-06-19]
简单的解决方案是使用reverse for循环获取数据。 for (int i = arr.count-1; i >= 0; i--) { UIImage *img = [arr objectAtIndex:i]; } 使用反向循环从NSMutableArray获取图像。 Simple solution is fetch data using reverse for loop. for (int i = arr.count-1; i >= 0; i--) { UIImage *img = ... -
NSMutatbleDictionary不保留顺序(排序或插入)。 你不能对它进行排序。 您需要一种不同的方法,例如使用NSArray排序键。 请参阅NSDictionary - 排序词典 。 NSMutatbleDictionary doesn't retain order (sorted or insertion). You can't sort it. You need a different approach, such as using an NSArray of sorted keys. See ...