Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- drop table [Product.Category]
- drop table Product
- drop table Category
- */
- create table Product
- (
- ProductId int identity primary key,
- ProductName nvarchar(50),
- ProductPrice money
- )
- create table Category
- (
- CategoryId int identity primary key,
- CategoryName nvarchar(50)
- )
- create table [Product.Category]
- (
- PcID int identity primary key,
- ProductId int,
- CategoryId int,
- foreign key (ProductId) references Product (ProductId),
- foreign key (CategoryId) references Category (CategoryId)
- )
- go
- insert into Product (ProductName, ProductPrice) values
- ('Молоко', 100),
- ('Хлеб', 40),
- ('Пирожок (уставший)', 20)
- insert into Category (CategoryName) values
- ('Жидкости'), ('Мучное'), ('По акции')
- insert into [Product.Category] (ProductId, CategoryId) values
- (1, 1), (2, 2), (3, 2), (3, 3)
- go
- -- непосредственная выборка:
- select p.*, c.CategoryName from Product p
- left join [Product.Category] pc on pc.ProductId = p.ProductId
- left join Category c on c.CategoryId = pc.CategoryId
- -- ну или же
- select p.*, string_agg(c.CategoryName, ', ') as Categories from Product p
- left join [Product.Category] pc on pc.ProductId = p.ProductId
- left join Category c on c.CategoryId = pc.CategoryId
- group by p.ProductId, p.ProductName, p.ProductPrice
- /*
- Вывод:
- ProductId ProductName ProductPrice CategoryName
- ----------- -------------------------------------------------- --------------------- --------------------------------------------------
- 1 Молоко 100,00 Жидкости
- 2 Хлеб 40,00 Мучное
- 3 Пирожок (уставший) 20,00 Мучное
- 3 Пирожок (уставший) 20,00 По акции
- (4 rows affected)
- ProductId ProductName ProductPrice Categories
- ----------- -------------------------------------------------- --------------------- --------------------------------------------------
- 1 Молоко 100,00 Жидкости
- 2 Хлеб 40,00 Мучное
- 3 Пирожок (уставший) 20,00 Мучное, По акции
- (3 rows affected)
- Completion time: 2021-11-18T15:36:12.2074363+03:00
- */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement