CREATE TABLE [dbo].[Tst_Order](
[ID] [int] NULL,
[Year] [int] NULL,
[Price] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
--表 Tst_Order [] 的数据
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (1, 2008, 100.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (2, 2008, 150.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (3, 2009, 120.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (4, 2009, 200.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (5, 2009, 300.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (6, 2010, 210.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (7, 2010, 300.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (8, 2010, 100.00)
GO
SELECT sum(price) as allPrice
FROM Tst_Order
where Year = 2010
group by year上面查询结果为
AllPrice
610
--------
然后我想查询的结果为:
以当前查询的同样where条件得出当前年和上一年的数据如结果为
PreYear ThisYear
620 610
那SQL要怎么写谢谢
declare @t1 float;declare @t2 floatSELECT @t1=sum(price) FROM Tst_Order
where Year = 2010 group by year
select @t2=sum(price) FROM Tst_Order
where Year = 2010-1 group by yearselect @t2 as PreYear , @t1 as ThisYear/*
PreYear ThisYear
---------------------- ----------------------
620 610
*/
[ID] [int] NULL,
[Year] [int] NULL,
[Price] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
--表 Tst_Order [] 的数据
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (1, 2008, 100.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (2, 2008, 150.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (3, 2009, 120.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (4, 2009, 200.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (5, 2009, 300.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (6, 2010, 210.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (7, 2010, 300.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (8, 2010, 100.00)
GOselect PreYear=(select SUM([Price]) from Tst_Order where [Year]=a.[Year]),
ThisYear=(select SUM([Price]) from Tst_Order where [Year]=a.[Year]-1)
from Tst_Order a where a.[Year] = 2010
group by [Year]
/*
PreYear ThisYear
--------------------------------------- ---------------------------------------
610.00 620.00
FROM Tst_Order
where Year = 2009
group by year)as preyear
FROM Tst_Order
where Year = 2010
group by year
CREATE TABLE [dbo].[Tst_Order](
[ID] [int] NULL,
[Year] [int] NULL,
[Price] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
--表 Tst_Order [] 的数据
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (1, 2008, 100.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (2, 2008, 150.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (3, 2009, 120.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (4, 2009, 200.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (5, 2009, 300.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (6, 2010, 210.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (7, 2010, 300.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (8, 2010, 100.00)
GOselect SUM([Price]),
ThisYear=(select SUM([Price]) from Tst_Order where [Year]=a.[Year]-1)
from Tst_Order a where a.[Year] = 2010
group by [Year]drop table Tst_Order/*610.00 620.00(1 行受影响)
[ID] [int] NULL,
[Year] [int] NULL,
[Price] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
--表 Tst_Order [] 的数据
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (1, 2008, 100.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (2, 2008, 150.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (3, 2009, 120.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (4, 2009, 200.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (5, 2009, 300.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (6, 2010, 210.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (7, 2010, 300.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (8, 2010, 100.00)
GO
SELECT
sum(case [Year] when 2010 - 1 then price else 0 end) as PreYear,
sum(case [Year] when 2010 then price else 0 end) as ThisYear
FROM Tst_Order/*
PreYear ThisYear
---------------------------------------- ----------------------------------------
620.00 610.00(所影响的行数为 1 行)
*/drop table Tst_Order
SELECT sum(case [year] when 2010-1 then price else 0 end) as preyear,
sum(case [year] when 2010 then price else 0 end) as thisyear
FROM Tst_Order group by year)aa/*
preyear thisyear
--------------------------------------- ---------------------------------------
620.00 610.00
*/
ThisYear=(select SUM([Price]) from Tst_Order where [Year]=a.[Year]-1)
from Tst_Order a where a.[Year] = 2010
group by [Year]
------------------------------------
我现在就是用这种方法,但因为取的两个年度的字段有很多
比如有
上年收入 上年支成 上年利润 上年公司人员 今年收入 今年支出 今年利润 今年公司人员如果一个个字段这样取出来感觉 效率不是很好
谢谢
[ID] [int] NULL,
[Year] [int] NULL,
[Price] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
--表 Tst_Order [] 的数据
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (1, 2008, 100.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (2, 2008, 150.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (3, 2009, 120.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (4, 2009, 200.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (5, 2009, 300.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (6, 2010, 210.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (7, 2010, 300.00)
INSERT INTO Tst_Order ([ID], [Year], [Price]) VALUES (8, 2010, 100.00)
GO
declare @year int
set @year =2010
select top 1
(select sum(price) from Tst_Order where Year = @year-1)as [PreYear],
(select sum(price) from Tst_Order where Year = @year)as ThisYear
from Tst_Order /*
PreYear ThisYear
---------------------- ----------------------
620.00 610.00
*/
sum(case [Year] when 2010 - 1 then price else 0 end) as PreYear,
sum(case [Year] when 2010 then price else 0 end) as ThisYear
FROM Tst_Order
-----------------
我有很多字段也要这样一条条拼出来的吗?
有没有像
Select Sum(收入),Sum(支出) From Order Where Year = 2009
Select Sum(收入),Sum(支出) From Order Where Year = 2010
上面两条记录表合成一条记录为:
上年收入 上年支出 今年收入 今年支出 收入增长率 支出增长率谢谢