如何按日期顺序从2个不同的表中选择数据然后按行添加每个数据行按Asc日期排序到第三个表 [英] How Do I Select Data From 2 Different Tables Order By Dates Then Add Each Data Row By Row Into A Third Table Order By Asc Date

问题描述

这就是我想要实现的目标。我有2张桌子购买和销售



购买表有这些记录



Sno Date购买数量贸易

1 20-02-2014 CUP 10 Bought

2 21-02-2014 SPOON 20 Bought

3 01-03-2014 PLATE 15 Bought

4 10-03-2014 SPOON 20购买



和销售有这些记录



Sno日期已售出数量贸易

1 21-02-2014刀2已售出

2 21-02-2014 SPOON 8已售

3 01-03-2014第5页售出

4 11-03-2014 SPOON 9已售出



现在如何复制或选择来自两个表的数据，并根据相应的日期逐行插入第三个表格中的历史记录



示例



历史表应如下所示

Sno Date Item数量贸易

1 19-01-2014刀2已售

2 20 -02-2014 CUP 10 Bought

3 21-02-2014 SPOON 20 Bought

4 21-02-2014 SPOON 8已售出

5 01-03-2014第15页买了

6 01-03-2014第5页已售出

7 10-03-2014 SPOON 20购买

8 11-03-2014 SPOON 9已售出





我需要这个。任何帮助将不胜感激。这是我的电子邮件地址[e-mail]



先谢谢。

This is what i am trying to achieve. i have 2 tables Purchase and Sales

Purchase Table Has these records

Sno Date Bought Qty Trade
1 20-02-2014 CUP 10 Bought
2 21-02-2014 SPOON 20 Bought
3 01-03-2014 PLATE 15 Bought
4 10-03-2014 SPOON 20 Bought

And Sales Has these Records

Sno Date Sold Qty Trade
1 21-02-2014 Knife 2 Sold
2 21-02-2014 SPOON 8 Sold
3 01-03-2014 PLATE 5 Sold
4 11-03-2014 SPOON 9 Sold

Now How do i copy or select Data from the two Tables and Insert into a Third Table called History row by row based on the Corresponding Dates

example

History Table should look like this
Sno Date Item Qty Trade
1 19-01-2014 knife 2 Sold
2 20-02-2014 CUP 10 Bought
3 21-02-2014 SPOON 20 Bought
4 21-02-2014 SPOON 8 Sold
5 01-03-2014 PLATE 15 Bought
6 01-03-2014 PLATE 5 Sold
7 10-03-2014 SPOON 20 Bought
8 11-03-2014 SPOON 9 Sold


Please i need this. any Help will be Appreciated. this is my email Address [e-mail]

Thanks in Advance.

推荐答案

您的日期字段是字符串，需要将其转换为日期类型才能正确排序：

Your date field is of string, need to convert it to Date type in order to sort properly:

select date, bought as item, qty, trade into history from purchase;
insert into history 
select date, sold, qty, trade from sales;
select ROW_NUMBER() OVER(ORDER BY CONVERT(date, date, 105)) AS sno, * from history

右边，日期字段应为日期或日期时间类型。

By right, a date field should be of date or datetime type.

这篇关于如何按日期顺序从2个不同的表中选择数据然后按行添加每个数据行按Asc日期排序到第三个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！