CREATE PROCEDURE AuditGstBalance AS declare @hoteldate datetime select @hoteldate=lastday from hoteldate Delete From TodayGstBalance Where HotelDate=@HotelDate insert into TodayGstBalance select @hoteldate,* from TodayGstBalanceReport GO下面是有关视图TodayGstBalanceReport的代码 CREATE VIEW dbo.TodayGstBalanceReport AS SELECT TOP 100 PERCENT Gst.Account, Gst.GroupAccount, Gst.FirstName, Gst.RoomNo, Gst.Status, Gst.ArrivalDate, Gst.DepartureDate, ISNULL(SUM(f.Debit), 0) AS Debit, ISNULL(SUM(f.Credit), 0) AS Credit, a.Balance FROM dbo.Guests Gst LEFT OUTER JOIN dbo.Folio f ON Gst.Account = f.Account INNER JOIN dbo.Account a ON Gst.Account = a.Account WHERE (Gst.Status = 'I') AND (LEN(Gst.GroupAccount) = 0) OR (Gst.Status = 'O') AND (LEN(Gst.GroupAccount) = 0) AND (Gst.ArrivalDate = (SELECT LastDay FROM HotelDate)) GROUP BY Gst.Account, Gst.RoomNo, Gst.ArrivalDate, Gst.DepartureDate, a.Balance, Gst.FirstName, Gst.RoomNo, Gst.Status, Gst.GroupAccount ORDER BY Gst.Account
无需理会你试试看这个就知道了declare @test table ( a int ) insert into @test select 1 union all select nullselect sum(a) from @test
select 1 union all select null 两句是什么意思啊.我是新手,没见过union all
select 1 union all select null 两句是什么意思啊.我是新手,没见过union all --------------------------------- 插入值给表@TEST
select 1 union all select null 两句是什么意思啊.我是新手,没见过union all ------------------------------------------------------------------------------------ union专用于select语句之间,表示将后面select语句的结果集追加到前面的结果集中.如果是union all,则表示追加所有行到前面的结果集中,否则追加时将排除重复的行.
谢谢各位了,再问一个问题 select 1 union all中的1是取@test表中的第一列吗
1是常量,不是取@test表中的第一列,而是插入到@test表中的第一列. 1只是与表第1列序号巧合而已.例如这样: insert @test select 999 union all select 888 将999和888插入到@test表的第一列中.
declare @hoteldate datetime
select @hoteldate=lastday from hoteldate
Delete From TodayGstBalance Where HotelDate=@HotelDate
insert into TodayGstBalance select @hoteldate,* from TodayGstBalanceReport
GO下面是有关视图TodayGstBalanceReport的代码
CREATE VIEW dbo.TodayGstBalanceReport
AS
SELECT TOP 100 PERCENT Gst.Account, Gst.GroupAccount, Gst.FirstName, Gst.RoomNo,
Gst.Status, Gst.ArrivalDate, Gst.DepartureDate, ISNULL(SUM(f.Debit), 0) AS Debit,
ISNULL(SUM(f.Credit), 0) AS Credit, a.Balance
FROM dbo.Guests Gst LEFT OUTER JOIN
dbo.Folio f ON Gst.Account = f.Account INNER JOIN
dbo.Account a ON Gst.Account = a.Account
WHERE (Gst.Status = 'I') AND (LEN(Gst.GroupAccount) = 0) OR
(Gst.Status = 'O') AND (LEN(Gst.GroupAccount) = 0) AND (Gst.ArrivalDate =
(SELECT LastDay
FROM HotelDate))
GROUP BY Gst.Account, Gst.RoomNo, Gst.ArrivalDate, Gst.DepartureDate, a.Balance,
Gst.FirstName, Gst.RoomNo, Gst.Status, Gst.GroupAccount
ORDER BY Gst.Account
(
a int
)
insert into @test
select 1 union all
select nullselect sum(a) from @test
select null
两句是什么意思啊.我是新手,没见过union all
select null
两句是什么意思啊.我是新手,没见过union all
---------------------------------
插入值给表@TEST
select null
两句是什么意思啊.我是新手,没见过union all
------------------------------------------------------------------------------------
union专用于select语句之间,表示将后面select语句的结果集追加到前面的结果集中.如果是union all,则表示追加所有行到前面的结果集中,否则追加时将排除重复的行.
select 1 union all中的1是取@test表中的第一列吗
1只是与表第1列序号巧合而已.例如这样:
insert @test
select 999 union all
select 888
将999和888插入到@test表的第一列中.