我现在有两个表,一个是买入货物表BUY_IN,一个是卖出货物表SALE_OUT
我想在这两表中进行统计查询:在一个时间(date)段内,按货物的名字(name)统计,买入和卖出的总数量(number)
我是这初学者,请大家帮我改正下面的语句:
select BUY_IN.name, SUM(BUY_IN.number) AS in_number, SUM(SALE_OUT.number) AS out_number FROM BUY_IN LEFT JOIN SALE_OUT ON BUY_IN.name=SALE_OUT.name GROUP BY BUY_IN.name WHERE date between #%s# and #%s# ";
我想在这两表中进行统计查询:在一个时间(date)段内,按货物的名字(name)统计,买入和卖出的总数量(number)
我是这初学者,请大家帮我改正下面的语句:
select BUY_IN.name, SUM(BUY_IN.number) AS in_number, SUM(SALE_OUT.number) AS out_number FROM BUY_IN LEFT JOIN SALE_OUT ON BUY_IN.name=SALE_OUT.name GROUP BY BUY_IN.name WHERE date between #%s# and #%s# ";
解决方案 »
- 菜鸟求教:想学数据库原理哪本教材讲得最好?
- sql语句查询问题
- 跪求此问解答:----SQL Server不存在或访问被拒绝Connection Open(connect()):
- 请问怎么样用SQL语句来修改列名?
- 如:select a/t,b/t,c/t如果t为空或零时就会出错,有何解决办法???
- 一个是.MDF,一个是.LDF 我想知道里面有哪些表 元素什么的
- 怎么利用多台服务器提高sql性能,急贴...
- 如何create table的时候用另一张表里的一列记录来作栏位名?
- SQL Server脚本问题
- 小小问题
- 普通电脑做服务器
- 误删C:\Program Files\Microsoft SQL Server\MSSQL\Data下的文件怎么办?
DECLARE @TODATE VARCHAR(10)SET @FROMDATE='2008-10-01'
SET @TODATE='2008-12-01'
CREATE TABLE #A (S_NO VARCHAR(10),IN_QTY INT,S_DATE VARCHAR(10))
INSERT INTO #A SELECT '01',100,'2008-10-01'
INSERT INTO #A SELECT '02',200,'2008-10-02'
INSERT INTO #A SELECT '01',200,'2008-11-05'
INSERT INTO #A SELECT '03',120,'2008-11-05'CREATE TABLE #B (S_NO VARCHAR(10),OUT_QTY INT,C_DATE VARCHAR(10))
INSERT INTO #B SELECT '01',50,'2008-10-08'
INSERT INTO #B SELECT '02',70,'2008-10-06'
INSERT INTO #B SELECT '03',30,'2008-10-20'
INSERT INTO #B SELECT '01',150,'2008-11-08'
SELECT S_NO,SUM(IN_QTY)IN_QTY,SUM(OUT_QTY)OUT_QTY
FROM
(SELECT S_NO,IN_QTY,0 OUT_QTY FROM #A WHERE S_DATE BETWEEN @FROMDATE AND @TODATE
UNION ALL
SELECT S_NO,0,OUT_QTY FROM #B WHERE C_DATE BETWEEN @FROMDATE AND @TODATE )A
GROUP BY S_NO
DROP TABLE #A,#B/***01 300 200
02 200 70
03 120 30
select isnull(m.name,n.name) name,
isnull(m.buy_number,0) buy_number ,
isnull(n.out_number,0) out_number
from
(
select name , sum(number) buy_number from where date between date1 and date2 in_number group by name
) m
full join
(
select name , sum(number) out_number from where date between date1 and date2 sale_out group by name
) n
on m.name = n.name
select BUY_IN.name, SUM(BUY_IN.number) AS in_number, SUM(SALE_OUT.number) AS
out_number FROM BUY_IN,SALE_OUT where BUY_IN.name=SALE_OUT.name and [date] between '开始时间' and '截止时间'
GROUP BY BUY_IN.name
(select name,sum(number) as in_number from BUY_IN group by name where date between #%s# and #%s#) a
join
(select name,sum(number) as out_number from SALE_OUT group by name where date between #%s# and #%s#) b
on a.name=b.name
DECLARE @TODATE VARCHAR(10)SET @FROMDATE='2008-10-01'
SET @TODATE='2008-12-01'
CREATE TABLE #A (S_NO VARCHAR(10),IN_QTY INT,S_DATE VARCHAR(10))
INSERT INTO #A SELECT '01',100,'2008-10-01'
INSERT INTO #A SELECT '02',200,'2008-10-02'
INSERT INTO #A SELECT '01',200,'2008-11-05'
INSERT INTO #A SELECT '03',120,'2008-11-05'CREATE TABLE #B (S_NO VARCHAR(10),OUT_QTY INT,C_DATE VARCHAR(10))
INSERT INTO #B SELECT '01',50,'2008-10-08'
INSERT INTO #B SELECT '02',70,'2008-10-06'
INSERT INTO #B SELECT '03',30,'2008-10-20'
INSERT INTO #B SELECT '01',150,'2008-11-08'
SELECT A.S_NO,(SELECT SUM(IN_QTY) FROM #A WHERE S_DATE BETWEEN @FROMDATE AND @TODATE AND S_NO=A.S_NO) IN_QTY,(SELECT SUM(OUT_QTY) FROM #B WHERE C_DATE BETWEEN @FROMDATE AND @TODATE AND S_NO=A.S_NO) OUT_QTY
FROM
#A A GROUP BY A.S_NO
DROP TABLE #A,#B/***
S_NO IN_QTY OUT_QTY
01 300 200
02 200 70
03 120 30
***/
DECLARE @TODATE VARCHAR(10)SET @FROMDATE='2008-10-01'
SET @TODATE='2008-12-01'
CREATE TABLE #BUY_IN (NAME VARCHAR(10),IN_NUMBER INT,[DATE] VARCHAR(10))
INSERT INTO #BUY_IN SELECT '01',100,'2008-10-01'
INSERT INTO #BUY_IN SELECT '02',200,'2008-10-02'
INSERT INTO #BUY_IN SELECT '01',200,'2008-11-05'
INSERT INTO #BUY_IN SELECT '03',120,'2008-11-05'CREATE TABLE #SALE_OUT (NAME VARCHAR(10),OUT_NUMBER INT,[DATE] VARCHAR(10))
INSERT INTO #SALE_OUT SELECT '01',50,'2008-10-08'
INSERT INTO #SALE_OUT SELECT '02',70,'2008-10-06'
INSERT INTO #SALE_OUT SELECT '03',30,'2008-10-20'
INSERT INTO #SALE_OUT SELECT '01',150,'2008-11-08'
SELECT A.NAME,(SELECT SUM(IN_NUMBER) FROM #BUY_IN WHERE [DATE] BETWEEN @FROMDATE AND @TODATE AND NAME=A.NAME) IN_NUMBER,(SELECT SUM(OUT_NUMBER) FROM #SALE_OUT WHERE [DATE] BETWEEN @FROMDATE AND @TODATE AND NAME=A.NAME) OUT_NUMBERFROM #BUY_IN AS A GROUP BY A.NAME
DROP TABLE #BUY_IN,#SALE_OUT
DECLARE @TODATE VARCHAR(10)SET @FROMDATE='2008-10-01'
SET @TODATE='2008-12-01'
CREATE TABLE #A (S_NO VARCHAR(10),IN_QTY INT,S_DATE VARCHAR(10))
INSERT INTO #A SELECT '01',100,'2008-10-01'
INSERT INTO #A SELECT '02',200,'2008-10-02'
INSERT INTO #A SELECT '01',200,'2008-11-05'
INSERT INTO #A SELECT '03',120,'2008-11-05'
INSERT INTO #A SELECT '04',120,'2008-11-05'CREATE TABLE #B (S_NO VARCHAR(10),OUT_QTY INT,C_DATE VARCHAR(10))
INSERT INTO #B SELECT '01',50,'2008-10-08'
INSERT INTO #B SELECT '02',70,'2008-10-06'
INSERT INTO #B SELECT '03',30,'2008-10-20'
INSERT INTO #B SELECT '01',150,'2008-11-08'
SELECT A.S_NO,SUM(IN_QTY) in1 ,SUM(OUT_QTY) ou1t from #a a left join #b b on a.s_no=b.s_no WHERE S_DATE BETWEEN '20040101' AND '20090101'
GROUP BY A.S_NO
DROP TABLE #A,#B
我用Access数据库+VC做的程序 m_database.Open(_T("ShopBook"));
CRecordset m_balanceSet(&m_database); m_balanceSet.Open(CRecordset::forwardOnly,
"select m.name, m.in_number, n.out_number from (select name, sum(number) as in_number from BUY_IN group by name) m join (select name , sum(number) as out_number from SALE_OUT group by name) n on m.name = n.name");