SQL Server/T-SQL Tutorial/View/Query view — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 13:24, 26 мая 2010

Query a view with where and order by

   <source lang="sql">

2> CREATE TABLE titles( 3> title_id varchar(20), 4> title varchar(80) NOT NULL, 5> type char(12) NOT NULL, 6> pub_id char(4) NULL, 7> price money NULL, 8> advance money NULL, 9> royalty int NULL, 10> ytd_sales int NULL, 11> notes varchar(200) NULL, 12> pubdate datetime NOT NULL 13> ) 14> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO 1> 2> 3> CREATE VIEW ranked_sales (rank, title_id, ytd_sales, title) 4> AS 5> SELECT 6> (SELECT COUNT(DISTINCT T2.ytd_sales) FROM titles AS T2 7> WHERE T2.ytd_sales >= T1.ytd_sales ) AS rank, 8> title_id, 9> ytd_sales, 10> title 11> FROM titles AS T1 WHERE ytd_sales IS NOT NULL 12> GO 1> 2> SELECT * FROM ranked_sales WHERE rank <= 10 ORDER BY rank 3> GO rank title_id ytd_sales title


-------------------- ----------- --------------------------------------------------------------------------------
         1 1                           4095 Secrets
         1 2                           4095 The
         1 7                           4095 Any?
         2 4                           4072 Prolonged
         3 5                           3876 With
         4 3                           3336 Emotional
         5 6                           2032 Valley
         6 8                           1509 Fifty

1> 2> drop VIEW ranked_sales; 3> GO 1> 2> 3> drop table titles; 4> GO</source>