《SQL Anywhere Studio 9开发指南》代码

ninetailsfox

3.10.1 IF and CASE Expressions [Top]
-----------------------------------------------------------------------------------
SELECT product.id,
       product.quantity,
       IF product.quantity < 20
       THEN 'Understocked'
       ELSE IF product.quantity > 50
            THEN 'Overstocked'
            ELSE ''
            ENDIF
       ENDIF AS level
  FROM product
ORDER BY product.quantity;
-----------------------------------------------------------------------------------
SELECT CASE region
          WHEN 'Western' THEN 1
          WHEN 'Central' THEN 2
          WHEN 'Eastern' THEN 3
          ELSE 0
       END      AS sort_order,
       region,
       COUNT(*) AS orders
  FROM sales_order
WHERE sort_order > 0
GROUP BY region
ORDER BY sort_order;
-----------------------------------------------------------------------------------
SELECT CASE
          WHEN sales_rep = 129
           AND region = 'Western'
             THEN 1
          WHEN region = 'Western'
             THEN 2
          WHEN region IN ( 'Eastern', 'Central' )
             THEN 3
          ELSE 0
       END AS sort_order,
       CASE sort_order
          WHEN 1 THEN 'Western 129'
          WHEN 2 THEN 'Other Western'
          WHEN 3 THEN 'Eastern and Central'
       END      AS breakdown,
       COUNT(*) AS orders
  FROM sales_order
WHERE sort_order > 0
GROUP BY sort_order
ORDER BY sort_order;
-----------------------------------------------------------------------------------

3.11 Top 15 Scalar Builtin Functions [Top]
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         UNSIGNED BIGINT NOT NULL,
   non_key_1     VARCHAR ( 100 ) NOT NULL,
   last_updated  TIMESTAMP NOT NULL,
   PRIMARY KEY ( key_1 ) );
INSERT t1 VALUES ( 1, '123.45', '2003-10-19 15:32.25.123' );
SELECT CAST ( key_1 AS VARCHAR ( 1 ) )         AS a,
       CAST ( key_1 AS VARCHAR )               AS b,
       CAST ( non_key_1 AS NUMERIC ( 10, 2 ) ) AS c,
       CAST ( non_key_1 AS NUMERIC )           AS d,
       CAST ( last_updated AS DATE )           AS e,
       CAST ( last_updated AS TIME )           AS f
  FROM t1;
-----------------------------------------------------------------------------------
SELECT EXPRTYPE ( '
SELECT CAST ( key_1 AS VARCHAR ( 1 ) )         AS a,
       CAST ( key_1 AS VARCHAR )               AS b,
       CAST ( non_key_1 AS NUMERIC ( 10, 2 ) ) AS c,
       CAST ( non_key_1 AS NUMERIC )           AS d,
       CAST ( last_updated AS DATE )           AS e,
       CAST ( last_updated AS TIME )           AS f
  FROM t1
', 2 );
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         UNSIGNED BIGINT NOT NULL,
   non_key_1     VARCHAR ( 100 ) NULL,
   non_key_2     TIMESTAMP NULL,
   PRIMARY KEY ( key_1 ) );
INSERT t1 VALUES ( 2, NULL, NULL );
SELECT COALESCE ( non_key_1, 'empty' )           AS a,
       COALESCE ( non_key_2, CURRENT TIMESTAMP ) AS b
  FROM t1;
-----------------------------------------------------------------------------------
SELECT customer.company_name                  AS company_name,
       product.name                           AS product_name,
       product.description                    AS product_description,
       COALESCE (
          SUM ( sales_order_items.quantity ),
          0.00 )                              AS quantity,
       COALESCE (
          SUM ( product.unit_price
              * sales_order_items.quantity ),
          0.00 )                              AS amount
  FROM ( customer
         CROSS JOIN product )
       LEFT OUTER JOIN
       ( sales_order
         INNER JOIN sales_order_items
                 ON sales_order_items.id = sales_order.id )
       ON  customer.id = sales_order.cust_id
       AND product.id  = sales_order_items.prod_id
WHERE customer.state = 'DC'
   AND product.name LIKE '%shirt%'
  GROUP BY customer.company_name,
       product.name,
       product.description
ORDER BY customer.company_name,
       product.name,
       product.description;
-----------------------------------------------------------------------------------
SELECT LEFT   ( '12345', 2 )    AS a,
       RIGHT  ( '12345', 2 )    AS b,
       SUBSTR ( '12345', 2, 3 ) AS c,
       SUBSTR ( '12345', 2 )    AS d;
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         UNSIGNED BIGINT NOT NULL,
   non_key_1     VARCHAR ( 100 ) NOT NULL,
   PRIMARY KEY ( key_1 ) );
INSERT t1 VALUES ( 1, '=A=B+C' );
SELECT SUBSTR ( non_key_1,
                LOCATE ( non_key_1, '=', 2 ) + 1 )
  FROM t1;
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         UNSIGNED BIGINT NOT NULL,
   non_key_1     NUMERIC ( 11, 2 ) NOT NULL,
   PRIMARY KEY ( key_1 ) );
INSERT t1 VALUES ( 1, 12345.78 );
INSERT t1 VALUES ( 2, 0.00 );
INSERT t1 VALUES ( 3, 12.34 );
SELECT RIGHT ( STRING ( REPEAT ( '0', 10 ), non_key_1 ), 10 ) AS a
  FROM t1
ORDER BY key_1;
-----------------------------------------------------------------------------------
MESSAGE STRING (
   'USER ',
   CURRENT USER,
   ' at ',
   CURRENT TIMESTAMP ) TO CONSOLE;
-----------------------------------------------------------------------------------

ninetailsfox

3.12 Boolean Expressions and the WHERE Clause [Top]
-----------------------------------------------------------------------------------

3.12.1 Comparison Predicates [Top]
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         UNSIGNED BIGINT NOT NULL,
   non_key_1     INTEGER NOT NULL,
   PRIMARY KEY ( key_1 ) );
INSERT t1 VALUES ( 1, 1 );
INSERT t1 VALUES ( 2, 2 );
SELECT *
  FROM t1
WHERE t1.non_key_1 = 2;
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         UNSIGNED BIGINT NOT NULL,
   non_key_1     INTEGER NOT NULL,
   PRIMARY KEY ( key_1 ) );
CREATE TABLE t2 (
   key_1         UNSIGNED BIGINT NOT NULL,
   non_key_1     INTEGER NOT NULL,
   PRIMARY KEY ( key_1 ) );
INSERT t1 VALUES ( 3, 5 );
INSERT t1 VALUES ( 4, 6 );
INSERT t2 VALUES ( 11, 999 );
INSERT t2 VALUES ( 22, 50 );
COMMIT;
SELECT *
  FROM t1
WHERE t1.non_key_1 = ANY ( SELECT t2.non_key_1 / 10
                              FROM t2 );
-----------------------------------------------------------------------------------
SELECT REWRITE ( '
SELECT *
  FROM t1
WHERE t1.non_key_1 = ANY ( SELECT t2.non_key_1 / 10
                              FROM t2 )' );
-----------------------------------------------------------------------------------
SELECT DISTINCT
       t1_1.key_1,
       t1_1.non_key_1
  FROM t2 as t2_1,
       t1 as t1_1
WHERE t1_1.non_key_1 = t2_1.non_key_1 / 10;
-----------------------------------------------------------------------------------

3.12.2 EXISTS Predicates [Top]
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL PRIMARY KEY,
   non_key_1     INTEGER NOT NULL );
CREATE TABLE t2 (
   key_1         INTEGER NOT NULL REFERENCES t1 ( key_1 ),
   key_2         INTEGER NOT NULL,
   non_key_1     INTEGER NOT NULL,
   PRIMARY KEY ( key_1, key_2 ) );
INSERT t1 VALUES ( 5, 5 );
INSERT t1 VALUES ( 6, 6 );
INSERT t1 VALUES ( 7, 7 );
INSERT t2 VALUES ( 6, 44, 77 );
INSERT t2 VALUES ( 6, 55, 88 );
SELECT *
  FROM t1
WHERE EXISTS ( SELECT *
                  FROM t2
                 WHERE t2.key_1 = t1.key_1 );
-----------------------------------------------------------------------------------
SELECT *
  FROM t1
WHERE NOT EXISTS ( SELECT *
                      FROM t2
                     WHERE t2.key_1 = t1.key_1 );
-----------------------------------------------------------------------------------

ninetailsfox

3.12.3 IN Predicates [Top]
-----------------------------------------------------------------------------------
SELECT emp_id,
       emp_fname,
       emp_lname
  FROM employee
WHERE emp_fname IN ( 'John', 'Paul', 'George', 'Ringo' )
ORDER BY emp_id;
-----------------------------------------------------------------------------------
SELECT emp_id,
       emp_fname,
       emp_lname
  FROM employee
WHERE emp_fname IN ( SELECT emp_lname
                        FROM employee )
    OR emp_lname IN ( SELECT emp_fname
                        FROM employee )
ORDER BY emp_id;
-----------------------------------------------------------------------------------

3.12.4 BETWEEN Predicates [Top]
-----------------------------------------------------------------------------------
SELECT id,
       cust_id,
       order_date
  FROM sales_order
WHERE order_date BETWEEN '2000-01-03' and '2000-01-07'
ORDER BY order_date;
-----------------------------------------------------------------------------------

ninetailsfox

3.15 GROUP BY ROLLUP [Top]
-----------------------------------------------------------------------------------
SELECT customer.id,
       sales_order.sales_rep,
       COUNT ( sales_order.sales_rep ) AS sales
  FROM ( SELECT *
           FROM customer
          WHERE customer.state IN ( 'OH' ) ) AS customer
       INNER JOIN
       ( SELECT *
           FROM sales_order
          WHERE sales_order.order_date
          BETWEEN '2000-03-01' AND '2000-05-31'  ) AS sales_order
       ON sales_order.cust_id = customer.id
GROUP BY ROLLUP ( customer.id,
       sales_order.sales_rep )
ORDER BY COALESCE ( customer.id, 99999999 ),
      COALESCE ( sales_order.sales_rep, 99999999 );
-----------------------------------------------------------------------------------
SELECT IF GROUPING ( customer.id ) = 1
          THEN 'Grand Total'
          ELSE STRING ( customer.id )
       ENDIF                                   AS customer_id,
       CASE
          WHEN GROUPING ( customer.id ) = 1
             THEN ''
          WHEN GROUPING ( sales_order.sales_rep ) = 1
             THEN 'Subtotal'
          ELSE STRING ( '', sales_order.sales_rep )
       END                                     AS sales_rep,
       COUNT ( sales_order.sales_rep )         AS sales,
       LIST ( sales_order.id, ', ' )           AS order_ids
  FROM ( SELECT *
           FROM customer
          WHERE customer.state IN ( 'OH' ) ) AS customer
       LEFT OUTER JOIN
       ( SELECT *
           FROM sales_order
          WHERE sales_order.order_date
          BETWEEN '2000-03-01' AND '2000-05-31'  ) AS sales_order
       ON sales_order.cust_id = customer.id
GROUP BY ROLLUP ( customer.id,
       sales_order.sales_rep )
ORDER BY customer_id,
      sales_rep;
-----------------------------------------------------------------------------------

ninetailsfox

3.16 HAVING Clause [Top]
-----------------------------------------------------------------------------------
SELECT customer.id  AS customer_id,
       COUNT(*)     AS sales
  FROM customer
       INNER JOIN sales_order
               ON sales_order.cust_id = customer.id
GROUP BY customer_id
HAVING customer_id >= 200
   AND sales >= 10
ORDER BY customer_id;
-----------------------------------------------------------------------------------
SELECT customer.id  AS customer_id,
       COUNT(*)     AS sales
  FROM customer
       INNER JOIN sales_order
               ON sales_order.cust_id = customer.id
WHERE customer_id >= 200
GROUP BY ROLLUP ( customer_id )
HAVING sales >= 10
ORDER BY customer_id;
-----------------------------------------------------------------------------------

ninetailsfox

3.17 ORDER BY [Top]
-----------------------------------------------------------------------------------
SELECT *
  FROM employee
ORDER BY 4, 3;
-----------------------------------------------------------------------------------
SELECT sales_order.order_date,
       COUNT(*) AS sales
  FROM sales_order
       INNER JOIN sales_order_items
               ON sales_order_items.id = sales_order.id
WHERE sales_order.order_date BETWEEN '2000-04-01' AND '2000-11-30'
GROUP BY sales_order.order_date
HAVING COUNT(*) >= 5
ORDER BY SUM ( sales_order_items.quantity ) DESC;
-----------------------------------------------------------------------------------

ninetailsfox

3.18 SELECT DISTINCT [Top]
-----------------------------------------------------------------------------------
SELECT DISTINCT
       prod_id,
       line_id
  FROM sales_order_items
WHERE line_id >= 3
ORDER BY prod_id,
       line_id;
-----------------------------------------------------------------------------------

3.19 FIRST and TOP [Top]
-----------------------------------------------------------------------------------
CREATE PROCEDURE p_pagefull (
   @page_number INTEGER )
BEGIN
   DECLARE @page_size   INTEGER;
   DECLARE @start       INTEGER;
   DECLARE @sql         LONG VARCHAR;
   SET @page_size = 10;
   SET @start = 1;
   SET @start = @start + ( ( @page_number - 1 ) * @page_size );
   SET @sql = STRING (
      'SELECT TOP ',
      @page_size,
      ' START AT ',
      @start,
      ' * FROM sales_order ORDER BY order_date' );
   EXECUTE IMMEDIATE @sql;
END;
CALL p_pagefull ( 15 );
-----------------------------------------------------------------------------------

ninetailsfox

3.20 NUMBER(*) [Top]
-----------------------------------------------------------------------------------
SELECT NUMBER(*) AS "#",
       STRING ( emp_lname, ', ', emp_fname ) AS full_name,
       STRING ( '(', LEFT ( phone, 3 ), ') ',
                SUBSTR ( phone, 4, 3 ), '-',
                RIGHT ( phone, 4 ) ) AS phone
  FROM employee
WHERE emp_lname LIKE 'd%'
ORDER BY emp_lname,
       emp_fname;
-----------------------------------------------------------------------------------
SELECT NUMBER(*)             AS "#",
       LEFT ( emp_lname, 1 ) AS letter,
       STRING ( emp_fname, ' ', emp_lname ) AS full_name
  INTO #t
  FROM employee
WHERE emp_lname LIKE 'D%'
ORDER BY emp_lname,
       emp_fname;
INSERT #t
SELECT NUMBER(*)             AS "#",
       LEFT ( emp_lname, 1 ) AS letter,
       STRING ( emp_fname, ' ', emp_lname ) AS full_name
  FROM employee
WHERE emp_lname LIKE 'E%'
ORDER BY emp_lname,
       emp_fname;
SELECT "#",
       full_name
  FROM #t
ORDER BY letter,
       "#";
-----------------------------------------------------------------------------------

ninetailsfox

3.21 INTO Clause [Top]
-----------------------------------------------------------------------------------
BEGIN
   DECLARE @table_name VARCHAR ( 128 );
   DECLARE @row_count  BIGINT;
   CHECKPOINT;
   SELECT FIRST
          table_name,
          count
     INTO @table_name,
          @row_count
     FROM SYSTABLE
    ORDER BY count DESC;
   MESSAGE STRING (
      @table_name,
      ' has the most rows: ',
      @row_count ) TO CONSOLE;
END;
-----------------------------------------------------------------------------------

ninetailsfox

.22 UNION, EXCEPT and INTERSECT [Top]
-----------------------------------------------------------------------------------
SELECT COUNT(*) AS count,
       LIST ( state ORDER BY state ) AS customer_states
  FROM ( SELECT DISTINCT state
           FROM customer )
       AS customer;
SELECT COUNT(*) AS count,
       LIST ( state ORDER BY state ) AS employee_states
  FROM ( SELECT DISTINCT state
           FROM employee )
       AS employee;
SELECT COUNT(*) AS count,
       LIST ( state ORDER BY state ) AS customer_except_employee
  FROM ( SELECT state
           FROM customer
         EXCEPT
         SELECT state
           FROM employee )
       AS customer_except_employee;
SELECT COUNT(*) AS count,
       LIST ( state ORDER BY state ) AS employee_except_customer
  FROM ( SELECT state
           FROM employee
         EXCEPT
         SELECT state
           FROM customer )
       AS employee_except_customer;
SELECT COUNT(*) AS count,
       LIST ( state ORDER BY state ) AS customer_xor_employee
  FROM ( ( SELECT state
             FROM customer
           EXCEPT
           SELECT state
             FROM employee )
       UNION ALL
         ( SELECT state
             FROM employee
           EXCEPT
           SELECT state
             FROM customer ) )
       AS customer_xor_employee;
SELECT COUNT(*) AS count,
       LIST ( state ORDER BY state ) AS customer_intersect_employee
  FROM ( SELECT state
           FROM customer
         INTERSECT
         SELECT state
           FROM employee )
       AS customer_intersect_employee;
SELECT COUNT(*) AS count,
       LIST ( state ORDER BY state ) AS customer_union_employee
  FROM ( SELECT state
           FROM customer
         UNION
         SELECT state
           FROM employee )
       AS customer_intersect_employee;
-----------------------------------------------------------------------------------
SELECT STRING ( customer.lname, ', ', customer.fname ) AS full_name,
       STRING ( '(', LEFT ( customer.phone, 3 ), ') ',
                SUBSTR ( customer.phone, 4, 3 ), '-',
                RIGHT ( customer.phone, 4 ) )          AS phone,
       'Customer'                                      AS relationship
  FROM customer
WHERE customer.lname LIKE 'k%'
UNION ALL
SELECT STRING ( employee.emp_lname, ', ', employee.emp_fname ),
       STRING ( '(', LEFT ( employee.phone, 3 ), ') ',
                SUBSTR ( employee.phone, 4, 3 ), '-',
                RIGHT ( employee.phone, 4 ) ),
       'Employee'
  FROM employee
WHERE employee.emp_lname LIKE 'k%'
ORDER BY 1;
-----------------------------------------------------------------------------------
SELECT customer.lname AS last_name
  INTO #last_name
  FROM customer
WHERE customer.lname LIKE 'k%'
UNION ALL
SELECT employee.emp_lname
  FROM employee
WHERE employee.emp_lname LIKE 'k%';
SELECT *
  FROM #last_name
ORDER BY 1;
-----------------------------------------------------------------------------------