2013-09-10
a)
SELECT sno, cno, grade
FROM scg
WHERE grade < 60;
b)
SELECT sno, COUNT(cno)
FROM scg
WHERE grade>=90
GROUP BY sno HAVING COUNT(cno)>2
ORDER BY COUNT(cno) DESC;
c)
SELECT TOP 3 sno, cno, grade
FROM scg
GROUP BY sno
ORDER BY sno, grade;
d)
- SELECT cno, first.sno, second.sno, first.grade, second.grade, first.grade-second.grade
- FROM
- (
- SELECT TOP 1 *
- FROM scg
- GROUP BY cno
- ORDER BY grade
- ) first,
- (
- SELECT TOP 1 *
- FROM
- ( SELECT TOP 2 *
- FROM scg
- GROUP BY cno
- ORDER BY grade DESC
- ) two
- GROUP BY two.cno
- ORDER BY two.grade
- ) second
- ;
已知字母序列【d, g, e, c, f, b, o, a】,请实现一个函数针对输入的一组字符串 input[] = {"bed", "dog", "dear", "eye"},按照字母顺序排序并打印。
本例的输出顺序为:dear, dog, eye, bed。
- #include <stdio.h>
- #include <stdlib.h>
- void swapstr(char **a,char **b){
- if(a!=b){
- char *tmp=*a;
- *a=*b;
- *b=tmp;
- }
- }
- int compare(char *a,char *b,char *hashstr){
- int lena=strlen(a);
- int lenb=strlen(b);
- int i=0;int j=0;
- while(i<lena&&j<lenb){
- printf("%d---%d\n",hashstr[*(a+i)],hashstr[*(b+j)]);
- if(hashstr[*(a+i)]<hashstr[*(b+j)]){
- return 1;
- }else if(hashstr[*(a+i)]>hashstr[*(b+j)]){
- return -1;
- }
- i++;j++;
- }
- if((i==lena)&&(j==lenb))
- {
- return 0;
- }
- if(i==lena){
- return -1;
- }
- if(j==lenb){
- return 1;
- }
- }
- int main()
- {
- char seq[]="dgecfboa";
- char hashstr[256];
- int count=0;
- memset(hashstr,0,256);
- int i;
- for(i=0;seq[i]!='\0';i++){
- hashstr[seq[i]]=count++;
- }
- char arrstr[][5]={"bed","dog","dear","eye"};
- int j,k;
- int maxword;
- int retu;
- for(j=0;j<4;j++){
- maxword=j;
- for(k=j+1;k<4;k++){
- retu=compare(arrstr[k],arrstr[maxword],hashstr);
- if(retu>0){
- maxword=k;
- }
- }
- swapstr(&arrstr[j],&arrstr[maxword]);
- }
- for(int x=0;x<4;x++){
- printf("%s\n",arrstr[x]);
- }
- return 0;
- }