各位,帮忙看下,数据库查询问题! 这么一张表, 我想查询 blmReqDate 在一个月之类的 blmSchQty的所有数量相加的和, 一个月是以1号到31或者30号为标准可以这样,1月的话就是1-31,2月不用管平年闰年的。 3-31,4-30 以此类推。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 ID Name BackLog blmGF ManterialDesc ETPONo blmReqDate blmSchQty blmSchDate Price3 admin 20100210(KW06) 33 BSM10GP120 HK-PO-000118 2010-02-22 00:00:00.000 420 2010-04-22 00:00:00.000 1000.04 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V AC HK-PO-000338 2010-03-22 00:00:00.000 1900 2010-04-22 00:00:00.000 1000.05 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V AC HK-PO-000338 2010-04-22 00:00:00.000 190 2010-04-22 00:00:00.000 1000.06 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V HK-PO-000338 2010-05-01 00:00:00.000 190 2010-04-22 00:00:00.000 1000.07 admin 20100210(KW06) 33 BSM10GP120 HK-PO-000118 2010-05-20 00:00:00.000 500 2010-04-22 00:00:00.000 1000.08 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V HK-PO-000338 2010-06-14 00:00:00.000 1260 2010-04-22 00:00:00.000 1000.0 select startDt=cast('2010-02-01' as datetime), endDt=dateadd(dd,-1,cast('2010-03-01' as datetime))/*startDt endDt----------------------- -----------------------2010-02-01 00:00:00.000 2010-02-28 00:00:00.000(1 行受影响)*/ ID Name BackLog blmGF ManterialDesc ETPONo blmReqDate blmSchQty blmSchDate Price3 admin 20100210(KW06) 33 BSM10GP120 HK-PO-000118 2010-02-22 420 2010-04-22 1000.04 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V AC HK-PO-000338 2010-03-22 1900 2010-04-22 1000.05 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V AC HK-PO-000338 2010-04-22 190 2010-04-22 1000.06 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V HK-PO-000338 2010-05-01 190 2010-04-22 1000.07 admin 20100210(KW06) 33 BSM10GP120 HK-PO-000118 2010-05-20 500 2010-04-22 1000.08 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V HK-PO-000338 2010-06-14 1260 2010-04-22 1000.0 兄弟,你这个查询出来返回的是一个月的间隔把 我要的在这个时间间隔里面 同一种型号“ManterialDesc”的“blmSchQty”数量相加 表我发上去了,麻烦你复制过去(第2次发的表),把格式排一下,谢谢你了 DECLARE @temp TABLE (ID INT, NAME VARCHAR(10), BackLog VARCHAR(50), blmGF INT, ManterialDesc VARCHAR(50), ETPONo VARCHAR(50), blmReqDate DATETIME, blmSchQty INT, blmSchDate DATETIME, Price DECIMAL(10,1))INSERT INTO @temp VALUES (3,'admin','20100210(KW06)',33,'BSM10GP120', 'HK-PO-000118', '2010-02-22',420, '2010-04-22',1000.0)INSERT INTO @temp VALUES (4,'admin','20100210(KW06)',58,'SAF-XC878CM-16FF15V','AC HK-PO-000338', '2010-03-22',1900,'2010-04-22',1000.0)INSERT INTO @temp VALUES (5,'admin','20100210(KW06)',58,'SAF-XC878CM-16FF15V','AC HK-PO-000338', '2010-04-22',190, '2010-04-22',1000.0)INSERT INTO @temp VALUES (6,'admin','20100210(KW06)',58,'SAF-XC878CM-16FF15V','HK-PO-000338', '2010-05-01',190, '2010-04-22',1000.0)INSERT INTO @temp VALUES (7,'admin','20100210(KW06)',33,'BSM10GP120', 'HK-PO-000118', '2010-05-20',500, '2010-04-22',1000.0)INSERT INTO @temp VALUES (8,'admin','20100210(KW06)',58,'SAF-XC878CM-16FF15V','HK-PO-000338', '2010-06-14',1260,'2010-04-22',1000.0)SELECT CONVERT(VARCHAR(6),blmReqDate,112) AS Mon ,ManterialDesc ,SUM(blmSchQty) AS blmSchQtyFROM @temp GROUP BY CONVERT(VARCHAR(6),blmReqDate,112) ,ManterialDesc 不好意思,可以解释下VARCHAR(6), 112 是什么意思不? 我看不大明白!! 数据字符长度可以为0? 操作问题求解~~~ 我是新手,请多关照 analysis servise+delphi Access---》Sql Server类型转换问题,着急呀!! 小白求几句SQL脚本 关于时间的运算: 头疼的数据库操作问题!!!!1 数据库附加问题 急切等待!!!请教!从表里将数据导入到文本文件! sql远程数据导入问题? 查询新疆维吾尔语显示问号?
ID Name BackLog blmGF ManterialDesc ETPONo blmReqDate blmSchQty blmSchDate Price
3 admin 20100210(KW06) 33 BSM10GP120 HK-PO-000118 2010-02-22 00:00:00.000 420 2010-04-22 00:00:00.000 1000.0
4 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V AC HK-PO-000338 2010-03-22 00:00:00.000 1900 2010-04-22 00:00:00.000 1000.0
5 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V AC HK-PO-000338 2010-04-22 00:00:00.000 190 2010-04-22 00:00:00.000 1000.0
6 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V HK-PO-000338 2010-05-01 00:00:00.000 190 2010-04-22 00:00:00.000 1000.0
7 admin 20100210(KW06) 33 BSM10GP120 HK-PO-000118 2010-05-20 00:00:00.000 500 2010-04-22 00:00:00.000 1000.0
8 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V HK-PO-000338 2010-06-14 00:00:00.000 1260 2010-04-22 00:00:00.000 1000.0
select startDt=cast('2010-02-01' as datetime),
endDt=dateadd(dd,-1,cast('2010-03-01' as datetime))/*
startDt endDt
----------------------- -----------------------
2010-02-01 00:00:00.000 2010-02-28 00:00:00.000(1 行受影响)
*/
3 admin 20100210(KW06) 33 BSM10GP120 HK-PO-000118 2010-02-22 420 2010-04-22 1000.0
4 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V AC HK-PO-000338 2010-03-22 1900 2010-04-22 1000.0
5 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V AC HK-PO-000338 2010-04-22 190 2010-04-22 1000.0
6 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V HK-PO-000338 2010-05-01 190 2010-04-22 1000.0
7 admin 20100210(KW06) 33 BSM10GP120 HK-PO-000118 2010-05-20 500 2010-04-22 1000.0
8 admin 20100210(KW06) 58 SAF-XC878CM-16FF15V HK-PO-000338 2010-06-14 1260 2010-04-22 1000.0
兄弟,你这个查询出来返回的是一个月的间隔把 我要的在这个时间间隔里面 同一种型号“ManterialDesc”的“blmSchQty”数量相加 表我发上去了,麻烦你复制过去(第2次发的表),把格式排一下,谢谢你了
DECLARE @temp TABLE (ID INT, NAME VARCHAR(10), BackLog VARCHAR(50), blmGF INT, ManterialDesc VARCHAR(50), ETPONo VARCHAR(50), blmReqDate DATETIME, blmSchQty INT, blmSchDate DATETIME, Price DECIMAL(10,1))INSERT INTO @temp VALUES (3,'admin','20100210(KW06)',33,'BSM10GP120', 'HK-PO-000118', '2010-02-22',420, '2010-04-22',1000.0)
INSERT INTO @temp VALUES (4,'admin','20100210(KW06)',58,'SAF-XC878CM-16FF15V','AC HK-PO-000338', '2010-03-22',1900,'2010-04-22',1000.0)
INSERT INTO @temp VALUES (5,'admin','20100210(KW06)',58,'SAF-XC878CM-16FF15V','AC HK-PO-000338', '2010-04-22',190, '2010-04-22',1000.0)
INSERT INTO @temp VALUES (6,'admin','20100210(KW06)',58,'SAF-XC878CM-16FF15V','HK-PO-000338', '2010-05-01',190, '2010-04-22',1000.0)
INSERT INTO @temp VALUES (7,'admin','20100210(KW06)',33,'BSM10GP120', 'HK-PO-000118', '2010-05-20',500, '2010-04-22',1000.0)
INSERT INTO @temp VALUES (8,'admin','20100210(KW06)',58,'SAF-XC878CM-16FF15V','HK-PO-000338', '2010-06-14',1260,'2010-04-22',1000.0)SELECT CONVERT(VARCHAR(6),blmReqDate,112) AS Mon
,ManterialDesc
,SUM(blmSchQty) AS blmSchQty
FROM @temp
GROUP BY CONVERT(VARCHAR(6),blmReqDate,112)
,ManterialDesc
不好意思,可以解释下VARCHAR(6), 112 是什么意思不? 我看不大明白!!