如何在单个查询中的不同表中获取不同的字段？ [英] how to get diffrent fields in diffrent tables in single query?

问题描述

select 
purchasemaster.date as pdate,
puchasetable.qty as pqty,
puchasetable.wt as pwt 
from purchasemaster 
inner join puchasetable on purchasemaster.PM_id=puchasetable.PM_id 
where puchasetable.G_id=1 
and purchasemaster.date BETWEEN '2014-10-01' and '2014-10-31' 
group by purchasemaster.date 

UNION 
select haladsale.date as sdate,haladsaletable.qty as sqty,
haladsaletable.wt as swt from haladsale 
inner join haladsaletable on haladsale.HS_id=haladsaletable.HS_id 
where haladsale.date BETWEEN '2014-10-01' and '2014-10-31' 
group by haladsale.date

如何获得此结果？

请帮忙...

How to get this result?
Please help...

推荐答案

如果查询正确地返回数据，那么你应该得到

sdate，sqty和swt记录，但带有pdate，pqty和pwt列标题。

因为union将第一个select语句中的列名称作为pdate，pqty和pwt。



你可以做些什么来区分purchasemaster和haladsale记录，你可以选择添加一个额外的列，例如带有静态值的类型，而不是使用列名（pdata和sdate等）



您可以这样做：

If the query is returning data correctly, then you should be getting
the sdate, sqty and swt records but with the pdate, pqty and pwt column headings.
As the union takes the column names from the first select statement as pdate, pqty and pwt.

What you could do to distinguish the purchasemaster and haladsale records is to add an extra column in you select such as type with a static value, instead of using the column names(pdata and sdate etc)

You could do something like this:

select 
	'purchasemaster' as type,
	purchasemaster.date as date,
	puchasetable.qty as qty,
	puchasetable.wt as wt 
...

union 

select 
	'haladsale' as type,
	aladsale.date as date,
	haladsaletable.qty as qty,
	haladsaletable.wt as wt
...

这篇关于如何在单个查询中的不同表中获取不同的字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！