将两个查询的结果合并为一个结果SQL
问题描述:
我试图创建一个compare
,现在我只能将它作为多个结果(两个不同的结果)。将两个查询的结果合并为一个结果SQL
上述两种结果的是从两个查询。
我的第一个结果查询
SELECT
customercode,
CONVERT(DATE, TransDate) transdate,
SUM(TotalReceivable) AS total
FROM
SalesOrderHeader
WHERE
CustomerCode = 'K-MDMM4'
AND TransDate BETWEEN '2016-07-25' AND '2016-07-30'
GROUP BY
CONVERT(DATE, TransDate), customercode
和我的第二个查询
SELECT
b.OutletCode AS outlet,
tanggal,
(cash + cc + dc + flash + piutang + reject + disc50 +
isnull(spesial_item,0)) total
FROM
transaksi a
LEFT JOIN
Outlet b ON a.Outlet = b.OutletCode
LEFT JOIN
area c ON b.areacode = c.areacode
WHERE
b.active = 1
AND b.OutletCode LIKE 'K-MDMM4'
AND flag = 1
AND tanggal BETWEEN '2016-07-25' AND '2016-07-30'
GROUP BY
b.OutletCode, tanggal, cash, cc, dc, flash,
piutang, reject, disc50, spesial_item, ba, mpm, tf,
ul,remarks
ORDER BY
tanggal DESC
我想这个结果。
customercode | transdate | total_tbl1 | total_tbl2
K-MDMM4 2016-07-25 6004050 6004050
K-MDMM4 2016-07-26 6880340 6880340
K-MDMM4 2016-07-27 5745040 5745040
K-MDMM4 2016-07-28 7424820 7424820
我不能使用的jsfiddle :(。我不知道为什么,我无法通过查询创建表。
从现在开始,我有这个疑问
SELECT
b.OutletCode AS outlet,
tanggal,
(cash + cc + dc + flash + piutang + reject + disc50 +
isnull(spesial_item, 0)) total,
SUM(d.TotalReceivable) AS total
FROM
transaksi a
LEFT JOIN
Outlet b ON a.Outlet = b.OutletCode
LEFT JOIN
area c ON b.areacode = c.areacode
LEFT JOIN
salesorderheader d ON CONVERT(DATE, a.tanggal) = CONVERT(DATE, d.transdate)
WHERE
b.active = 1
AND b.BrandCode LIKE '%%'
AND b.OutletCode LIKE '%%'
AND flag = 1
AND YEAR(tanggal) = '2016'
AND MONTH(tanggal) = '7'
AND outlet = 'K-MDMM4'
GROUP BY
OutletCode, tanggal, cash, cc, dc, flash,
piutang, reject, disc50, spesial_item, transdate, totalreceivable
ORDER BY
tanggal DESC
和结果到目前为止我的预期结果...
答
合并这两个查询到单个连接,并选择
SELECT tbl1.customercode,
CAST(tbl1.transdate AS DATE) AS transdate,
tbl1.total AS total_tbl1,
tbl2.total AS total_tbl2
FROM
(
-- Query 1
SELECT customercode,convert(date,TransDate) transdate,SUM(TotalReceivable) as total
FROM SalesOrderHeader
where CustomerCode = 'K-MDMM4'
and TransDate between '2016-07-25' and '2016-07-30'
group by convert(date,TransDate),customercode
) AS tbl1
INNER JOIN (
-- Query 2
select b.OutletCode as outlet,tanggal, (cash + cc + dc + flash + piutang + reject + disc50 +
isnull(spesial_item,0)) total From transaksi a
left join Outlet b on a.Outlet = b.OutletCode
left join area c on b.areacode = c.areacode
where b.active = 1 and b.OutletCode like 'K-MDMM4' and flag = 1 and tanggal
between '2016-07-25' and '2016-07-30'
group by b.OutletCode,tanggal,cash,cc,dc,flash,piutang,reject,disc50,spesial_item,ba,mpm,tf,ul,remarks
) AS tbl2 ON tbl2.outlet = tbl1.customercode AND CAST(tbl2.trnggal AS DATE) = CAST(tbl1.transdate AS DATE)
order by CAST(tbl1.transdate AS DATE) DESC;
答
我没有在这台电脑上安装的数据库,但你要找的是:
SELECT val1, val2 FROM
(SELECT1_of_your_code AS table1) INNER JOIN
(SELECT2_of_your_code AS table2) ON
table1.x == table2.y
+0
请发表评论,downvotes-这是第一次发布的解决方案,甚至它是不是在我看来,一个复制/粘贴的解决方案,它包括所需的全部信息要解决问题描述 – Wolfgang
感谢男人,这是工作。 'by by'部分给我一个错误。我把它放在查询的最后,错误消失了 – YVS1102
好的地方,我更新了答案。 –