SELECT a.stcd as 编号,a.stnm as 名称, CONVERT(char(7), b.tm, 120) AS 年月,
旬=case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
,round(AVG(b.z),2) ,round(avg(b.q),2)
FROM tb_stbprp_b a,tb_river_r b
WHERE (a.stcd=b.stcd) AND
(DATEPART(yy, b.tm) = 2005) AND
(DATEPART(mm, b.tm) = 8) and datePart(hh,b.tm) in (2,8,14,20) and datepart(mi,b.tm)=0
group by a.stcd,a.stnm,convert(char(7),b.tm,120)
,case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
ORDER BY a.stcd,COUNT(DAY(b.tm)), COUNT(DATEPART(hh, b.tm))
我在页面上设置了一个textBox,用于输入年份,两个Dropdownlist,一个ddlmonth(1,2,3,4,5,6,7,8,9,10,11,12)用于选择月份,一个ddlxun(上旬,中旬,下旬)用于选择旬
,请问怎么样将textbox,ddlmonth,ddlxun的值应用到sql语句中。
请高手帮忙解答,100分不够,还可以再加!
12 个解决方案
#1
dim sql as string
sql = "SELECT a.stcd as 编号,a.stnm as 名称, CONVERT(char(7), b.tm, 120) AS 年月,
旬=case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
,round(AVG(b.z),2) ,round(avg(b.q),2)
FROM tb_stbprp_b a,tb_river_r b
WHERE (a.stcd=b.stcd) AND
(DATEPART(yy, b.tm) = "
+ textBox1.text +
") AND
(DATEPART(mm, b.tm) = "
+ ddlmonth.selecteditem.text +
") and datePart(hh,b.tm) in (2,8,14,20) and datepart(mi,b.tm)= "
+ ddlxun.selecteditem.text +
"group by a.stcd,a.stnm,convert(char(7),b.tm,120)
,case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
ORDER BY a.stcd,COUNT(DAY(b.tm)), COUNT(DATEPART(hh, b.tm))"
用上面的sql语句进行查询就可以了。
sql = "SELECT a.stcd as 编号,a.stnm as 名称, CONVERT(char(7), b.tm, 120) AS 年月,
旬=case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
,round(AVG(b.z),2) ,round(avg(b.q),2)
FROM tb_stbprp_b a,tb_river_r b
WHERE (a.stcd=b.stcd) AND
(DATEPART(yy, b.tm) = "
+ textBox1.text +
") AND
(DATEPART(mm, b.tm) = "
+ ddlmonth.selecteditem.text +
") and datePart(hh,b.tm) in (2,8,14,20) and datepart(mi,b.tm)= "
+ ddlxun.selecteditem.text +
"group by a.stcd,a.stnm,convert(char(7),b.tm,120)
,case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
ORDER BY a.stcd,COUNT(DAY(b.tm)), COUNT(DATEPART(hh, b.tm))"
用上面的sql语句进行查询就可以了。
#2
谢谢,我试试!
#3
好像不行,我主要是要统计某年某月的上、中、下旬的数据的平均值?也就是输入年份,选择月份和相应的旬后,按下按钮就统计出这个月的这个旬的数据的平均值。请各位帮帮,谢谢!
#4
你取日期(旬)的地方在哪?
#5
直接做成视图好了!!!
#6
dim sql as string
sql = "SELECT a.stcd as 编号,a.stnm as 名称, CONVERT(char(7), b.tm, 120) AS 年月,
旬=case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
,round(AVG(b.z),2) ,round(avg(b.q),2)
FROM tb_stbprp_b a,tb_river_r b
WHERE (a.stcd=b.stcd) AND
(DATEPART(yy, b.tm) = "
+ textBox1.text +
") AND
(DATEPART(mm, b.tm) = "
+ ddlmonth.selecteditem.text +
") and datePart(hh,b.tm) in (2,8,14,20) and datepart(mi,b.tm)= 0
group by a.stcd,a.stnm,convert(char(7),b.tm,120)
,case
when "+ ddlxun.selecteditem.text +" between 1 and 10 then '上旬' when "+ ddlxun.selecteditem.text +" between 11 and 20 then '中旬'
when "+ ddlxun.selecteditem.text +" >21 then '下旬' end
ORDER BY a.stcd,COUNT(DAY(b.tm)), COUNT(DATEPART(hh, b.tm))"
这样,可不可以? 见笑了,我也是菜鸟,学习中~~
sql = "SELECT a.stcd as 编号,a.stnm as 名称, CONVERT(char(7), b.tm, 120) AS 年月,
旬=case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
,round(AVG(b.z),2) ,round(avg(b.q),2)
FROM tb_stbprp_b a,tb_river_r b
WHERE (a.stcd=b.stcd) AND
(DATEPART(yy, b.tm) = "
+ textBox1.text +
") AND
(DATEPART(mm, b.tm) = "
+ ddlmonth.selecteditem.text +
") and datePart(hh,b.tm) in (2,8,14,20) and datepart(mi,b.tm)= 0
group by a.stcd,a.stnm,convert(char(7),b.tm,120)
,case
when "+ ddlxun.selecteditem.text +" between 1 and 10 then '上旬' when "+ ddlxun.selecteditem.text +" between 11 and 20 then '中旬'
when "+ ddlxun.selecteditem.text +" >21 then '下旬' end
ORDER BY a.stcd,COUNT(DAY(b.tm)), COUNT(DATEPART(hh, b.tm))"
这样,可不可以? 见笑了,我也是菜鸟,学习中~~
#7
谢谢楼上的几位,我的方法也可以算出旬平均值,只不过是一次将上旬、中旬、下旬全部算出来了放在一个DataGrid里,现在的结果是这样的:
编号 名称 年月 旬 z q
1 大海 2005-07 上旬 123 234
1 大海 2005-07 中旬 124 236
1 大海 2005-07 下旬 122 230
2 小河 2005-07 上旬 239 4590
2 小河 2005-07 中旬 236 4580
2 小河 2005-07 下旬 240 4620
.......................................
我的要求是当选择旬的ddlxun中的上旬时就计算出上旬的平均值,即结果应该是这样的:
编号 名称 年月 旬 z q
1 大海 2005-07 上旬 123 234
2 小河 2005-07 上旬 239 4590
.......................................
、选择ddlxun中的中旬时就计算出中旬的平均值,记结果是这样的:
编号 名称 年月 旬 z q
1 大海 2005-07 中旬 124 236
2 小河 2005-07 中旬 236 4580
.......................................
以此类推。谢谢!
编号 名称 年月 旬 z q
1 大海 2005-07 上旬 123 234
1 大海 2005-07 中旬 124 236
1 大海 2005-07 下旬 122 230
2 小河 2005-07 上旬 239 4590
2 小河 2005-07 中旬 236 4580
2 小河 2005-07 下旬 240 4620
.......................................
我的要求是当选择旬的ddlxun中的上旬时就计算出上旬的平均值,即结果应该是这样的:
编号 名称 年月 旬 z q
1 大海 2005-07 上旬 123 234
2 小河 2005-07 上旬 239 4590
.......................................
、选择ddlxun中的中旬时就计算出中旬的平均值,记结果是这样的:
编号 名称 年月 旬 z q
1 大海 2005-07 中旬 124 236
2 小河 2005-07 中旬 236 4580
.......................................
以此类推。谢谢!
#8
直接在程序里判断ddlxun输入的是上旬,中旬还是下旬,然后把日期作为查询条件传进来,不用要那么多case吧,直接写where语句好了
#9
同意楼上! 继续关注!
#10
谢alphaqxm,具体怎么写?
#11
设置一下datagrid的数据源的视图的过滤条件,假设你的datagrid数据源是ds.tables(0)
显示上旬:
ds.Tables(0).DefaultView.RowFilter = " 旬='上旬' "
显示中旬:
ds.Tables(0).DefaultView.RowFilter = " 旬='中旬' "
或者在select的时候加上where 旬='?旬'
显示上旬:
ds.Tables(0).DefaultView.RowFilter = " 旬='上旬' "
显示中旬:
ds.Tables(0).DefaultView.RowFilter = " 旬='中旬' "
或者在select的时候加上where 旬='?旬'
#12
谢谢楼上的几位,这个问题我解决了。
#1
dim sql as string
sql = "SELECT a.stcd as 编号,a.stnm as 名称, CONVERT(char(7), b.tm, 120) AS 年月,
旬=case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
,round(AVG(b.z),2) ,round(avg(b.q),2)
FROM tb_stbprp_b a,tb_river_r b
WHERE (a.stcd=b.stcd) AND
(DATEPART(yy, b.tm) = "
+ textBox1.text +
") AND
(DATEPART(mm, b.tm) = "
+ ddlmonth.selecteditem.text +
") and datePart(hh,b.tm) in (2,8,14,20) and datepart(mi,b.tm)= "
+ ddlxun.selecteditem.text +
"group by a.stcd,a.stnm,convert(char(7),b.tm,120)
,case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
ORDER BY a.stcd,COUNT(DAY(b.tm)), COUNT(DATEPART(hh, b.tm))"
用上面的sql语句进行查询就可以了。
sql = "SELECT a.stcd as 编号,a.stnm as 名称, CONVERT(char(7), b.tm, 120) AS 年月,
旬=case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
,round(AVG(b.z),2) ,round(avg(b.q),2)
FROM tb_stbprp_b a,tb_river_r b
WHERE (a.stcd=b.stcd) AND
(DATEPART(yy, b.tm) = "
+ textBox1.text +
") AND
(DATEPART(mm, b.tm) = "
+ ddlmonth.selecteditem.text +
") and datePart(hh,b.tm) in (2,8,14,20) and datepart(mi,b.tm)= "
+ ddlxun.selecteditem.text +
"group by a.stcd,a.stnm,convert(char(7),b.tm,120)
,case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
ORDER BY a.stcd,COUNT(DAY(b.tm)), COUNT(DATEPART(hh, b.tm))"
用上面的sql语句进行查询就可以了。
#2
谢谢,我试试!
#3
好像不行,我主要是要统计某年某月的上、中、下旬的数据的平均值?也就是输入年份,选择月份和相应的旬后,按下按钮就统计出这个月的这个旬的数据的平均值。请各位帮帮,谢谢!
#4
你取日期(旬)的地方在哪?
#5
直接做成视图好了!!!
#6
dim sql as string
sql = "SELECT a.stcd as 编号,a.stnm as 名称, CONVERT(char(7), b.tm, 120) AS 年月,
旬=case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
,round(AVG(b.z),2) ,round(avg(b.q),2)
FROM tb_stbprp_b a,tb_river_r b
WHERE (a.stcd=b.stcd) AND
(DATEPART(yy, b.tm) = "
+ textBox1.text +
") AND
(DATEPART(mm, b.tm) = "
+ ddlmonth.selecteditem.text +
") and datePart(hh,b.tm) in (2,8,14,20) and datepart(mi,b.tm)= 0
group by a.stcd,a.stnm,convert(char(7),b.tm,120)
,case
when "+ ddlxun.selecteditem.text +" between 1 and 10 then '上旬' when "+ ddlxun.selecteditem.text +" between 11 and 20 then '中旬'
when "+ ddlxun.selecteditem.text +" >21 then '下旬' end
ORDER BY a.stcd,COUNT(DAY(b.tm)), COUNT(DATEPART(hh, b.tm))"
这样,可不可以? 见笑了,我也是菜鸟,学习中~~
sql = "SELECT a.stcd as 编号,a.stnm as 名称, CONVERT(char(7), b.tm, 120) AS 年月,
旬=case
when day(b.tm) between 1 and 10 then '上旬'
when day(b.tm) between 11 and 20 then '中旬'
when day(b.tm) >21 then '下旬' end
,round(AVG(b.z),2) ,round(avg(b.q),2)
FROM tb_stbprp_b a,tb_river_r b
WHERE (a.stcd=b.stcd) AND
(DATEPART(yy, b.tm) = "
+ textBox1.text +
") AND
(DATEPART(mm, b.tm) = "
+ ddlmonth.selecteditem.text +
") and datePart(hh,b.tm) in (2,8,14,20) and datepart(mi,b.tm)= 0
group by a.stcd,a.stnm,convert(char(7),b.tm,120)
,case
when "+ ddlxun.selecteditem.text +" between 1 and 10 then '上旬' when "+ ddlxun.selecteditem.text +" between 11 and 20 then '中旬'
when "+ ddlxun.selecteditem.text +" >21 then '下旬' end
ORDER BY a.stcd,COUNT(DAY(b.tm)), COUNT(DATEPART(hh, b.tm))"
这样,可不可以? 见笑了,我也是菜鸟,学习中~~
#7
谢谢楼上的几位,我的方法也可以算出旬平均值,只不过是一次将上旬、中旬、下旬全部算出来了放在一个DataGrid里,现在的结果是这样的:
编号 名称 年月 旬 z q
1 大海 2005-07 上旬 123 234
1 大海 2005-07 中旬 124 236
1 大海 2005-07 下旬 122 230
2 小河 2005-07 上旬 239 4590
2 小河 2005-07 中旬 236 4580
2 小河 2005-07 下旬 240 4620
.......................................
我的要求是当选择旬的ddlxun中的上旬时就计算出上旬的平均值,即结果应该是这样的:
编号 名称 年月 旬 z q
1 大海 2005-07 上旬 123 234
2 小河 2005-07 上旬 239 4590
.......................................
、选择ddlxun中的中旬时就计算出中旬的平均值,记结果是这样的:
编号 名称 年月 旬 z q
1 大海 2005-07 中旬 124 236
2 小河 2005-07 中旬 236 4580
.......................................
以此类推。谢谢!
编号 名称 年月 旬 z q
1 大海 2005-07 上旬 123 234
1 大海 2005-07 中旬 124 236
1 大海 2005-07 下旬 122 230
2 小河 2005-07 上旬 239 4590
2 小河 2005-07 中旬 236 4580
2 小河 2005-07 下旬 240 4620
.......................................
我的要求是当选择旬的ddlxun中的上旬时就计算出上旬的平均值,即结果应该是这样的:
编号 名称 年月 旬 z q
1 大海 2005-07 上旬 123 234
2 小河 2005-07 上旬 239 4590
.......................................
、选择ddlxun中的中旬时就计算出中旬的平均值,记结果是这样的:
编号 名称 年月 旬 z q
1 大海 2005-07 中旬 124 236
2 小河 2005-07 中旬 236 4580
.......................................
以此类推。谢谢!
#8
直接在程序里判断ddlxun输入的是上旬,中旬还是下旬,然后把日期作为查询条件传进来,不用要那么多case吧,直接写where语句好了
#9
同意楼上! 继续关注!
#10
谢alphaqxm,具体怎么写?
#11
设置一下datagrid的数据源的视图的过滤条件,假设你的datagrid数据源是ds.tables(0)
显示上旬:
ds.Tables(0).DefaultView.RowFilter = " 旬='上旬' "
显示中旬:
ds.Tables(0).DefaultView.RowFilter = " 旬='中旬' "
或者在select的时候加上where 旬='?旬'
显示上旬:
ds.Tables(0).DefaultView.RowFilter = " 旬='上旬' "
显示中旬:
ds.Tables(0).DefaultView.RowFilter = " 旬='中旬' "
或者在select的时候加上where 旬='?旬'
#12
谢谢楼上的几位,这个问题我解决了。