与MSSQL对比学习MYSQL的心得（七）--查询(2)

时间:2014-08-05 02:06来源:网络整理作者:网络点击: 次

分享到：

根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息 SELECT s_id ,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id HAVING COUNT(f_name)1 在group by中使用

根据s_id对fruits表中的数据进行分组，并显示水果种类大于1的分组信息

SELECT s_id ,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id HAVING COUNT(f_name)>1

在group by中使用with rollup

SELECT s_id ,COUNT(1) AS total FROM fruits GROUP BY s_id WITH ROLLUP

增加了最后一行，7表示total列的所有值的总和

而rollup关键字在SQLSERVER里面也有，详见：SQLSERVER中的ALL、PERCENT、CUBE关键字、ROLLUP关键字和GROUPING函数

注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的！

limit限制查询结果的数量

在SQLSERVER中是使用TOP关键字，而在MYSQL中是使用LIMIT关键字

LIMIT[位置偏移量]，行数
第一个“位置偏移量”参数指示MYSQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”

将会从表中第一条记录开始（第一条记录的位置偏移量是0，第二天记录的位置偏移量是1......以此类推）

第二个参数“行数”指示返回的记录条数

SELECT * FROM fruits

SELECT * FROM fruits LIMIT 4,3

上面结果返回从第5条记录行（因为从0开始数）开始之后的3条记录

注意：在MYSQL5.6中可以使用 LIMIT 4 OFFSET 3 ，意思是获取从第5行记录开始的3条记录，和 LIMIT 4,3 返回的结果是一样的

在SQLSERVER2012里面开始支持类似MYSQL的这种语法，但是需要按某一列先排序，而不像MYSQL那样不用按照某一列排序

USE [sss]
GO

CREATE TABLE fruits
(
  f_id CHAR(10) NOT NULL ,
  s_id INT NOT NULL ,
  f_name CHAR(255) NOT NULL ,
  f_price DECIMAL(8, 2) NOT NULL ,
  PRIMARY KEY ( f_id )
)

INSERT INTO fruits
    ( f_id ,
     s_id ,
     f_name ,
     f_price
    )
    SELECT 'a1' ,
        101 ,
        'apple' ,
        5.2
    UNION ALL
    SELECT 'b1' ,
        102 ,
        'blackberry' ,
        5.2
    UNION ALL
    SELECT 'bs1' ,
        105 ,
        'orange' ,
        5.2
    UNION ALL
    SELECT 'bs2' ,
        103 ,
        'melon' ,
        5.2
    UNION ALL
    SELECT 't1' ,
        106 ,
        'banana' ,
        5.2
    UNION ALL
    SELECT 'a6' ,
        101 ,
        'cherry' ,
        6
    UNION ALL
    SELECT 'a8' ,
        102 ,
        'coconut' ,
        7

先按s_id排序，然后返回第5行开始的3条记录

SELECT *
FROM  [dbo].[fruits]
ORDER BY [s_id] ASC
    OFFSET 4 ROWS
FETCH NEXT 3 ROWS ONLY;

虽然没有MYSQL那么方便，不过也算是一种进步，而对于OFFSET FETCH NEXT的性能可以参考宋沄剑的文章：

SQL Server2012 T-SQL对分页的增强尝试

子查询

子查询这个特性从MYSQL4.1开始引入。

插入测试数据

CREATE TABLE tbl1(num1 INT NOT NULL);
CREATE TABLE tbl2(num2 INT NOT NULL)

INSERT INTO tbl1 VALUES(1),(4),(13),(27);
INSERT INTO tbl2 VALUES(6),(14),(11),(20)

ANY关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较为TRUE，则返回TRUE

返回tbl2表的所有num2列，然后将tbl1中的num1的值与之进行比较，只要大于num2的任何一个值，即为符合查询条件的结果

SELECT num1 FROM tbl1 WHERE num1>ANY(SELECT num2 FROM tbl2)

ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE，则返回TRUE

SELECT num1 FROM tbl1 WHERE num1>ALL(SELECT num2 FROM tbl2)

在SQLSERVER中也可以使用ANY和ALL关键字

USE [sss]
GO
CREATE TABLE tbl1(num1 INT NOT NULL)
CREATE TABLE tbl2(num2 INT NOT NULL)

INSERT INTO tbl1 VALUES(1),(4),(13),(27)
INSERT INTO tbl2 VALUES(6),(14),(11),(20)

SELECT num1 FROM tbl1 WHERE num1>ANY(SELECT num2 FROM tbl2)
SELECT num1 FROM tbl1 WHERE num1>ALL(SELECT num2 FROM tbl2)

结果是一样的