创建sql表条目，日期为每个日期的列

本文介绍了创建sql表条目，日期为每个日期的列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

大家好，

我怎样设法做到这一点，

它需要显示我的sql条目是按日期在一列中，这意味着每个输入日期都将成为一个列并成为一个报告。

Hi all,

How can i manage to do this,
it would require to show my sql entries to be per date in a column, so it means that every entry date would become a column and be a report.

SELECT remittance_no, remit_date, messenger, item, item_value, rate.product, rate.product_value
FROM remittance
INNER JOIN rate
ON rate.product=remittance.item

样本输出：

remittance_no remit_date messenger item item_value product product_value

REMMKT00003 12/4/2015 MESSENGER 3 PRODX 150 PRODX 1.85

REMMKT00003 12/4/2015 MESSENGER 3 PRODX 150 PRODX 1.75

REMMKT00003 12/4/2015 MESSENGER 3 PRODX 150 PRODX 1.65

REMMKT00003 12/4/2015 MESSE NGER 3 PRODX 150 PRODX 1.5

REMMKT00004 12/5/2015 MESSENGER 3 PRODX 7 PRODX 1.85

REMMKT00004 12/5/2015 MESSENGER 3 PRODX 7 PRODX 1.75

REMMKT00004 12/5/2015 MESSENGER 3 PRODX 7 PRODX 1.65

REMMKT00004 12/5/2015 MESSENGER 3 PRODX 7 PRODX 1.5

REMMKT00004 12/6/2015 MESSENGER 3 PRODX 7 PRODX 1.85

REMMKT00004 12/6/2015 MESSENGER 3 PRODX 7 PRODX 1.75

REMMKT00004 12/6/2015 MESSENGER 3 PRODX 7 PRODX 1.65

REMMKT00004 12/6/2015 MESSENGER 3 PRODX 7 PRODX 1.5

REMMKT00004 12/7/2015 MESSENGER 3 PRODX 7 PRODX 1.85

REMMKT00004 12/7/2015 MESSENGER 3 PRODX 7 PRODX 1.75

REMMKT00004 12/7/2015 MESSENGER 3 PRODX 7 PRODX 1.65

REMMKT00004 12/8/2015 MESSENGER 3 PRODX 7 PRODX 1.85

REMMKT00004 12/8/2015 MESSENGER 3 PRODX 7 PRODX 1.75

REMMKT00004 12/8/2015 MESSENGER 3 PRODX 7 PRODX 1.65

每个相同日期应该有一列o utput，例如：产品| day1 | day2 |第3天| day4 | day5

非常感谢你，

raz

SAMPLE OUTPUT:

remittance_noremit_date messenger item item_value productproduct_value
REMMKT0000312/4/2015MESSENGER 3PRODX150PRODX1.85
REMMKT0000312/4/2015MESSENGER 3PRODX150PRODX1.75
REMMKT0000312/4/2015MESSENGER 3PRODX150PRODX1.65
REMMKT0000312/4/2015MESSENGER 3PRODX150PRODX1.5
REMMKT0000412/5/2015MESSENGER 3PRODX7PRODX1.85
REMMKT0000412/5/2015MESSENGER 3PRODX7PRODX1.75
REMMKT0000412/5/2015MESSENGER 3PRODX7PRODX1.65
REMMKT0000412/5/2015MESSENGER 3PRODX7PRODX1.5
REMMKT0000412/6/2015MESSENGER 3PRODX7PRODX1.85
REMMKT0000412/6/2015MESSENGER 3PRODX7PRODX1.75
REMMKT0000412/6/2015MESSENGER 3PRODX7PRODX1.65
REMMKT0000412/6/2015MESSENGER 3PRODX7PRODX1.5
REMMKT0000412/7/2015MESSENGER 3PRODX7PRODX1.85
REMMKT0000412/7/2015MESSENGER 3PRODX7PRODX1.75
REMMKT0000412/7/2015MESSENGER 3PRODX7PRODX1.65
REMMKT0000412/8/2015MESSENGER 3PRODX7PRODX1.85
REMMKT0000412/8/2015MESSENGER 3PRODX7PRODX1.75
REMMKT0000412/8/2015MESSENGER 3PRODX7PRODX1.65

each same date should have a single column output, ex: product | day1 | day2 | day3 | day4 | day5

Thank you so much,

raz

推荐答案

select remittance_no, messenger, item, item_value, product
	, isnull([2015-12-04],0) as day1
	, isnull([2015-12-05],0) as day2
	, isnull([2015-12-06],0) as day3
	, isnull([2015-12-07],0) as day4
	, isnull([2015-12-08],0) as day5
from
(
	select remittance_no, remit_date, messenger, item, item_value, rate.product, rate.product_value
	FROM remittance
	INNER JOIN rate
	ON rate.product=remittance.item
) as sourcedata
PIVOT
(
	sum(product_value) for remit_date in ([2015-12-04], [2015-12-05], [2015-12-06],[2015-12-07],[2015-12-08])
)  pvt

提供结果

REMMKT00003 MESSENGER 3	PRODX	150	PRODX	6.75	0	0	0	0
REMMKT00004 MESSENGER 3	PRODX	7	PRODX	0	6.75	6.75	5.25	5.25

尽管如此，您很可能不会知道最终结果中需要哪些日期。在这种情况下，您需要了解Dynamic Pivoting。这个 []包含了Itzik Ben-Gan动态构建枢轴列列表的方法示例。

CodeProject上有很多文章可以指导您连接到MySQL - 例如 [ ]

要使用DateTimePicker构建新查询，可以执行与后续代码类似的操作。我假设您想要dtp中日期前14天的数据加上dtp上的日期。

It is highly likely though, that you are not going to know which dates are required in the final result. In which case you need to know about Dynamic Pivoting. This post[^] includes an example of Itzik Ben-Gan's approach to building the pivot column list dynamically.

There are lots of articles here on CodeProject that can guide you through connecting to MySQL - e.g. Connecting to MySQL Database using C# and .NET[^]

To get your new query built using a DateTimePicker you can do something similar to the code that follows. I've assumed that you want the data for the 14 days preceding the date in the dtp plus the date on the dtp.

Dim sCommand As MySqlCommand = New MySqlCommand()
Dim sqlQuery As StringBuilder = New StringBuilder()

sqlQuery.Append("select MSGR,PRODNAME,RteVal,")

'Set up the start date for 14 days prior to the datepicker (i.e. datepicker date is 15th date)
Dim CalcDate As Date = DateTimePicker1.Value.Date.AddDays(-14)
For i As Integer = 1 To 15
    sqlQuery.Append(String.Format("sum(case when rmdte = @Date{0} then ProdVal else 0 end) `DAY {1}`,", 15 - i + 1, i))

    sCommand.Parameters.AddWithValue(String.Format("@Date{0}", i), CalcDate)
    CalcDate = CalcDate.AddDays(1)
Next

sqlQuery.Append("sum(ProdVal)*rteval as Total from")
sqlQuery.Append("select date(remit_date) as rmdte, messenger as Msgr, item as ProdName, item_value as ProdVal,")
sqlQuery.Append("b.product as RteProd,b.product_value as Rteval FROM remittance a JOIN rate b on a.item=b.product")
sqlQuery.Append("where a.messenger = 'MESSENGER 3' and b.ratecode = '5i.35' ) as SOURCEDATA group by ProdName")

sCommand.CommandText = sqlQuery.ToString()

这篇关于创建sql表条目，日期为每个日期的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！