如何为每个日期获取多行并将其插入数组中 [英] How do I get multiple rows and insert it in an Array for each date
问题描述
我需要使用一个查询来获取多行并将其插入到数组中。请参考下面的例子。
Ex。
I need use a query that'll get multiple rows and insert it in an Array. Please refer to the example below.
Ex.
[Day] | [Fruit]
01/02/2014 | {A, B, C, D}
01/03/2014 | {A, C, D}
01/04/2014 | {A, B, D, E}
01/05/2014 | {A, C}
01/06/2014 | {A, B, C, D, E}
01/07/2014 | {A}
这是我的代码：
Here's my code:
using (var conn = new SqlConnection("DataSource Here"))
{
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT DISTINCT [Fruit], [DateReceive] FROM [FruitsDB].[dbo].[dummy] WHERE [Location]=@loca AND [DateReceive] BETWEEN '2014-01-02' AND '2014-01-07' ORDER BY [DateReceive]";
cmd.Parameters.AddWithValue("@loca", DropDownList1.SelectedValue.ToString());
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var tmp = reader["Fruit"];
if (tmp != DBNull.Value || tmp2 != DBNull.Value)
{
ListBox1.Items.Add(tmp.ToString());
}
IList<string> list1 = new List<string>();
foreach (var items in ListBox1.Items)
{
list1.Add(items.ToString());
}
lbList.Text = string.Join(",", list1);
}
}
}
}
我希望你能帮助我们。
已解决
- 使用Christian G.的FOR XML查询
这里是解决方案
谢谢Christian Graus先生的帮助！
更换cmd .CommandText上面
I hope you can help me guys.
SOLVED
-Using Christian G.'s FOR XML query
HERE'S THE SOLUTION
Thank you Mr. Christian Graus for helping me!
replacement for cmd.CommandText above
SELECT DAY(o.DateReceive) AS DayReceive,
STUFF
(
(SELECT ',' + convert(varchar(99), s.Fruit)
FROM [FruitsDB].[dbo].[dummy] s
WHERE DAY(s.DateReceive) = DAY(o.DateReceive)
AND Location = 'Quezon'
GROUP BY s.Fruit
ORDER BY s.Fruit
FOR XML PATH('')
), 1, 1, ''
) AS Fruit
FROM [FruitsDB].[dbo].[dummy] o
WHERE o.DateReceive IS NOT NULL
GROUP BY Day(o.DateReceive)
结果：
RESULT:
[Day] | [Fruit]
2 | Apple, Orange, Grapes, Lemon
3 | Apple, Figs, Lemon
4 | Apple, Figs, Grapes, Strawberry
5 | Grapes, Lychee, Strawberry
6 | Apple, Orange, Grapes
7 | Apple, Figs, Grapes, Lychee
推荐答案
如果你知道你的水果列表，你可以使用pivot来获取每个水果的数量。您还可以使用FOR XML为每天创建水果列表。
这里 [ ^ ]是关于如何执行此操作的文章。
如果您发布SQL来创建表结构并将数据放入其中，我很乐意为您编写SQL。
你已经完成了这个：
If you know your list of fruits, you an use pivot to get numbers of each. You can also use FOR XML to create a list of fruits for each day.
Here[^] is my article on how to do this.
If you post SQL to create your table structure and put data in to it, I'd be happy to write the SQL for you.
You've done this":
SELECT distinct DAY(DateReceive) AS DayReceive,
STUFF
(
(SELECT ',' + convert(varchar(99), s.Fruit)
FROM [FruitsDB].[dbo].[dummy] s
WHERE DAY(s.DateReceive) = DAY(s.DateReceive)
GROUP BY s.Fruit
ORDER BY s.Fruit
FOR XML PATH('')
), 1, 1, ''
) AS Fruit
FROM [FruitsDB].[dbo].[dummy]
WHERE Location = 'Quezon'
注意你的where语句。将其更改为WHERE DAY（s.DateReceive）= DAY（DateReceive）或更好，为您的外表提供另一个别名并使用它。
我还认为你需要丢失DISTINCT，而是使用一个分组。我想你需要做groupby day（datereceive）。
Note your where statement. Change it to WHERE DAY(s.DateReceive) = DAY(DateReceive) or better yet, give your outer table another alias and use that.
I also think you need to lose the DISTINCT, and instead use a group by. You will need to do groupby day(datereceive), I think.
[现在进行下一阶段]
如果一个问题实际上是一大堆问题，QA格式就会被打破。问这是一个新问题，我很乐意回答。
看起来你不是，我要出去了。我将这些值存储在字典中，但另一个选项是这样的类：
class MyObject
（
public int id;
public List< string> Fruits;
）
和然后是一个List< MyObject>
显然，字典会将id映射到字符串列表。
"[Now Proceeding in Next Phase]"
The QA format gets broken if one question is actually a ton of questions. Ask this as a new question and I'll be happy to answer it.
Looks like you're not about, and I am going out. I'd store those values in a dictionary, but the other option is a class like this:
class MyObject
(
public int id;
public List<string> Fruits;
)
and then a List<MyObject>
Obviously, a dictionary would map an id to a list of strings, also.
这篇关于如何为每个日期获取多行并将其插入数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
