SQL数据提取和显示 [英] SQL Data extract and display

问题描述

我需要显示来自SQL DB的数据:

I need to display the data from SQL DB:

我有一个数据库，其中包含每个客户的贷款数量及其过去五年的当前余额. (日期部分包含年，月，日)

I have db with number of loans for each customer and their current balance for past five years. ( The date part contains year, month, day)

我需要在UI中显示数据，其中最大当前未付余额客户为等级1，并将显示在第一行中.第一列按等级显示客户.

I need to display the data in the UI with Max current unpaid balance customer as Rank number 1 and will be displayed in the first row. The first column display the customers by rank.

按年显示在第二组中的是客户持有的贷款数量.

The number of loans hold by the customer is displayed in the second group by year.

第三组按年份显示当前余额.

The third group display the current balance by year. (which is sum of balance for all loans by year)

第四组包含每年每个客户的排名.

The fourth group contains the Rank of each customer in each year.

最后一列包含每个客户的当前年份排名.

The last column contains the current year rank of each customer.

第一列应显示一组客户abc，其他客户应显示在不同组中.

The customers abc  should be displayed in one group, other customers displayed in a different group in the first column.

abc客户的排名高于其他客户.我需要为此的SQL查询和列架构

The abc customers have higher rank than other customers. I need sql query and column schema for this

推荐答案

您好wpf_xam1，

Hi wpf_xam1,

感谢您的信息.我认为，您需要存储i nitial客户数据， 交易 记录.这些数据可以查询其他数据.以下表格方案供您参考.

Thank you for your post. Per my opinion, you need to store the initial customer data and each transaction record. Other data could be queried by these data. Table schemes below are for your reference.

CREATE TABLE [dbo].[Customer]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [CustomerName] NVARCHAR(50) NOT NULL
)

CREATE TABLE [dbo].[TransactionRecord]
(
	[Id] INT NOT NULL PRIMARY KEY, 
    [CustomerId] INT NOT NULL, 
    [TransactionAccount] DECIMAL(18, 2) NOT NULL, 
    [TransactionDate] DATETIME NOT NULL
)

最好的问候，
王莉

Best Regards,
Li Wang

这篇关于SQL数据提取和显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！