SELECT productname, chargetype, COUNT(*) AS pici, SUM(CASE jyjg WHEN 0 THEN 1 ELSE 0 END) AS buhegeshu
FROM task
WHERE (productname <> '') and datepart(year,takedate)='2009'
GROUP BY productname, chargetype
ORDER BY productname
dataset1 按照批次进行汇总的查询
SELECT productname, chargetype, COUNT(*) AS pici, SUM(CASE jyjg WHEN 0 THEN 1 END) AS buhegeshu, chargedcompany
FROM task
WHERE (productname <> '') and datepart(year,takedate)='2009'
GROUP BY productname, chargetype, chargedcompany
ORDER BY productname
dataset2 按照企业汇总的查询
现在是dataset2 的记录条数比dataset1 的条数多对dataset2 进行筛选 我这样:
For i = 0 To dataset1.Tables(0).Rows.Count - 1
dataset2.Tables(0).Select("productname='" + dataset1.Tables(0).Rows(i)("productname") + "',chargetype='" + dataset1.Tables(0).Rows(i)("chargetype") + "'")
Next
筛选的条件是要和dataset1 中的产品名称和类型一样。。
但是这样筛选 返回的是一个一维的东西,要怎么把这些在组合成一个和dataset1.tables(0).rows 相同的表呢??
还有sql 语句中的case when then 可以sum吗??
FROM task
WHERE (productname <> '') and datepart(year,takedate)='2009'
GROUP BY productname, chargetype
ORDER BY productname
dataset1 按照批次进行汇总的查询
SELECT productname, chargetype, COUNT(*) AS pici, SUM(CASE jyjg WHEN 0 THEN 1 END) AS buhegeshu, chargedcompany
FROM task
WHERE (productname <> '') and datepart(year,takedate)='2009'
GROUP BY productname, chargetype, chargedcompany
ORDER BY productname
dataset2 按照企业汇总的查询
现在是dataset2 的记录条数比dataset1 的条数多对dataset2 进行筛选 我这样:
For i = 0 To dataset1.Tables(0).Rows.Count - 1
dataset2.Tables(0).Select("productname='" + dataset1.Tables(0).Rows(i)("productname") + "',chargetype='" + dataset1.Tables(0).Rows(i)("chargetype") + "'")
Next
筛选的条件是要和dataset1 中的产品名称和类型一样。。
但是这样筛选 返回的是一个一维的东西,要怎么把这些在组合成一个和dataset1.tables(0).rows 相同的表呢??
还有sql 语句中的case when then 可以sum吗??
Dim newRow As DataRow
For i = 0 To dataset1.Tables(0).Rows.Count - 1
For Each row In dataset2.Tables(0).Select("productname='" + dataset1.Tables(0).Rows(i)("productname") + "',chargetype='" + dataset1.Tables(0).Rows(i)("chargetype") + "'")
newRow = datatable.NewRow()
newRow.ItemArray=row.ItemArray
datatable.Rows.Add(newRow)
Next
Next新建的那个datatable 就是你要的