[江湖告急+在线等候] 在SYBASE查询中加入星期函数

心剑

免费乐捐，感谢支持

各们大大，一个月里分别统计周一至周日的人数。如果在下查询中加入呀？

IF (object_id('#AAA')) IS NOT NULL DROP TABLE #AAA

select D.VIPTYPE, C.ZHIYE, ISNULL(C.SEX,2) SEX ,
DATEPART(hh,E.JYSJ) HOUR ,
30*round(DATEPART(mi,JYSJ)/30,0) BAN ,
COUNT(*) XFCS
INTO #AAA
from VIPXFJL A, VIPXX C, VIPDEF D, SKTXSJL E
WHERE A.SKTNO=E.SKTNO
AND A.JLBH=E.JLBH
and DATEDIFF(DAY, '2005.7.1', A.RQ )>=0 --KAISI RIQI
AND DATEDIFF(DAY, A.RQ, '2005.7.31' )>=0 --JIE SHU RIQI
AND C.VIPTYPE = D.VIPTYPE
AND A.VIPID = C.VIPID
GROUP BY D.VIPTYPE, C.ZHIYE, C.SEX ,
DATEPART(hh,E.JYSJ),
30*round(DATEPART(mi,E.JYSJ)/30,0)

SELECT VIPTYPE, ZHIYE, SEX, SUM(XFCS) '<=12:00'
FROM #AAA
WHERE HOUR<=12
GROUP BY VIPTYPE, ZHIYE, SEX

SELECT VIPTYPE, ZHIYE, SEX, SUM(XFCS) '>12-14'
FROM #AAA
WHERE HOUR > 12
AND HOUR<=14
GROUP BY VIPTYPE, ZHIYE, SEX

SELECT VIPTYPE, ZHIYE, SEX, SUM(XFCS) '14-18'
FROM #AAA
WHERE HOUR >14
AND HOUR<=18
GROUP BY VIPTYPE, ZHIYE, SEX

SELECT VIPTYPE, ZHIYE, SEX, SUM(XFCS) '>18'
FROM #AAA
WHERE HOUR >18
GROUP BY VIPTYPE, ZHIYE, SEX

阿辉

select D.VIPTYPE, C.ZHIYE, ISNULL(C.SEX,2) SEX ,DATEPART(dw,A.RQ) WEEK,DATEPART(hh,E.JYSJ) HOUR ,
30*round(DATEPART(mi,JYSJ)/30,0) BAN ,
COUNT(*) XFCS
INTO #AAA
from VIPXFJL A, VIPXX C, VIPDEF D, SKTXSJL E
WHERE A.SKTNO=E.SKTNO
AND A.JLBH=E.JLBH
and DATEDIFF(DAY, '2005.7.1', A.RQ )>=0 --KAISI RIQI
AND DATEDIFF(DAY, A.RQ, '2005.7.31' )>=0 --JIE SHU RIQI
AND C.VIPTYPE = D.VIPTYPE
AND A.VIPID = C.VIPID
GROUP BY D.VIPTYPE, C.ZHIYE, C.SEX , DATEPART(dw,A.RQ),DATEPART(hh,E.JYSJ),30*round(DATEPART(mi,E.JYSJ)/30,0)

阿辉

在临时表里多加一列week就搞定了。

原来的ＳＱＬ不是你写的吧，如果是你写的，应该很容易就搞定的呀

呵呵