SQL题集

//要求：用一条SQL语句实现日期过滤，当pid相同的时候返回离系统时间最近的那条记录。

SELECT
             A.*
FROM
       exam_1 A
      JOIN (
                SELECT

                           MAX(time) date,
                           pid 
                FROM
                          exam_1 
                GROUP BY
                          pid
                ) B
ON

        a.pid = b.pid AND a.time =b.date 

//要求：一条SQL语句按class分别取出price的前三条记录，price按降序排列。

SELECT
               *
FROM
               exam_2  a
WHERE
              price IN
             (
              SELECT
                         TOP 3 price
              FROM 
                         exam_2 b
              WHERE
                         a.class=b.class
              ORDER BY

                         price DESC
              ) 
ORDER BY
           CLASS,
           price DESC

//要求：用一条SQL语句统计不同元素出现的次数，并要求出现次数大于5，并放到另一表R1中。

--要求：用一条SQL?句??不同元素出?的次数，并要求出?次数大于5，并放到?一表R1中
DROP TABLE R1
SELECT
              ITEM  NUM,
              SUM(COT) COUNT
INTO
              R1
FROM
           (
            SELECT a ITEM,COUNT(a) COT FROM exam_3 WHERE a >= 0 GROUP BY a
            UNION ALL
            SELECT b,     COUNT(b)      FROM exam_3 WHERE b >= 0 GROUP BY b
            UNION ALL
            SELECT c,    COUNT(c)      FROM exam_3 WHERE c >= 0 GROUP BY c
            UNION ALL
            SELECT d,    COUNT(d)      FROM exam_3 WHERE d >= 0 GROUP BY d
            UNION ALL
            SELECT e,    COUNT(e)      FROM exam_3 WHERE e >= 0 GROUP BY e
           ) M
GROUP BY
          ITEM
HAVING
          SUM(COT) >= 5
SELECT
            *
FROM
           R1