SQL Server/T-SQL Tutorial/Table/Table alias
Содержание
Ambiguous column name during table join
<source lang="sql">
4> 5> 6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 4> GO (1 rows affected) 1> 2> 3> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 4> GO (1 rows affected) 1> 2> 3> 4> UPDATE Billings 5> SET CreditTotal = CreditTotal + 100 6> FROM(SELECT TOP 5 * FROM Billings) AS TopBillings 7> 8> GO Msg 209, Level 16, State 1, Server J\SQLEXPRESS, Line 5 Ambiguous column name "CreditTotal". 1> 2> 3> drop table Billings; 4> GO</source>
Column name with or without a table alias
<source lang="sql">
2> 3> CREATE TABLE stores( 4> stor_id char(4) NOT NULL, 5> stor_name varchar(40) NULL, 6> stor_address varchar(40) NULL, 7> city varchar(20) NULL, 8> state char(2) NULL, 9> zip char(5) NULL 10> ) 11> GO 1> insert stores values("1","B","567 Ave.","Tustin", "CA","92789") 2> insert stores values("2","N","577 St.", "Los Gatos","CA","96745") 3> insert stores values("3","T","679 St.", "Portland", "OR","89076") 4> insert stores values("4","F","89 St.", "Fremont", "CA","90019") 5> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE discounts( 4> discounttype varchar(40) NOT NULL, 5> stor_id char(4) NULL , 6> lowqty smallint NULL, 7> highqty smallint NULL, 8> discount dec(4,2) NOT NULL 9> ) 10> GO 1> 2> insert discounts values("Initial Customer", NULL, NULL, NULL, 10.5) 3> insert discounts values("Volume Discount", NULL, 100, 1000, 6.7) 4> insert discounts values("Customer Discount", "8042", NULL, NULL, 5.0) 5> GO (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SELECT discounttype, discount, s.stor_name 3> FROM discounts d 4> JOIN stores s 5> ON d.stor_id = s.stor_id 6> GO discounttype discount stor_name
-------- ----------------------------------------
(0 rows affected) 1> 2> drop table stores; 3> drop table discounts; 4> GO</source>
Query two tables with column in common
<source lang="sql">
4> CREATE TABLE Suppliers ( 5> SupplierID int NOT NULL , 6> CompanyName nvarchar (40) NOT NULL , 7> ContactName nvarchar (30) NULL , 8> ContactTitle nvarchar (30) NULL , 9> Address nvarchar (60) NULL , 10> City nvarchar (15) NULL , 11> Region nvarchar (15) NULL , 12> PostalCode nvarchar (10) NULL , 13> Country nvarchar (15) NULL , 14> Phone nvarchar (24) NULL , 15> Fax nvarchar (24) NULL , 16> HomePage ntext NULL 17> ) 18> GO 1> 2> 3> INSERT Suppliers VALUES(1,"L","N","Manager","L 10","Van",NULL,"2800","Paris","1114108","43844115",NULL) 4> INSERT Suppliers VALUES(2,"Z","D","Manager","V 22","Zaa",NULL,"9999 ZZ","USA"," 1212","(12345) 1210",NULL) 5> INSERT Suppliers VALUES(3,"K","A","Manager","V 12","Lap",NULL,"53120","Finland"," 10956",NULL,NULL) 6> INSERT Suppliers VALUES(4,"G","W","Tester", "1 Hill","Sydney","NSW","2042","Australia","(02) 555-5914","(021) 555-2222",null) 7> INSERT Suppliers VALUES(5,"M","J","Manager","2 St.","Mon","BC","H1J 1C3","Canada","(514) 555-9022",NULL,NULL) 8> INSERT Suppliers VALUES(6,"P","G","Administrator","V 153","Sal",NULL,"84100","Italy","(089) 6547665","(089) 1111111",NULL) 9> INSERT Suppliers VALUES(7,"E","M","Sales","22 Str","Mont",NULL,"71300","France","85.57.00.07",NULL,NULL) 10> INSERT Suppliers VALUES(8,"G","E","Sales","B Ave","Ann",NULL,"74000","France","38.76.98.06","38.76.98.58",NULL) 11> INSERT Suppliers VALUES(9,"F","C","Manager","1 Str","Ste","Calgary","J2S 7S8","Canada","(514) 555-2955","(514) 555-2921",NULL) 12> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE Products ( 4> ProductID int NOT NULL , 5> ProductName nvarchar (40) NOT NULL , 6> SupplierID int NULL , 7> CategoryID int NULL , 8> QuantityPerUnit nvarchar (20) NULL , 9> UnitPrice money NULL, 10> UnitsInStock smallint NULL, 11> UnitsOnOrder smallint NULL, 12> ReorderLevel smallint NULL, 13> Discontinued bit NOT NULL 14> ) 15> GO 1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6) 2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7) 3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0) 4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0) 5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0) 6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0) 7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0) 8> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> SELECT * 2> FROM Products, Suppliers 3> WHERE Products.SupplierID = Suppliers.SupplierID 4> GO ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued SupplierID CompanyName
ContactName ContactTitle Address City Region PostalCode Country
Phone Fax HomePage
---------------------------------------- ----------- ----------- -------------------- --------------------- ------------ ------------ ------------ ------------ ----------- ----------------
------------------------------ ------------------------------ ------------------------------------------------------------ --------------- --------------- ---------- ---------
------------------------ ------------------------ -----------------------------------------------------------------------------------------------------------------------------------------------
4 L 4 7 5 kg pkg. 10.0000 4 20 5 0 4 G
W Tester 1 Hill Sydney NSW 2042 Australia
(02) 555-5914 (021) 555-2222 NULL
(1 rows affected) 1> 2> drop table Products; 3> drop table Suppliers; 4> GO</source>
Reference column with table name
<source lang="sql">
6> CREATE TABLE Products ( 7> ProductID int NOT NULL , 8> ProductName nvarchar (40) NOT NULL , 9> SupplierID int NULL , 10> CategoryID int NULL , 11> QuantityPerUnit nvarchar (20) NULL , 12> UnitPrice money NULL, 13> UnitsInStock smallint NULL, 14> UnitsOnOrder smallint NULL, 15> ReorderLevel smallint NULL, 16> Discontinued bit NOT NULL 17> ) 18> GO 1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6) 2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7) 3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0) 4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0) 5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0) 6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0) 7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0) 8> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE Suppliers ( 3> SupplierID int NOT NULL , 4> CompanyName nvarchar (40) NOT NULL , 5> ContactName nvarchar (30) NULL , 6> ContactTitle nvarchar (30) NULL , 7> Address nvarchar (60) NULL , 8> City nvarchar (15) NULL , 9> Region nvarchar (15) NULL , 10> PostalCode nvarchar (10) NULL , 11> Country nvarchar (15) NULL , 12> Phone nvarchar (24) NULL , 13> Fax nvarchar (24) NULL , 14> HomePage ntext NULL 15> ) 16> GO 1> 2> 3> INSERT Suppliers VALUES(1,"L","N","Manager","L 10","Van",NULL,"2800","Paris","1114108","43844115",NULL) 4> INSERT Suppliers VALUES(2,"Z","D","Manager","V 22","Zaa",NULL,"9999 ZZ","USA"," 1212","(12345) 1210",NULL) 5> INSERT Suppliers VALUES(3,"K","A","Manager","V 12","Lap",NULL,"53120","Finland"," 10956",NULL,NULL) 6> INSERT Suppliers VALUES(4,"G","W","Tester", "1 Hill","Sydney","NSW","2042","Australia","(02) 555-5914","(021) 555-2222",null) 7> INSERT Suppliers VALUES(5,"M","J","Manager","2 St.","Mon","BC","H1J 1C3","Canada","(514) 555-9022",NULL,NULL) 8> INSERT Suppliers VALUES(6,"P","G","Administrator","V 153","Sal",NULL,"84100","Italy","(089) 6547665","(089) 1111111",NULL) 9> INSERT Suppliers VALUES(7,"E","M","Sales","22 Str","Mont",NULL,"71300","France","85.57.00.07",NULL,NULL) 10> INSERT Suppliers VALUES(8,"G","E","Sales","B Ave","Ann",NULL,"74000","France","38.76.98.06","38.76.98.58",NULL) 11> INSERT Suppliers VALUES(9,"F","C","Manager","1 Str","Ste","Calgary","J2S 7S8","Canada","(514) 555-2955","(514) 555-2921",NULL) 12> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SELECT Products.*, Suppliers.SupplierID 3> FROM Products 4> INNER JOIN Suppliers 5> ON Products.SupplierID = Suppliers.SupplierID 6> 7> GO ProductID ProductName SupplierID CategoryID QuantityPerUnit UnitPrice UnitsInStock UnitsOnOrder ReorderLevel Discontinued SupplierID
---------------------------------------- ----------- ----------- -------------------- --------------------- ------------ ------------ ------------ ------------ -----------
4 L 4 7 5 kg pkg. 10.0000 4 20 5 0 4
(1 rows affected) 1> 2> drop table Products; 3> GO 1> 2> drop table Suppliers; 3> GO</source>
Table alias during table join
<source lang="sql">
4> CREATE TABLE Customers ( 5> CustomerID nchar (5) NOT NULL , 6> CompanyName nvarchar (40) NOT NULL , 7> ContactName nvarchar (30) NULL , 8> ContactTitle nvarchar (30) NULL , 9> Address nvarchar (60) NULL , 10> City nvarchar (15) NULL , 11> Region nvarchar (15) NULL , 12> PostalCode nvarchar (10) NULL , 13> Country nvarchar (15) NULL , 14> Phone nvarchar (24) NULL , 15> Fax nvarchar (24) NULL 16> ) 17> GO 1> 2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111") 3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL) 4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444") 5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55") 6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777") 7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32") 8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99") 9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41") 10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745") 11> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE Orders ( 4> OrderID int NOT NULL , 5> CustomerID nchar (5) NULL , 6> EmployeeID int NULL , 7> OrderDate datetime NULL , 8> RequiredDate datetime NULL , 9> ShippedDate datetime NULL , 10> ShipVia int NULL , 11> Freight money NULL DEFAULT (0), 12> ShipName nvarchar (40) NULL , 13> ShipAddress nvarchar (60) NULL , 14> ShipCity nvarchar (15) NULL , 15> ShipRegion nvarchar (15) NULL , 16> ShipPostalCode nvarchar (10) NULL , 17> ShipCountry nvarchar (15) NULL 18> ) 19> GO 1> 2> 3> INSERT INTO Orders VALUES (10248,"1",5,"7/4/1996","8/1/2001","7/16/2001",3,32.38,"V","A","R", NULL,N"51100","France") 7> go 1> 2> 3> 4> 5> SELECT c.CustomerID, CompanyName 6> FROM Customers c 7> LEFT OUTER JOIN Orders o 8> ON c.CustomerID = o.CustomerID 9> WHERE o.CustomerID IS NULL 10> 11> GO CustomerID CompanyName
----------------------------------------
1 A 2 M 3 H 4 B 5 S 6 F 7 C 8 P 9 D (9 rows affected) 1> 2> drop table orders; 3> drop table customers; 4> GO 1> 2></source>
