select bd_areacl.areaclname,
bd_salestru.vsalestruname as xs,
bd_psnbasdoc.psnname as ywy,
bd_cubasdoc.custcode as zpksbm,
bd_cubasdoc.custname as zpks,
sum(nvl(arap_djfb.dfbbje, 0)) as dfbbje
from arap_djzb
inner join arap_djfb on arap_djzb.vouchid = arap_djfb.vouchid
inner join bd_cumandoc on arap_djfb.ksbm_cl = bd_cumandoc.pk_cumandoc
inner join bd_cubasdoc on bd_cubasdoc.pk_cubasdoc =
bd_cumandoc.pk_cubasdoc
inner join bd_areacl on bd_cubasdoc.pk_areacl = bd_areacl.pk_areacl
inner join bd_defdoc on bd_defdoc.pk_defdoc =
bd_cumandoc.pk_pricegroupcorp
and bd_defdoc.pk_defdoc1 in
('0001A110000000008NAR', '0001A110000000005YDC')
inner join bd_psndoc on bd_cumandoc.pk_resppsn1 = bd_psndoc.pk_psndoc
inner join bd_psnbasdoc on bd_psnbasdoc.pk_psnbasdoc =
bd_psndoc.pk_psnbasdoc
inner join bd_salestru on bd_salestru.csalestruid =
bd_cumandoc.pk_salestru
where (arap_djfb.dr = 0)
and (arap_djfb.dr = 0)
and (arap_djzb.djrq >= '2009-06-01')
and (arap_djzb.djrq <= '2009-06-30')
and bd_cubasdoc.pk_areacl = '0001A110000000000V3W'
and arap_djzb.djlxbm in ('23AA', '23AE')
group by bd_areacl.areaclname,
bd_psnbasdoc.psnname,
bd_cubasdoc.custcode,
bd_cubasdoc.custname,
bd_salestru.vsalestruname; --帐扣select bd_areacl.areaclname,
bd_salestru.vsalestruname as xs,
bd_psnbasdoc.psnname as ywy,
bd_cubasdoc.custcode as zpksbm,
bd_cubasdoc.custname as zpks,
sum(nvl(arap_djfb.dfbbje, 0)) as dfbbje
from arap_djzb
inner join arap_djfb on arap_djzb.vouchid = arap_djfb.vouchid
inner join bd_cumandoc on arap_djfb.ksbm_cl = bd_cumandoc.pk_cumandoc
inner join bd_balatype on bd_balatype.pk_balatype = arap_djzb.pj_jsfs
inner join bd_cubasdoc on bd_cubasdoc.pk_cubasdoc =
bd_cumandoc.pk_cubasdoc
inner join bd_areacl on bd_cubasdoc.pk_areacl = bd_areacl.pk_areacl
inner join bd_defdoc on bd_defdoc.pk_defdoc =
bd_cumandoc.pk_pricegroupcorp
and bd_defdoc.pk_defdoc1 in
('0001A110000000008NAR', '0001A110000000005YDC')
inner join bd_psndoc on bd_cumandoc.pk_resppsn1 = bd_psndoc.pk_psndoc
inner join bd_psnbasdoc on bd_psnbasdoc.pk_psnbasdoc =
bd_psndoc.pk_psnbasdoc
inner join bd_salestru on bd_salestru.csalestruid =
bd_cumandoc.pk_salestru
where (arap_djfb.dr = 0)
and (arap_djfb.dr = 0)
and (arap_djzb.djrq >= '2009-06-01')
and (arap_djzb.djrq <= '2009-06-30')
and bd_cubasdoc.pk_areacl = '0001A110000000000V3W'
and arap_djzb.djlxbm in ('23A1', '23AK')
and bd_balatype.balancode in ('10', '20')
group by bd_areacl.areaclname,
bd_psnbasdoc.psnname,
bd_cubasdoc.custcode,
bd_cubasdoc.custname,
bd_salestru.vsalestruname; --支票现金
这两个表 只不过是条件不一样 但是 我想合并成一个 可是他们的字段我也想全要 谁有什么好办法吗?急急
bd_salestru.vsalestruname as xs,
bd_psnbasdoc.psnname as ywy,
bd_cubasdoc.custcode as zpksbm,
bd_cubasdoc.custname as zpks,
sum(nvl(arap_djfb.dfbbje, 0)) as dfbbje
from arap_djzb
inner join arap_djfb on arap_djzb.vouchid = arap_djfb.vouchid
inner join bd_cumandoc on arap_djfb.ksbm_cl = bd_cumandoc.pk_cumandoc
inner join bd_cubasdoc on bd_cubasdoc.pk_cubasdoc =
bd_cumandoc.pk_cubasdoc
inner join bd_areacl on bd_cubasdoc.pk_areacl = bd_areacl.pk_areacl
inner join bd_defdoc on bd_defdoc.pk_defdoc =
bd_cumandoc.pk_pricegroupcorp
and bd_defdoc.pk_defdoc1 in
('0001A110000000008NAR', '0001A110000000005YDC')
inner join bd_psndoc on bd_cumandoc.pk_resppsn1 = bd_psndoc.pk_psndoc
inner join bd_psnbasdoc on bd_psnbasdoc.pk_psnbasdoc =
bd_psndoc.pk_psnbasdoc
inner join bd_salestru on bd_salestru.csalestruid =
bd_cumandoc.pk_salestru
where (arap_djfb.dr = 0)
and (arap_djfb.dr = 0)
and (arap_djzb.djrq >= '2009-06-01')
and (arap_djzb.djrq <= '2009-06-30')
and bd_cubasdoc.pk_areacl = '0001A110000000000V3W'
and arap_djzb.djlxbm in ('23AA', '23AE')
group by bd_areacl.areaclname,
bd_psnbasdoc.psnname,
bd_cubasdoc.custcode,
bd_cubasdoc.custname,
bd_salestru.vsalestruname; --帐扣select bd_areacl.areaclname,
bd_salestru.vsalestruname as xs,
bd_psnbasdoc.psnname as ywy,
bd_cubasdoc.custcode as zpksbm,
bd_cubasdoc.custname as zpks,
sum(nvl(arap_djfb.dfbbje, 0)) as dfbbje
from arap_djzb
inner join arap_djfb on arap_djzb.vouchid = arap_djfb.vouchid
inner join bd_cumandoc on arap_djfb.ksbm_cl = bd_cumandoc.pk_cumandoc
inner join bd_balatype on bd_balatype.pk_balatype = arap_djzb.pj_jsfs
inner join bd_cubasdoc on bd_cubasdoc.pk_cubasdoc =
bd_cumandoc.pk_cubasdoc
inner join bd_areacl on bd_cubasdoc.pk_areacl = bd_areacl.pk_areacl
inner join bd_defdoc on bd_defdoc.pk_defdoc =
bd_cumandoc.pk_pricegroupcorp
and bd_defdoc.pk_defdoc1 in
('0001A110000000008NAR', '0001A110000000005YDC')
inner join bd_psndoc on bd_cumandoc.pk_resppsn1 = bd_psndoc.pk_psndoc
inner join bd_psnbasdoc on bd_psnbasdoc.pk_psnbasdoc =
bd_psndoc.pk_psnbasdoc
inner join bd_salestru on bd_salestru.csalestruid =
bd_cumandoc.pk_salestru
where (arap_djfb.dr = 0)
and (arap_djfb.dr = 0)
and (arap_djzb.djrq >= '2009-06-01')
and (arap_djzb.djrq <= '2009-06-30')
and bd_cubasdoc.pk_areacl = '0001A110000000000V3W'
and arap_djzb.djlxbm in ('23A1', '23AK')
and bd_balatype.balancode in ('10', '20')
group by bd_areacl.areaclname,
bd_psnbasdoc.psnname,
bd_cubasdoc.custcode,
bd_cubasdoc.custname,
bd_salestru.vsalestruname; --支票现金
这两个表 只不过是条件不一样 但是 我想合并成一个 可是他们的字段我也想全要 谁有什么好办法吗?急急
是两个结果集合并吗?
A 1
B 2
第二个SELECT
C 3
D 4
是要这两个的合并吗?
A 1
B 2
C 3
D 4
像这样
select ...
union all
select...
A 1
B 2
第二个SELECT
A 1
B 2
是要这两个的不是合并
A 1
B 2
A 3
B 4我不是要的这种结果 我要的是 select A1 B2,A3,A4 但是不是两个表结果集合并的 而是一个表就能算出来这种效果
没有关联字段的话,建一个自增的列关联
--> 测试数据: [tx]
if object_id('[tx]') is not null drop table [tx]
create table [tx] ([A1] varchar(1),[B2] int)
insert into [tx]
select 'A',1 union all
select 'B',2
--> 测试数据: [tx1]
if object_id('[tx1]') is not null drop table [tx1]
create table [tx1] ([A3] varchar(1),[A4] int)
insert into [tx1]
select 'A',1 union all
select 'B',2select a1,b2,a3,a4 from
(
select id1=(select count(*) from [tx] where a1<t.a1)+1 ,* from [tx] t
) m1,(
select id2=(select count(*) from tx1 where a3<t.a3)+1 ,* from [tx1] t
) m2where m1.id1=m2.id2
bd_salestru.vsalestruname as xs,
bd_psnbasdoc.psnname as ywy,
bd_cubasdoc.custcode as zpksbm,
bd_cubasdoc.custname as zpks,
sum(nvl(arap_djfb.dfbbje, 0)) as dfbbje --帐扣金额
sum(nvl(arap_djfb.dfbbje, 0)) as dfbbje --现金金额
你的查询的速度慢,可能是因为你的表JION太多。
你的两个查询是没有办法合并的,因为分组不一样。可以建立索引来提速。或是 干脆建立 新表来统计,要统计的表上做 触发器来改 新表的统计。
-- =========================================
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( id int)
go
insert tb SELECT
32 UNION ALL SELECT
43 UNION ALL SELECT
88
go
IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1( 工号 varchar(10), 姓名 varchar(10))
go
insert tb1 SELECT
'T-0012' , '里尔' UNION ALL SELECT
'T-0015' , '尼号' UNION ALL SELECT
'T-0016' , '张海'
select 工号 , 姓名,ID
from(select ROW_NUMBER() over (order by getdate()) as rn ,* from tb ) l
join (select ROW_NUMBER() over (order by getdate()) as rn ,* from tb1 ) k
on l.rn=k.rngo(3 行受影响)
工号 姓名 ID
---------- ---------- -----------
T-0012 里尔 32
T-0015 尼号 43
T-0016 张海 88
http://topic.csdn.net/u/20090828/11/54c25728-4dc0-4923-a117-3a359c1c16e2.html
我当时的需求跟LZ的有所相似,所以我引用过来,看对LZ有没有帮助,
我决非没有侵权的意思哦。
bd_salestru.vsalestruname as xs,
bd_psnbasdoc.psnname as ywy,
bd_cubasdoc.custcode as zpksbm,
bd_cubasdoc.custname as zpks,
sum(ISNULL(case when bd_defdoc.pk_defdoc1 in (...) then arap_djfb.dfbbje else 0 end,0)) as dfbbje --帐扣金额
sum(ISNULL(case when bd_defdoc.pk_defdoc1 in (...) then arap_djfb.dfbbje else 0 end,0)) as dfbbje1 --现金金额
from ... inner join ...
where ...
group by ...
select * from
union all
select * from
此查询列必须相同,并且各列的数据类型也必须相同。