请问高手个子查询的问题?

nullarea 2003-03-24 08:53:25
现在有两个表,一个油品编码表PRODUCTTAB,用得着的就是两个字段PRODUCTID(油品编码),PRODUCTNAME(油品名称),一个油品入库表STOCKTAB,主要字段是PRODUCTID,STOCKTIME(入库时间),BILLNUMBER(提油单号),STOCKAMOUNT(入库数量),VAT(BIT类型字段)判断提油单是否结算,现在要查询的是某月前,没结算的提油单油品的总数量,某月内没结算的提油单油品的总数量,某月内和某月以前结算的提油单油品总数量,总数量都是分油品汇总,我的语句是,
SELECT PRODUCTNAME,LASTAMOUNT=(SELECT SUM(STOCKAMOUNT) FROM STOCKTAB S,PRODUCTTAB P WHERE S。PRODUCTID=P。PRODUCTID AND STOCKTIME<STARTTIME AND VAT=0 ),NOWAMOUNT=(SELECT SUM(STOCKAMOUNT) FROM STOCKTAB S,PRODUCTTAB P WHERE S。PRODUCTID=P。PRODUCTID AND STOCKTIME>STARTTIME AND
STOCKTIME<ENDTIME AND VAT=0),AMOUNT==(SELECT SUM(STOCKAMOUNT) FROM STOCKTAB S,PRODUCTTAB P WHERE S。PRODUCTID=P。PRODUCTID AND STOCKTIME<ENDTIME AND VAT=0) FROM Producttab group by PRODUCTNAME
大家帮我看一下问题出在那里
...全文
3 点赞 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
nullarea 2003-03-25
谢谢大家,我已经自己解决问题了,不过还是要感谢大家
回复
yonghengdizhen 2003-03-25
现在有两个表,一个油品编码表PRODUCTTAB,用得着的就是两个字段PRODUCTID(油品编码),PRODUCTNAME(油品名称),一个油品入库表STOCKTAB,主要字段是PRODUCTID,STOCKTIME(入库时间),BILLNUMBER(提油单号),STOCKAMOUNT(入库数量),VAT(BIT类型字段)判断提油单是否结算,现在要查询的是某月前,没结算的提油单油品的总数量,某月内没结算的提油单油品的总数量,某月内和某月以前结算的提油单油品总数量,总数量都是分油品汇总


select PRODUCTNAME,(select sum(STOCKAMOUNT) from STOCKTAB where PRODUCTID=PT.PRODUCTID and VAT=0 and datediff(month,STOCKTIME,<日期参数>)<0),
(select sum(STOCKAMOUNT) from STOCKTAB where PRODUCTID=PT.PRODUCTID and VAT=1 and datediff(month,STOCKTIME,<日期参数>)<=0)
from PRODUCTTAB PT
回复
pengdali 2003-03-24
多了一个,号!哈哈。。。
SELECT PRODUCTNAME,LASTAMOUNT,NOWAMOUNT,AMOUNT
FROM (
SELECT PRODUCTID,SUM(STOCKAMOUNT) AS AMOUNT,
SUM(CASE WHEN STOCKTIME<STARTTIME THEN STOCKAMOUNT ELSE 0 END) AS LASTAMOUNT,
SUM(CASE WHEN STOCKTIME>=STARTTIME THEN STOCKAMOUNT ELSE 0 END) AS NOWAMOUNT
WHERE VAL=0 AND STOCKTIME<ENDTIME
GROUP BY PRODUCTID
) AS S,PRODUCTTAB P
WHERE S.PRODUCTID=P.PRODUCTID
回复
CSDNM 2003-03-24
SELECT PRODUCTNAME,LASTAMOUNT,NOWAMOUNT,AMOUNT
FROM (
SELECT PRODUCTID,SUM(STOCKAMOUNT) AS AMOUNT,
SUM(CASE WHEN STOCKTIME<STARTTIME THEN STOCKAMOUNT ELSE 0 END) AS LASTAMOUNT,
SUM(CASE WHEN STOCKTIME>=STARTTIME THEN STOCKAMOUNT ELSE 0 END) AS NOWAMOUNT,
WHERE VAL=0 AND STOCKTIME<ENDTIME
GROUP BY PRODUCTID
) AS S,PRODUCTTAB P
WHERE S.PRODUCTID=P.PRODUCTID
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2003-03-24 08:53
社区公告
暂无公告