我在SQL方面还很新!我有来自不同表的两个查询。这是我的第一个查询:
SELECT cli.CLIENTE as clientet,cli.RAZON as nom,
SUM(distinct par.[RECAPTACIO]*0.9) AS recaudacionSINIVA,
SUM(distinct par.canvi) as cambiocargado,
SUM(distinct par.consum) as CC_recaptacio,count(distinct par.DATA) as visita,
SUM((c.quantitat + c.caixes * c.uxcaja) * c.pvp) as CC_Caducados
FROM [V30].[dbo].[parmaq] as par,[V30].[dbo].[clientes] as cli , V30.dbo.parcons as c
where cli.CLIENTE=par.EMPRESA and par.enllas=c.enllas
and par.data > '13-11-2015' and par.data < '20-11-2015'
group by cli.CLIENTE,cli.RAZON
这是我的第二个查询:
SELECT cli.cliente as clientet, cli.RAZON, SUM( distinct m1.IMPORTE) as FACTURACION,
SUM(distinct m2.coste * m2.canti) as CC_facturacio
FROM [V30].[dbo].[clientes] as cli, [V30].[dbo].[MESTA1] as m1,V30.dbo.MESTA2 as m2
where m2.albaran=m1.albaran and m1.CLIENTE=cli.CLIENTE
and m1.FECHA > '13-11-2015' and m1.FECHA < '30-11-2015'
group by cli.CLIENTE,cli.razon order by cli.cliente
在sql上,无法用主键链接表!并且查询一和查询二的结果很好。我想做的是使用“ clientet”列合并结果。我不得不说,您可以获得不同数量的行。
目的是使所有这些信息都具有相同的结果。例如,如果“ clientet”在两个查询中都有结果,则最终结果应该是第一个查询的所有列+第二个查询的“ FACTURACION”和“ CC_facturacio”。
希望您能对我有所帮助,我一直在尝试内部联接和子查询,但是没有办法实现。
两种选择:
正如jarlh所建议的,只要在每个查询中放置占位符以查找不存在的缺失列,并确保它们的顺序相同,就可以进行联合。
您可以对两个查询进行完全外部联接。您可能希望将合并添加到其他列,这取决于在两个表中都不存在行时是否要为列显示空值。
下面的示例假定clientet是联接列:
SELECT COALESCE(TBL_1.clientet, TBL_2.clientet) as clientet
, TBL_1.nom
, TBL_1.recaudacionSINIVA
, TBL_1.cambiocargado
, TBL_1.CC_recaptacio
, TBL_1.visita
, TBL_1.CC_Caducados
, TBL_2.FACTURACION
, TBL_2.CC_facturacio
FROM
(SELECT cli.CLIENTE as clientet
, cli.RAZON as nom
, SUM(distinct par.[RECAPTACIO]*0.9) AS recaudacionSINIVA
, SUM(distinct par.canvi) as cambiocargado
, SUM(distinct par.consum) as CC_recaptacio
, count(distinct par.DATA) as visita
, SUM((c.quantitat + c.caixes * c.uxcaja) * c.pvp) as CC_Caducados
FROM [V30].[dbo].[parmaq] as par,[V30].[dbo].[clientes] as cli , V30.dbo.parcons as c
where cli.CLIENTE=par.EMPRESA and par.enllas=c.enllas
and par.data > '13-11-2015' and par.data < '20-11-2015'
group by cli.CLIENTE,cli.RAZON) TBL_1
FULL OUTER JOIN
(SELECT cli.cliente as clientet
, cli.RAZON
, SUM( distinct m1.IMPORTE) as FACTURACION
, SUM(distinct m2.coste * m2.canti) as CC_facturacio
FROM [V30].[dbo].[clientes] as cli, [V30].[dbo].[MESTA1] as m1,V30.dbo.MESTA2 as m2
where m2.albaran=m1.albaran and m1.CLIENTE=cli.CLIENTE
and m1.FECHA > '13-11-2015' and m1.FECHA < '30-11-2015'
group by cli.CLIENTE,cli.razon) TBL_2
ON TBL_1.clientet = TBL_2.clientet
ORDER BY COALESCE(TBL_1.clientet, TBL_2.clientet)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句