数据库表处理问题(60分)

我有一个表,类似于仓存那种,将成品入库表.<br>ID &nbsp;GROUP &nbsp;INDATE <br>001 &nbsp;A &nbsp; &nbsp; &nbsp;2008-1-2<br>002 &nbsp;B &nbsp; &nbsp; &nbsp;2008-1-16<br>001 &nbsp;A &nbsp; &nbsp; &nbsp;2008-2-10<br>003 &nbsp;A &nbsp; &nbsp; &nbsp;2008-2-20<br>004 &nbsp;B &nbsp; &nbsp; &nbsp;2008-2-22<br>005 &nbsp;C &nbsp; &nbsp; &nbsp;2008-3-12<br><br>我想上表做成一个分析表,统计入库时间IDNATE离当天天数差为0-30天,31-60天,60-180天,及180天以上的情况:<br>GROUP ID &nbsp;0-30 &nbsp;31-60 &nbsp;61-180 &nbsp;180以上<br>A &nbsp; &nbsp; 001 &nbsp;... &nbsp; ... &nbsp; ... &nbsp; &nbsp; &nbsp;...<br><br>请问各位高手,应该怎么写会比较好点,先谢了

高手在哪里

自己再顶一下,有没有高手帮下忙呀

能否把表写全点，要统计什么东西，这样很难说的。。。

SELECT ID,GROUP,AA1,BB1,CC1,DD1<br>FROM <br>(SELECT ID,GROUP,COUNT(*) as AA1 FROM 表名 where sysdate-indate&gt;0 and sysdate-indate&lt;=30) a,<br>(SELECT ID,GROUP,COUNT(*) as BB1 FROM 表名 where sysdate-indate&gt;31 and sysdate-indate&lt;=60) b,<br>(SELECT ID,GROUP,COUNT(*) as CC1 FROM 表名 where sysdate-indate&gt;61 and sysdate-indate&lt;=180) c,<br>(SELECT ID,GROUP,COUNT(*) as DD1 FROM 表名 where sysdate-indate&gt;180) d<br>where a.id(+)=b.id(+)<br>&nbsp; &nbsp; &nbsp; and a.id(+)=c.id(+)<br>&nbsp; &nbsp; &nbsp; and a.id(+)=d.id(+)<br>&nbsp; &nbsp; &nbsp; <br>在Oracle数据库中,以上SQL语句应该可以实现,具体没有去做测试,你可以试一下.

谢谢楼上两位朋友.<br>TO_港湾,我之前用的是INNER JOIN的方法,将四种情况组合记录达几十万条,实际上不到1000条记录,这种连接是否有问题?<br>TO lngdtommy,<br>不好意思,我没说清楚.我的意思有一个入库表,入库时都会有以产品编号(ID),及产品类型(GROUP)和入库日期(INDATE),入库数量(QUANTITY),比如我想统计一下以今天时间2008-7-13为准,离今天入库日期为30天,31到60天,61到180天及180天以上库存量的情况.<br>举例:我统计0-30天库存情况:select A.GROUP,A.ID,SUM(A.QUANTITY)AS '0-30' from TABLE A where DATADIFF(DAY,A.INDATE,GETDAGE())&gt;0 and DATADIFF(DAY,A.INDATE,GETDATE()&lt;=30<br>GROUP BY A.GROUP,A.ID<br>ORDER BY A.GROUP<br>但是这只是一种情况30天内的情况,我想一次性查询那四种情况,等于要查询成这样<br>select GROUP,ID,SUM(A.QUANTITY) AS '0-30',SUM(B.QUANTITY) AS '31-60',SUM(C.QUANTITY) AS '61-180',SUM(D,QUANTITY)AS '180~~' from ...<br>不知道后面怎么样写.

先创建一个自定义函数：<br>create function f_sjd(@t1 datetime, @t2 datetime) <br>returns varchar(50)<br>as<br>begin<br>&nbsp; declare @n int<br>&nbsp; declare @result varchar(50)<br>&nbsp; set @n=DateDiff(DAY,@t1,@t2)<br>&nbsp; if @n&gt;=0 and @n&lt;=30 set @result='0-30天'<br>&nbsp; else if @n&gt;30 and @n&lt;=60 set @result='31-60天'<br>&nbsp; else if @n&gt;60 and @n&lt;=90 set @result='61-90天'<br>&nbsp; else if @n&gt;90 and @n&lt;=120 set @result='91-120天'<br>&nbsp; else if @n&gt;120 and @n&lt;=150 set @result='121-150天'<br>&nbsp; else if @n&gt;150 and @n&lt;=180 set @result='151-180天'<br>&nbsp; else set @result='180天以上' <br>&nbsp; return @result<br>end<br>然后：<br>--示例数据：<br>declare @tb table([ID] varchar(3), [GROUP] varchar(10), [QUANTITY]int, [INDATE] Datetime)<br>insert into @tb<br>select '001','A',1000,'2008-1-2' union all<br>select '002','B',3000,'2008-1-16' union all<br>select '001','A',2000,'2008-2-10' union all<br>select '003','A',1500,'2008-2-20' union all<br>select '004','B',3000,'2008-2-22' union all<br>select '005','C',2800,'2008-3-12'<br><br>select<br>&nbsp; &nbsp;[GROUP],[ID],<br>&nbsp; &nbsp;sum(case [时间段] when '0-30天' then [QUANTITY] else 0 end) as [0-30天],<br>&nbsp; &nbsp;sum(case [时间段] when '31-60天' then [QUANTITY] else 0 end) as [31-60天],<br>&nbsp; &nbsp;sum(case [时间段] when '61-90天' then [QUANTITY] else 0 end) as [61-90天],<br>&nbsp; &nbsp;sum(case [时间段] when '91-120天' then [QUANTITY] else 0 end) as [91-120天],<br>&nbsp; &nbsp;sum(case [时间段] when '121-150天' then [QUANTITY] else 0 end) as [121-150天],<br>&nbsp; &nbsp;sum(case [时间段] when '151-180天' then [QUANTITY] else 0 end) as [151-180天],<br>&nbsp; &nbsp;sum(case [时间段] when '180天以上' then [QUANTITY] else 0 end) as [180天以上]<br>from<br>(<br>select dbo.f_sjd([INDATE], getDate()) as [时间段],[ID],[GROUP],sum(QUANTITY) [QUANTITY]<br>from @tb<br>group by dbo.f_sjd([INDATE], getDate()),[GROUP],[ID]<br>) t<br>group by [GROUP],[ID]<br>/*<br>GROUP &nbsp; &nbsp; &nbsp;ID &nbsp; &nbsp; &nbsp; &nbsp;0-03天 &nbsp; &nbsp; 31-60天 &nbsp; 61-90天 &nbsp; &nbsp;91-120天 &nbsp; 121-150天 151-180天 180天以上<br>A 001 0 0 0 0 0 2000 1000<br>B 002 0 0 0 0 0 3000 0<br>A 003 0 0 0 0 1500 0 0<br>B 004 0 0 0 0 3000 0 0<br>C 005 0 0 0 0 2800 0 0<br>*/

select [ID], [GROUP],<br>&nbsp; &nbsp;Sum(t1) as t30,<br>&nbsp; &nbsp;Sum(t2) as t3060,<br>&nbsp; &nbsp;Sum(t3) as t60180,<br>&nbsp; &nbsp;Sum(t4) as t180<br>from<br>&nbsp; &nbsp;(Select [ID], [GROUP],<br>&nbsp; &nbsp; &nbsp; Case when Cstt(GetDate() - INDATE as int) &lt; 30 then 1 end as t1,<br>&nbsp; &nbsp; &nbsp; Case when Cstt(GetDate() - INDATE as int) between 31 and 60 then 1 end as t2,<br>&nbsp; &nbsp; &nbsp; Case when Cstt(GetDate() - INDATE as int) between 61 and 180 then 1 end as t3,<br>&nbsp; &nbsp; &nbsp; Case when Cstt(GetDate() - INDATE as int) &gt; 180 then 1 end as t4<br>&nbsp; &nbsp;from [你的表名]) tmp<br>Group by [ID], [GROUP]

错了,cstt 为 Cast

谢谢kaida,datafans;我明天试一下.

to 楼上朋友们,<br>select A.GROUP,A.ID,SUM(A.QUANTITY)AS '0-30' from TABLE A where &nbsp;Cast(GetDate() - INDATE as int) &gt; 0 and <br>&nbsp;Cast(GetDate() - INDATE as int)&lt;=30 &nbsp;[and ID='001']<br>GROUP BY A.GROUP,A.ID<br><br>我发现一个很奇怪的问题,不管采用哪种时间段,sum(QUANTITY)所得的结果都会一样

某日某号是什么时间,DELPHI相关的函数

你这句 select A.GROUP,A.ID,SUM(A.QUANTITY)AS '0-30' from TABLE A where &nbsp;Cast(GetDate() - INDATE as int) &gt; 0 and <br>&nbsp;Cast(GetDate() - INDATE as int)&lt;=30 &nbsp;[and ID='001']<br>GROUP BY A.GROUP,A.ID 是哪来的？<br>上面回答你的是通过测试的，你测试了吗？

谢谢kaida,我按你方法测试可以通过,只是结果不对劲,查询的结果并不是按时间表段的结果,sum(quantity)都是一样的. 那个语句是我将你的忙组合改写了一下.

to kaida,请问是否在线,方便用QQ吗?想多请教一下.我的QQ:157730017

多人接受答案了。