Use defined variable in new select clause : Select « Select Clause « SQL / MySQL

1. SQL / MySQL
2. Select Clause
3. Select

Use defined variable in new select clause

  
/*
mysql> SELECT * FROM report;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  4.45 |
|    0001 | B      |  5.45 |
|    0002 | A      | 16.67 |
|    0003 | B      |  6.12 |
|    0003 | C      |  2.78 |
|    0003 | D      |  2.34 |
|    0004 | D      | 21.29 |
+---------+--------+-------+
7 rows in set (0.00 sec)

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM report;
+------------------------+------------------------+
| @min_price:=MIN(price) | @max_price:=MAX(price) |
+------------------------+------------------------+
|                   2.34 |                  21.29 |
+------------------------+------------------------+
1 row in set (0.00 sec)

mysql> /* Using User Variables */
mysql> SELECT * FROM report WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  2.34 |
|    0004 | D      | 21.29 |
+---------+--------+-------+
2 rows in set (0.00 sec)


*/
Drop table report;  


CREATE TABLE report (
       article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
       dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
       price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
       PRIMARY KEY(article, dealer));


INSERT INTO report VALUES (1,'A',4.45),
                        (1,'B',5.45),
                        (2,'A',16.67),
                        (3,'B',6.12),
                        (3,'C',2.78),
                        (3,'D',2.34),
                        (4,'D',21.29);
    
SELECT * FROM report;    

SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM report;
  
/* Using User Variables */
SELECT * FROM report WHERE price=@min_price OR price=@max_price;


           
         
    
  

Related examples in the same category