表A:销售商品流水
SaleNo SalePlu SaleCount Price SsTotal
1 商品1 1 150 150
2 商品3 1 200 200
2 商品5 2 400 800
表B:销售付款流水
SaleNo Zfmode Zftotal
1 现金 10
1 银联 140
2 购物卡 300
2 现金券 100
2 银联 600
现要将表A B统计成:
SaleNo SalePlu 现金支付 购特卡支付 现金券支付 银联支付
1 商品1 10 140
2 商品3 200
2 商品5 100 100 600
SaleNo SalePlu SaleCount Price SsTotal
1 商品1 1 150 150
2 商品3 1 200 200
2 商品5 2 400 800
表B:销售付款流水
SaleNo Zfmode Zftotal
1 现金 10
1 银联 140
2 购物卡 300
2 现金券 100
2 银联 600
现要将表A B统计成:
SaleNo SalePlu 现金支付 购特卡支付 现金券支付 银联支付
1 商品1 10 140
2 商品3 200
2 商品5 100 100 600
解决方案 »
- 在线等啊 高手指教
- TOraSession控件,连接ora后,闲置一段时间,再关闭或者做其他的查询操作会出错
- 关于启动注入DLL的问题。
- 哪位老大有直接将窗体上所有简体字转换成繁体字的控件啊。
- 新手提问:学习DELPHI遇到的相关问题。散光所有分。
- 用wise Installation制作安装包,如何让安装试运行另一个exe文件!
- 一个非常初级的问题,关于Paradox
- 初学者提问(不要笑我哟)!如何ado连接数据库,另外程序发布时,需要bde吗? 谢谢!
- ****** 20 分 *********** 如何得到STRINGGRID的行号
- updatasql的用法?
- Delphi动态调用C++编写的DLL问题
- 进货单问题.
参考:
DECLARE @t1 TABLE(SaleNo INT, SalePlu nVARCHAR(100), SaleCount INT, Price MONEY, SsTotal MONEY)
INSERT INTO @t1 VALUES(1,'商品1',1,150,150)
INSERT INTO @t1 VALUES(2,'商品3',1,200,200)
INSERT INTO @t1 VALUES(2,'商品5',2,400,800)DECLARE @t2 TABLE(SaleNo INT, Zfmode nVARCHAR(100),Zftotal MONEY)
INSERT INTO @t2 VALUES (1,N'现金',10)
INSERT INTO @t2 VALUES(1,N'银联',140)
INSERT INTO @t2 VALUES(2,N'购物卡',300)
INSERT INTO @t2 VALUES(2,N'现金券',100)
INSERT INTO @t2 VALUES(2,N'银联',600)SELECT x.SaleNo, x.SalePlu,
sum(CASE x.zfmode WHEN N'现金' THEN x.Zftotal ELSE 0 END) '现金支付',
sum(CASE x.zfmode WHEN N'购物卡' THEN x.Zftotal ELSE 0 END) '购物卡支付',
sum(CASE x.zfmode WHEN N'现金券' THEN x.Zftotal ELSE 0 END) '现金券支付',
sum(CASE x.zfmode WHEN N'银联' THEN x.Zftotal ELSE 0 END) '银联支付'
FROM (SELECT a.SaleNo, a.SalePlu, b.Zfmode,b.Zftotal
FROM @t1 a LEFT JOIN @t2 b ON a.SaleNo = b.SaleNo) x
GROUP BY x.SaleNo, x.SalePlu
declare @ZfMode varchar(50);
declare @Sql varchar(1000);
set @Sql = 'select SaleNo, SalePlu, sum(case when ZfMode='
while exists(select 1 from #ZfMode)
begin
select top 1 @ZfMode = ZfMode from #ZfMode
delete from #ZfMode where ZfMode = @ZfMode;
set @Sql = @Sql + @ZfMode + ' then 1 else 0 end) [' + @ZfMode + '], '
end;
set @Sql = SubString(@Sql, 1, len(@Sql)-1) + ' from 表A a, 表B b '
+ 'where a.SaleNo=b.SaleNo group by a.SaleNo, a.SalePlu'
exec(@Sql);
drop table #ZfMode
declare @ZfMode varchar(50);
declare @Sql varchar(1000);
set @Sql = 'select SaleNo, SalePlu, sum(case when ZfMode='
while exists(select 1 from #ZfMode)
begin
select top 1 @ZfMode = ZfMode from #ZfMode
delete from #ZfMode where ZfMode = @ZfMode;
set @Sql = @Sql + @ZfMode + ' then 1 else 0 end) [' + @ZfMode + '], '
end;
set @Sql = SubString(@Sql, 1, len(@Sql)-1) + ' from 表A a, 表B b '
+ 'where a.SaleNo=b.SaleNo group by a.SaleNo, a.SalePlu'
exec(@Sql);
drop table #ZfMode
select a.saleno,a.saleplu,a.sstotal,b.t1,b.t2,b.t3,b.t4
from (
select 1 saleno, '1' saleplu, 1 salecount, 150 price, 150 sstotal from dual union
select 2,'3', 1, 200, 200 from dual union
select 2,'5', 2, 400, 800 from dual) a,
(select saleno,
sum(decode(zfmode,'',zftotal)) t1,
sum(decode(zfmode,'',zftotal)) t2,
sum(decode(zfmode,'',zftotal)) t3,
sum(decode(zfmode,'',zftotal)) t4
from (
select 1 saleno, '' zfmode, 10 zftotal from dual union
select 1, '', 140 from dual union
select 2, '', 300 from dual union
select 2, '', 100 from dual union
select 2, '', 600 from dual) group by saleno) b
where a.saleno = b.saleno
order by a.saleno,a.saleplu;saleno saleplu sstotal 现金支付 购物卡支付 现金券支付 银联支付
----------------------------------------------------------
1 商品1 150 10 140
2 商品3 200 300 100 600
2 商品5 800 300 100 600
除非做一个临时表用完后删了还可以有办法,其它的我还没想到我只想到一个用临界时表的方法CREATE TABLE TBA
(
序号 INT,
品名 VARCHAR(50),
账一 INT,
账二 INT,
账三 INT,
)
CREATE TABLE TBB
(
序号 INT,
款项 VARCHAR(50),
金额 INT
)
INSERT INTO TBA(序号,品名,账一,账二,账三)VALUES(1,'商品一',1,150,150)
INSERT INTO TBA(序号,品名,账一,账二,账三)VALUES(2,'商品二',1,200,200)
INSERT INTO TBA(序号,品名,账一,账二,账三)VALUES(2,'商品五',2,400,800)
INSERT INTO TBB(序号,款项,金额)VALUES(1,'现金',10)
INSERT INTO TBB(序号,款项,金额)VALUES(1,'银联',140)
INSERT INTO TBB(序号,款项,金额)VALUES(2,'购物卡',300)
--select a.id,a.uname,b.age from (select * from T_Test) as a,(select * from T_Test2) as b where a.id=b.id
SELECT A.序号,A.品名,A.账一,A.账二,A.账三,B.款项,B.金额 FROM
(SELECT * FROM TBA) AS A,
(SELECT * FROM TBB) AS B DROP TABLE TBA
DROP TABLE TBB
--DROP TABLE MTEMP
-------------------------------------------------------------------------------------
CREATE TABLE TBA
(
序号 INT,
品名 VARCHAR(50),
账一 INT,
账二 INT,
账三 INT,
)
CREATE TABLE TBB
(
序号 INT,
款项 VARCHAR(50),
金额 INT
)
INSERT INTO TBA(序号,品名,账一,账二,账三)VALUES(1,'商品一',1,150,150)
INSERT INTO TBA(序号,品名,账一,账二,账三)VALUES(2,'商品二',1,200,200)
INSERT INTO TBA(序号,品名,账一,账二,账三)VALUES(2,'商品五',2,400,800)
INSERT INTO TBB(序号,款项,金额)VALUES(1,'现金',10)
INSERT INTO TBB(序号,款项,金额)VALUES(1,'银联',140)
INSERT INTO TBB(序号,款项,金额)VALUES(2,'购物卡',300)
INSERT INTO TBB(序号,款项,金额)VALUES(2,'现金卷',100)
INSERT INTO TBB(序号,款项,金额)VALUES(2,'银联',600)
--select a.id,a.uname,b.age from (select * from T_Test) as a,(select * from T_Test2) as b where a.id=b.id
SELECT A.序号,A.品名,A.账一,A.账二,A.账三,B.款项,B.金额 FROM
(SELECT * FROM TBA) AS A,
(SELECT * FROM TBB) AS B /*
SaleNo SalePlu 现金支付 购特卡支付 现金券支付 银联支付
1 商品1 10 140
2 商品3 200
2 商品5 100 100 600*/
/*CREATE TABLE MTEMP
(
序号 INT,
品名 VARCHAR(50),
现金支付 INT,
购特卡支付 INT,
现金卷支付 INT,
银联支付 INT
)*/--INSERT INTO MTEMP(序号,品名,现金支付,购特卡支付,现金卷支付,银联支付)VALUES()--SELECT 序号 序号,品名 商品名 FROM TBA
DROP TABLE TBA
DROP TABLE TBB
--DROP TABLE MTEMP