如何使用sql表显示数据 [英] How to display data using sql table

问题描述

以下是我的表格结构和数据.

Hi,
Below is my tables structure and data.

CREATE TABLE [dbo].[PM_Ledger](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Year] [int] NULL,
    [Type] [varchar](5) NULL,
    [code] [int] NULL,
    [Quantity] [float] NULL,
 CONSTRAINT [PK_PM_Ledger] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[PM_Ledger] ON
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (1, 2010, N'R', 1001, 2500)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (3, 2010, N'R', 1002, 2800)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (4, 2010, N'I', 1001, 2100)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (5, 2010, N'R', 1001, 2800)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (6, 2010, N'I', 1002, 2500)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (7, 2011, N'R', 1001, 2500)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (8, 2011, N'I', 1001, 2800)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (9, 2011, N'R', 1002, 2800)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (10, 2011, N'I', 1002, 2500)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (11, 2012, N'R', 1001, 2000)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (12, 2012, N'I', 1001, 1500)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (13, 2012, N'R', 1002, 1800)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (14, 2012, N'I', 1002, 1200)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (15, 2013, N'R', 1001, 1500)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (16, 2013, N'I', 1001, 1800)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (17, 2013, N'R', 1002, 1200)
INSERT [dbo].[PM_Ledger] ([Id], [Year], [Type], [code], [Quantity]) VALUES (18, 2013, N'I', 1002, 1450)
SET IDENTITY_INSERT [dbo].[PM_Ledger] OFF

我想要以下格式的数据，

期初数量是(前一年(用户输入的年份-1))((类型=``R''的总和(数量)-类型=''I''的总和(数量))
用户输入年份的结束数量为期初数量为(Sum(Quantity)其中Type =''R''-Sum(Quantity)其中Type =''I'')

如果我输入2010，则希望使用以下格式的数据(2010年之前，我们没有数据，这就是为什么OpeningQuantity为0的原因)

代码      OpeningQuantity  ClosingQuantity
1001     0       3200
1002     0       300


如果我输入2011，我希望数据采用以下格式

代码   YearOpeningQuantity   ClosingQuantity
1001     3200       2900
1002     300       600


我输入2012，我想要以下格式的数据

代码    OpeningQuantity  ClosingQuantity
1001     2900       3400
1002     600        1200< 1200


如果我输入2013，则需要以下格式的数据

代码    OpeningQuantity  ClosingQuantity
1001     3400       3100
1002     600       950< 950



谢谢

I want the data in below format,

Opening Quantity is (Sum(Quantity) where Type=''R'' - Sum(Quantity) where Type=''I'') up to previous year(User entered Year -1)
Closing Qunatity is Opening Quantity is (Sum(Quantity) where Type=''R'' - Sum(Quantity) where Type=''I'') for User entered Year

If i enter 2010, i want the data in below format(before 2010 we have no data, thats why OpeningQuantity is 0)

Code   Year    OpeningQuantity  ClosingQuantity
1001   2010   0      3200
1002   2010   0      300


If i enter 2011, i want the data in below format

Code   YearOpeningQuantity   ClosingQuantity
1001   2011   3200      2900
1002   2011   300      600


i enter 2012, i want the data in below format

Code   Year   OpeningQuantity   ClosingQuantity
1001   2012   2900      3400
1002   2012   600      1200



If i enter 2013, i want the data in below format

Code   Year   OpeningQuantity   ClosingQuantity
1001   2013   3400      3100
1002   2013   600      950



Thanks

推荐答案

在编写子句时确实很好(很好，几乎)-我想知道您在sql中停止了什么工作.
看这里:

As you formulated your clauses really good (well, almost) - I am wondering what have you stopped in formulating it in sql.
Look here:

declare @year int;
set @year=2011;
SELECT distinct(code), @year,
 (select sum(Quantity) from PM_Ledger I where Type='R' and "Year" < @year and I.code=L.code) - (select sum(Quantity) from PM_Ledger I where Type='I' and "Year" < @year and I.code=L.code) as OpeningQuantity,
 (select sum(Quantity) from PM_Ledger I where Type='R' and "Year" <= @year and I.code=L.code) - (select sum(Quantity) from PM_Ledger I where Type='I' and "Year" <= @year and I.code=L.code) as ClosingQuantity
from PM_Ledger L

这篇关于如何使用sql表显示数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！