时间戳

首页 > 解决方案 > sql中的求和列

sql-server - sql中的求和列

问题描述

我需要一点帮助，基本上，我正在为酒店管理创建一个数据库，请查看我的查询。所以现在，最后，我想对我遇到的可用服务进行总计。这里的任何人都可以帮我解决这个问题，如果我在某个地方错了，请告诉我，因为我仍在学习 Microsoft SQL。提前致谢

Create Database CustRoomBooking
use CustRoomBooking

drop table RoomsTypes
CREATE TABLE RoomTypes
(
ID int Not NULL,
type varchar(50) not NULL,
PRIMARY KEY (ID)
)

CREATE TABLE Pricing
(
service_ID int not NULL,
price int not null,
PRIMARY KEY(Service_ID)
)


drop table Rooms
create Table Rooms
(
ID int Not NULL,
floor int not NULL,
bed int not NULL,
roomType int not NULL,
roomPrice int not NULL, 
PRIMARY KEY (ID),
FOREIGN KEY (roomType) REFERENCES RoomTypes(ID),
FOREIGN KEY (roomPrice) REFERENCES Pricing(Service_ID)
)


Drop Table Customers
create Table Customers
(
ID int Not NULL,
name varchar(100) not NULL,
cnic varchar (13) not null,
age int not null,
phone varchar(11) not null,
gender varchar(6) not null,
PRIMARY KEY (ID)
)


drop table Booking
Create Table Booking
(
ID int not NULL,
room_ID int not NULL,
cust_ID int not NULL,
checkIN date not NULL,
checkOUT date not NULL,
booking_status varchar(100) not NULL,
booking_date date not NULL,
PRIMARY KEY (ID),
FOREIGN KEY (room_ID) REFERENCES Rooms(ID),
FOREIGN KEY (cust_ID) REFERENCES Customers(ID)

)

drop table Billing
Create Table Billing 
(
ID int NOT NULL,
room_ID int not NULL,
service_price int not NULL,
cust_ID int not NULL,
credit_card varchar(20) not NULL,
payment_date Date not NULL,
status varchar(50) not NULL, 

FOREIGN KEY (room_ID) REFERENCES Rooms(ID),
FOREIGN KEY (service_price) REFERENCES Pricing(service_ID),
FOREIGN KEY (cust_ID) REFERENCES Customers(ID)
)

CREATE TABLE Services
(
ID int not null,
service varchar (100),
price int not NULL,
PRIMARY KEY(ID),

FOREIGN KEY(price) REFERENCES Pricing(service_ID)

)

CREATE TABLE ServicesAvailed
(
ID int not null,
cust_ID int not NULL,
service int not NULL,
price int not NULL,

PRIMARY KEY(ID),
FOREIGN KEY(cust_ID) REFERENCES Customers(ID),
FOREIGN KEY(service) REFERENCES Services(ID),
FOREIGN KEY(price) REFERENCES Pricing(service_ID)
)

insert into ServicesAvailed
values
(1,1,1,1),
(2,1,2,2)


insert into RoomTypes
Values
(1,'Standard'),
(2,'Moderate '),
(3,'Deluxe ')

insert into Services
Values
(1,'Laundry',1),
(2,'Airport Transport',2),
(3,'Standard Room',3),
(4,'Moderate Room',4),
(5,'Deluxe Room',5)


insert into Pricing
Values
(1,1000),
(2,2000),
(3,2000),
(4,4000),
(5,6000)

insert into Rooms
Values
(1,0,2,1,3),
(2,0,2,1,3),
(3,1,3,2,4),
(4,1,3,2,4),
(5,2,4,3,5),
(6,2,4,3,5)

insert into Customers
Values
(1,'Hamza','12345',20,'98765','Male'),
(2,'Ahmed','12345',20,'98765','Male'),
(3,'Irfan','12345',20,'98765','Male')

Insert Into Booking
Values
(1,1,1,'2020-12-31','2021-1-1','Booked','2020-12-31'),
(2,2,2,'2020-12-31','2021-1-1','Booked','2020-12-31')

Insert Into Billing 
Values
(1,1,1,1,'5657446546','2020-12-31','Paid'),
(2,2,2,2,'5657446546','2020-12-31','Paid')

select * from Billing
select * from Booking
select * from Customers
select * from RoomTypes
select * from Rooms
select * from RoomPrices
select * from Booking
select * from Billing




select r.ID as RoomNumber,r.floor,r.bed,rt.*,p.price from Rooms r
inner join RoomTypes rt on r.roomType=rt.ID
inner join Pricing p on r.roomPrice=p.service_ID





select b.ID as BookingID,c.ID as CustomerID,c.name,b.booking_date,b.booking_status,b.checkIN,b.checkOUT, r.ID as roomID,r.floor,r.bed,rt.type,rp.price from Booking b
inner join Customers c on b.cust_ID=c.ID
inner join Rooms r on b.room_ID=r.ID
inner join RoomTypes rt ON r.roomType=rt.ID
inner join Pricing rp ON r.roomPrice= rp.service_ID 
where c.name='Hamza'


select * from Billing


select c.ID as CustomerID,c.name,r.ID as roomID,r.floor,r.bed,rt.type,rp.price from Billing b
inner join Customers c on b.cust_ID=c.ID
inner join Rooms r on b.room_ID=r.ID
inner join RoomTypes rt ON r.roomType=rt.ID
inner join Pricing rp ON r.roomPrice= rp.service_ID 

select sa.ID as NumberOFService,c.ID as CustomerID,c.name,s.service,p.price from ServicesAvailed sa
inner join Customers c on sa.cust_ID=c.ID
inner join Services s on sa.service=s.ID
inner join Pricing p on sa.price=p.service_ID

标签： sql-serverdatabase

解决方案

我猜你的问题是你的最后一个查询不包括房间本身的价格。在这种情况下我会做的步骤：

  1. 将 [charged_date] 列添加到表 [ServicesAvailed] 中，并将其包含在主键中，因为客户可能会多次购买相同类型的服务

  2. 将触发器添加到 [Booking]，它使用 [checkIN] 和 [checkOUT] 列在 [ServicesAvailed] 中创建条目，这些条目实际上是房间价格。然后，您的最终查询将包括房价。

如果您不想在 [ServicesAvailed] 中的每一天都有相同的嘘声，您可以考虑增加一列 [multiplier]，它将基本服务价格乘以一个因子（如果是房间，这将是一个约会）。如果您愿意，您可以使用类似的逻辑来实现折扣等。

推荐阅读